Resolving common Oracle Wait Events using the Wait Interface

oracleagent

2 years ago

Wait Event:

Possible Causes:

The Oracle session has requested and is waiting for multiple contiguous database blocks (up to DB_FILE_MULTIBLOCK_READ_COUNT) to be read into the SGA from the disk.
Full Table scans
Fast Full Index Scans

ACTIONS:

Optimize multi-block I/O by setting the parameter DB_FILE_MULTIBLOCK_READ_COUNT
Partition pruning to reduce the number of blocks visited
Consider the usage of multiple buffers pools and caches of frequently used indexes/tables in the KEEP pool
Optimize the SQL statement that initiated most of the waits. The goal is to minimize the number of physical and logical reads.
Should the statement access the data by a full table scan or index FFS? Would an index range or unique scan be more efficient?
Does the query use the right driving table?
Are the SQL predicates appropriate for hash or merge join?
If full scans are appropriate, can parallel query improve the response time?
The objective is to reduce the demands for both the logical and
physical I/Os, and this is best achieved through SQL and application tuning.
Make sure all statistics are representative of the actual data. Check the LAST_ANALYZED date.

REMARKS:

If an application that has been running fine for a while suddenly clocks a lot of time on the DB file scattered read event and there hasn’t been a code change, you might want to check to see if one or more indexes have been dropped or become unusable.
DB file scattered read Reference Note# 34558.1

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us: