Oracle Wait Events: DB scattered Read

Dear Readers,

In this article, we will see DB scattered Read  Wait Event.

DB scattered Read wait event

Adjacent blocks are read from disk allowing Oracle to read many blocks by performing large I/Os. Thus, from the storage perspective, db file scattered read is a large sequential read with block size that can get up to 1 MB.

db file scattered read wait event is common for applications with a high amount of large reads (such as full or range scans).

This is true for BI, DWH and DSS workload environments.

The db file scattered read wait event represents a wait for a physical read of multiple Oracle blocks from the disk (unlike db file sequential read  which represents reading a single block). It is usually caused by scanning the entire or a sub-range of a table, index, table partition, or index partition.

Reason:

Multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full table scans into the buffer cache show up as waits for ‘db file scattered read’.

Solution

1. Add / Modify index(es) on the table
2. Update table and/or index statistics if proper index not being used
3. Add hint to use existing index
4. Optimize the application
5. Use Parallel Reads
6. Set Database Parameters
7. Improve I/O Speed
8. Optimize the application
9. Larger Database Caches (64-bit)

Improve Response Time for Db File Scattered Reads

1. Parallel Reads
2. Can be set at the table level (use with caution)
3. Alter table customer parallel degree 4;
4. Normally used by hinting in the SQL Statement

select /*+ FULL(customer) PARALLEL(customer, 4) */ customer_name from  customer;

5. A delicate tradeoff
6. sacrifice the performance of others for the running query.
7. Not necessarily efficient, just faster
8. Parallel Reads may actually do twice the work of a sequential query but have four workers, thus finishing in half the time while using 8x resource.

Thank you for giving your valuable time to read the above information.
Follow us on 
Website              :  www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page   : KT EXPERTS

Follow Me On
Linkedin :Rakesh Monga