Oracle Common Wait Events

DB file Scattered read

For frequently accessed tables, if an index is not indexed or an appropriate index is not established, Oracle can only scan it for a full table, resulting in a large number of waiting events.

When a full table scan, the data read is generally contiguous on disk, but is not contiguous when read to memory, so the event is named discrete read (scattered read), and be careful not to be fooled by its name.

The number of multiple-block reads is affected by the db_file_multiblock_read_count of the parameters.

In the actual diagnostics, you can find the session waiting through the v$session_wait view, and then find the problem SQL in conjunction with other views, when the wait event is more significant, the user can also combine the v$session_longops dynamic performance view to diagnose.

1 simulation via full table scan generates DB file scattered read:

Oracle 11g, a new change in the full table scan algorithm for large tables, determines whether to bypass the SGA to read data directly from the disk, depending on the size of the table, the size of the cache, and so on. and 10g reads the data all through the cache. Oracle 11g considers the use of direct path reading for large table full tables, which can be faster and more efficient than the data file hash read in 10g (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 changed.

The following is simulated with 10046 events:

Sql> alter session set tracefile_identifier= ' Jujay ';

The session has changed.

Sql> alter session SET events ' 10046 Trace name Context forever, Level 12 ';

The session has changed.

Sql> Select/*+full (t)/* from t where object_id=-1; --Force the optimizer to take a full table scan via hint

Sql> alter session SET events ' 10046 Trace name context off ';

The session has changed.

Sql> SELECT * from V$diag_info where name= ' Default Trace File ';

inst_id NAME

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

VALUE

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

1 Default Trace File

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

The following is the contents of the trace file generated by 10046:

.......

Wait #47878792599648: nam= ' db file scattered read ' ela= file#=8 block#=131 blocks=5 obj#=23315 tim=1356470039698744

Wait #47878792599648: nam= ' db file scattered read ' ela= file#=8 block#=200 blocks=8 obj#=23315 tim=1356470039699325

Wait #47878792599648: nam= ' db file scattered read ' ela= file#=8 block#=217 blocks=7 obj#=23315 tim=1356470039699788

Wait #47878792599648: nam= ' db file scattered read ' ela= file#=8 block#=224 blocks=8 obj#=23315 tim=1356470039700179

Wait #47878792599648: nam= ' db file scattered read ' ela= file#=8 block#=241 blocks=7 obj#=23315 tim=1356470039700589

.......

Back to the column page: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/