Solution : https://service.sap.com/sap/support/notes/619188 (SAP Service marketplace login required)
Summary :
The SAP note delves into Oracle wait events and their impact on database performance, revealing that they constitute vital metrics for diagnosing and tuning issues. It discusses various types of wait events, how they can be accessed using Oracle V$ views, such as V$SYSTEM_EVENT and V$SESSION_WAIT, and the relevance of wait events to SAP systems, differentiating between Oracle-defined and SAP-specific idle events. Optimization tactics include querying and analyzing these events to pinpoint inefficiencies. Emphasized is the necessity to understand these waiting times as they often compose a significant part of the database's response time. It further lists queries to access historical and current wait event data, aiding in proactive system management and optimization.
Key words :
1 data block2 sort block3 save undo block4 segment header5 save undo header6 free list7 extent map8 1st level bitmap block9 2nd level bitmap block10 3rd level bitmap block11 bitmap block12 bitmap index block13 file header block14 unused15 system undo block16 system undo block17 undo header18 undo block, sbtinit / sbtopen / sbtread / sbtwrite / sbtclose / sbtinfo / sbtremove / sbtbackup / sbtclose2 / sbtcommand / sbtend / sbterror / sbtinfo2 / sbtinit2 / sbtread2 / sbtremove2 / sbtrestore / sbtwrite2 / sbtpcbackup / sbtpccancel / sbtpccommit / sbtpcend / sbtpcquerybackup / sbtpcqueryrestore / sbtpcrestore / sbtpcstart / sbtpcstatus / sbtpcvalidate oracle 10g, slave waitjobq slave waitjs external jobksv master waitlns async archive loglns async dest activationlns async end, components sv-bo backoffice service delivery priority recommendations / additional info category faq validity, block change tracking buffer spacechange tracking file synchronous writechange tracking file synchronous read meaning, data block / bitmap block / bitmap index block, txn recovery startpx deque waitpx idle waitqueue messagesrdbms ipc messagesga, asm background timerclass slave waitdiag idle waitdispatcher timeremon idle waitgcs, component shrinksingle-task messageslave waitsmon timersql net message, library cache locklibrary cache load locklibrary cache pin meaning
Related Notes :
| 1611285 | FRP sequence 1: Performance improvement for DIF access |
| 1438410 | SQL: Script collection for Oracle |
| 1176846 | DBA Cockpit: Adjusting the Oracle idle events |
| 1121838 | SELECT on partition hangs if a shrink compact is running |
| 1044758 | EM repository operations with EMCA and Oracle quiesce |
| 1017970 | DBMS_ADVISOR may result in deadlock |
| 1013049 | FAQ: Oracle Data Pump |
| 971261 | Oracle 9.2 / 10.2: Hanging queries with STAR_TRANSFORMATION |
| 964619 | RMAN: Incremental backups with Block Change Tracking |
| 964344 | FAQ: Oracle mutexes |
| 963894 | SMON blocks processes in rollback of large transactions |
| 904188 | Locks when you execute Index Rebuilds Online in parallel |
| 894078 | 9.2.0.7: Long waits for KSU PROCESS ALLOC LATCH YIELD |
| 875477 | Avoiding long runtimes with BEGIN BACKUP |
| 869006 | Composite SAP note: ORA-04031 |
| 857998 | Number range buffering for DIM IDs and SIDs |
| 857973 | Deleting clients efficiently using Oracle |
| 853576 | Oracle 10g: Performance analysis w/ ASH and Oracle Advisors |
| 853084 | Transferring temporary /BI0/06 tables to another tablespace |
| 846364 | Parse of star query may spin |
| 842240 | FAQ: Backup strategy of large and highly-available databases |
| 840553 | BW Oracle: TRUNCATE table slower than DELETE |
| 830576 | Parameter recommendations for Oracle 10g |
| 825653 | Oracle: Common misconceptions |
| 806554 | FAQ: I/O-intensive database operations |
| 805934 | FAQ: Database time |
| 793113 | FAQ: Oracle I/O configuration |
| 789011 | FAQ: Oracle memory areas |
| 768308 | Deadlock with row cache locks and library cache locks |
| 767414 | FAQ: Oracle latches |
| 766827 | Composite SAP note: Performance SCM 4.0 |
| 766349 | FAQ: Oracle SQL optimization |
| 758989 | Poor performance with TRUNCATEs |
| 745639 | FAQ: Oracle enqueues |
| 738641 | Change run/rollup stops with simultaneous COALESCE |
| 712624 | High CPU consumption by Oracle |
| 659946 | FAQ: Temporary tablespaces |
| 651060 | FAQ: Oracle Parallel Execution |
| 649876 | Library Cache Pin Contention after Upg. to 9.2.0.2 - 9.2.0.4 |
| 626172 | Performance problems with Outer Join queries in Oracle 9.2 |
| 620803 | Oracle 9i: Automatic Segment Space Management |
| 618868 | FAQ: Oracle performance |
| 613872 | Oracle traces with ORADEBUG |
| 610357 | Oracle hang with event DB_FILE_SCATTERED_READ |
| 607415 | RSORAVSE displays poor averages |
| 596748 | Simultaneous generation of selection screens in background |
| 596420 | System standstill during deadlock (ORA-60) |
| 582427 | Error due to inconsistent Oracle DDIC |
| 564446 | Scheduling of background job BTCH_RSORAVSH |
| 563359 | Performance optimization for tables with LOB columns |
| 562403 | FAQ: Oracle Net |
| 556764 | Upgrade hangs in phase ACT_<REL> |
| 549298 | RSORAVSH: LOAD_PROGRAM_NOT FOUND during the run of RSCOLL00 |
| 541538 | FAQ: Reorganization |
| 521230 | FAQ: Client software 9i or lower on UNIX |
| 500235 | Network Diagnosis with NIPING |
| 488583 | Database hangs: Waits for cache buffer chain latch |
| 449136 | "cache buffers chains" latch contention on 8.1 |
| 400698 | ORA-4031 Memory leak on Oracle 8.1.7 |
| 376905 | Long parsing times and Library Cache Locks |
| 359835 | Design of the temporary tablespace in the BW System |
| 328822 | AIX kernel extension for Oracle |
| 198752 | TCP delay problem under Oracle 8.1.x |
| 180430 | Installing the ORACLE client software for UNIX |
| 164925 | Storage parameter of tablespace PSAPTEMP |
| 79341 | Checkpoint not complete |
| 72638 | Performance problems with SQL*Net |
| 20071 | Permanent lock after connection termination |
| 391 | Archiver stuck |