[Oracle] common wait events

Oracle11g, a new change in the full table scan algorithm for large tables, based on the large table

Oracle 11 GB, new changes in the full table scan algorithm for large tables, according to the large table

Db file scattered read

For tables with frequent access, if no index is created or an appropriate index is not created, Oracle can only scan the entire table, resulting in 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 11g, a new change in the full table scan algorithm for large tables, determines whether to directly read data from the disk by bypassing 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 control this behavior through a hidden parameter "_ serial_direct_read ".

SQL> alter system set "_ serial_direct_read" = false;

The system has been changed.

The following is a simulation of the 10046 event:

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 the entire table

SQL> alter session set events '10046 trace name context off ';

The session has been changed.

SQL> select * from v $ diag_info where;

INST_ID NAME

--------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

1 Default Trace File

C: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace \ orcl_ora_3744_jujay.trc

The following 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 read '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) fast indexing to generate db file scattered read

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; -- fast and full index Scan

SQL> alter session set events '10046 trace name context off ';

The session has been changed.

SQL> select * from v $ diag_info where;

INST_ID NAME

--------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

1 Default Trace File

C: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace \ orcl_ora_3744_jujay.trc

The following 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 read '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

.......

Db file sequential read

This event is generated when you access a data block that is not in SGA, which occurs frequently when you use an index.
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 with 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 with indexes

SQL> alter session set events '10046 trace name context off ';

The session has been changed.

SQL> select * from v $ diag_info where;

INST_ID NAME

--------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

1 Default Trace File

C: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace \ orcl_ora_12136_jujay.trc

C: \ app \ xianzhu \ diag \ rdbms \ orcl \ trace> tkprof orcl_ora_12136_jujay.trc orcl_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: 0bjnptjy6ctk2

Plan Hash: 2928007915

Select object_name

From

T where object_id = 1000

Call count cpu elapsed disk query current rows

-----------------------------------------------------------------------

Parse 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 0.68 34 76 0 64

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing 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 client 9 19.22 36.71

**************************************** ****************************************

As shown above, the df file sequential read event appears

Buffer busy waits & cache buffer chain