DB file scattered read for Oracle wait event

1. Cause of production

This wait event typically occurs when the database is read in chunks, indicating that there is a wait associated with a full table scan and a quick index scan . This usually means too many full-table scans, or insufficient I/O capabilities, or I/O competition.

2. Determine the method of generating the problem object

A) The SQL statement that looks for a full table scan can use the following statement:

Select Sql_text

From V$sqltext T, V$sql_plan p

where T.hash_value = P.hash_value

and p.operation = ' TABLE ACCESS '

and p.options = ' full '

Order by P.hash_value, t.piece;

b) Find the SQL statement for the fast full Index scan using the following statement:

Select Sql_text

From V$sqltext T, V$sql_plan p

where T.hash_value = P.hash_value

and p.operation = ' INDEX '

and p.options = ' Full SCAN '

Order by P.hash_value, t.piece;

3. Solutions

(1) Establishing an index on a suitable field changing the way a table is accessed from a full table scan to an index scan can effectively reduce physical IO.

(2) For large tables, in the appropriate fields, such as year and year, the region code to establish a partition to the full table scan into a partition scan to reduce physical IO.

(3) It is also effective to reduce physical IO by placing the database tables that need to be scanned frequently in the keep pool.

(4) Adjust the Db_file_multiblock_read_count value to achieve the purpose of reading more data blocks each time.

DB file scattered read for Oracle wait event