[Oracle] common wait events

[Oracle] common wait event db file scattered read for tables that are frequently accessed, if no index is created or an appropriate index is not created, Oracle can only scan the entire table, this will cause a large number of waiting events. During full table scan, the read data is usually continuous on the disk, but the read memory is not continuous. Therefore, this event is named scattered read ), be careful not to be confused by its name. The number of multiple reads at a time is affected by the DB_FILE_MULTIBLOCK_READ_COUNT parameter. In the actual diagnosis process, you can use the v $ session_wait view to find the session wait, and then use other views to find the problematic SQL statement. When the wait event is significant, you can also use the v $ session_longops dynamic performance view for diagnosis. 1) simulate db file scattered read generated by full table scan: Oracle 11 GB. There are new changes in the full table scan algorithm for large tables, determines whether to directly read data from the disk by bypassing the SGA Based on the table size and cache size. 10 Gb reads data through the cache. Oracle 11g considers that direct path reading is faster and more efficient than db file scattered reads. We can use a hidden parameter "_ serial_direct_read" to control this behavior. SQL> alter system set "_ serial_direct_read" = false; the system has changed. The following uses the 10046 event simulation: SQL> alter session set tracefile_identifier = 'jujay '; the session has been changed. SQL> alter session set events '10046 trace name context forever, level 12'; the session has been changed. SQL> Select/* + full (t) */* from t where object_id =-1; -- use Hint to force the optimizer to scan SQL in the whole table> alter session set events '10046 trace name context off'; the session has been changed. SQL> select * from v $ diag_info where name = 'default Trace file'; INST_ID NAME ---------- ------------------------------------------------------------ VALUE limit 1 Default Trace Filec: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace \ orcl_ora_3744_jujay.trc is the content of the trace file generated by 10046 :....... WAIT #47878792599648: nam = 'db file scattered read 'ela = 34 file # = 8 block # = 131 blocks = 5 obj # = 23315 tim = 1356470039698744 WAIT #47878792599648: nam = 'db file scattered read' ela = 27 file # = 8 block # = 200 blocks = 8 obj # = 23315 tim = 1356470039699325 WAIT #47878792599648: nam = 'db file scattered read' ela = 20 file # = 8 block # = 217 blocks = 7 obj # = 23315 tim = 1356470039699788 WAIT #47878792599648: nam = 'db file scattered re Ad 'ela = 22 file # = 8 block # = 224 blocks = 8 obj # = 23315 tim = 1356470039700179 WAIT #47878792599648: nam = 'db file scattered read' ela = 19 file # = 8 block # = 241 blocks = 7 obj # = 23315 tim = 1356470039700589 ....... 2) generate db file scattered read through index quick scan first, create an index: SQL> create index I _t on t (object_id); the index has been created. SQL> alter session set tracefile_identifier = 'jujay '; the session has been changed. SQL> alter session set events '10046 trace name context forever, level 12'; the session has been changed. SQL> Select count (*) from t; -- index quick full scan SQL> alter session set events '10046 trace name context off'; session changed. SQL> select * from v $ diag_info where name = 'default Trace file'; INST_ID NAME ---------- ------------------------------------------------------------ VALUE limit 1 Default Trace Filec: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace \ orcl_ora_3744_jujay.trc is the content of the trace file generated by 10046 :....... WAIT #47878792599648: nam = 'db file scattered read 'ela = 34 file # = 8 block # = 131 blocks = 5 obj # = 23315 tim = 1356470039698744 WAIT #47878792599648: nam = 'db file scattered read' ela = 27 file # = 8 block # = 200 blocks = 8 obj # = 23315 tim = 1356470039699325 WAIT #47878792599648: nam = 'db file scattered read' ela = 20 file # = 8 block # = 217 blocks = 7 obj # = 23315 tim = 1356470039699788 WAIT #47878792599648: nam = 'db file scattered re Ad 'ela = 22 file # = 8 block # = 224 blocks = 8 obj # = 23315 tim = 1356470039700179 WAIT #47878792599648: nam = 'db file scattered read' ela = 19 file # = 8 block # = 241 blocks = 7 obj # = 23315 tim = 1356470039700589 ....... When db file sequential read accesses a data block that is not in the SGA, this event is generated. Generally, index access occurs frequently. In a normal OLTP system, a large proportion of the event is normal, but if the event waits for a very long event, it indicates that there are a large number of index read operations currently, can you consider whether full table scan is faster? Or is the disk I/O too slow? First, create an index: SQL> create index I _t on t (object_id); the index has been created. Then access the table data using indexes: SQL> alter session set tracefile_identifier = 'jujay '; the session has been changed. SQL> alter session set events '10046 trace name context forever, level 12'; the session has been changed. SQL> select object_name from t where object_id = 1000; -- access table data using indexes SQL> alter session set events '10046 trace name context off'; the session has been changed. SQL> select * from v $ diag_info where name = 'default Trace file'; INST_ID NAME ---------- ------------------------------------------------------------ VALUE limit 1 Default Trace Filec: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace \ orcl_ora_12136_jujay.trc c: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace> tkprof orcl_ora_12136_jujay.trc or Cl_ora_12136_jujay.txt TKPROF: Release 11.2.0.1.0-Development on Wednesday December 26 13:23:29 2012 Copyright (c) 1982,200 9, Oracle and/or its affiliates. all rights reserved. view the content in the trace file: SQL ID: 0bjnptjy6ctk2Plan Hash: 2928007915 select object_name from t where object_id = 1000 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ------------ Pars E 2 0.01 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch 8 0.03 0.68 34 76 0 64 ------- ------ -------- ---------- total 12 0.04 34 76 0 64 misses in library cache during parse: 1 Optimizer mode: ALL_ROWSParsing user id: 92 Rows Row Source Operation ------- ------------------------------------------------- 32 table access by index rowid t (cr = 38 pr = 34 pw = 0 time = 0 us cost = 35 size = 960 card = 32) 32 index range scan I _T (cr = 6 pr = 2 pw = 0 time = 837 us cost = 3 size = 0 card = 32) (object id 75191) elapsed times include waiting on following events: Event waited on Times Max. wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL * Net message to client 9 0.00 0.00 db file sequential read 34 0.10 0.67 SQL * Net message from clien T 9 19.22 36.71 ************************************ **************************************** * *** as shown above, the buffer busy waits & cache buffer chain and buffer wait events occur in the df file sequential read event because of hot blocks. The solution varies depending on the data block type: * Table data blocks: data is distributed across more data blocks to reduce the frequency of simultaneous access of data blocks by multiple users. * Index data block: When a table's primary key uses sequence to generate a key value, the rightmost leaf node of the index is easy to become a hot block. You can consider using reverse indexes. Free buffer -- wait for the idle buffer when the data block is read from the disk into the memory, if there is no idle space in the memory, this wait event will occur, possible reasons: 1) data buffer is too small to increase Data buffer2) there is too much dirty Data in the memory, so we need to increase the number of DBWR processes buffer busy-the buffer is busy waiting when the user frequently reads or modifies the same Data block, the wait event appears. If the wait event is long, it indicates that there is a hot block in the database. Log file sync-log file synchronization wait. When a commit command is issued, the LGWR process writes the corresponding redo log from the log buffer to the disk. This event is generated. If there are a large number of such events, check that there are frequent user submissions, more than in the OLTP system. Log buffer space -- wait for the log buffer space. If there is no free space available for the log buffer, this wait event occurs. Generally, this wait event is rarely possible unless the log buffer is too small or the I/O is too bad. Log file sequential read-log file sequential read wait when the log file is read (such as log archiving), this wait event occurs. Log file switch -- log switching wait for this event to be divided into two types: 1) when the checkpoint incomplete log is switched, if some dirty databases corresponding to the next log have not been written to the disk, you must wait for the checkpoint to complete. The methods to reduce the waiting event include increasing the log file size, increasing the log file group, or improving the DBWR performance. 2) archiving needed if the database is in archive mode, when the log is switched, if the next log is still archived, the switch can be completed only after the log is archived. The reason for this wait event is generally that the arch process is dead or too slow. Enqueue-The lock wait enqueue can be equivalent to the lock. If a long wait occurs, it indicates that the database is blocked and locked. Wait events related to latch contention * library cache pin allocation * library cache load lock and library cache wait events can be determined to be a problem with the shared pool, it is basically caused by poor SQL statements, such as not binding variables.