My Oracle Support Banner

High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access (Doc ID 875472.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 to 11.2.0.2 [Release 8.0.3 to 11.2]
Information in this document applies to any platform.

Symptoms

A query can wait on 'db file sequential read' for a long time even if the execution plan appears to be optimal. This usually happens when the result set of index scan is large.

For Example:

SELECT D
FROM BIG_TABLE
WHERE A = 1253
AND B in ('CA', 'CO')
AND C > 210 ;


Rows    Row Source Operation
------- ---------------------------------------------------
 215431 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=880191 pr=430780 pw=0 time=2293667056 us) <<<
3582275  INDEX RANGE SCAN BIG_TABLE_IDX (cr=664748 pr=218595 pw=0 time=352506821 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   14363        0.00          0.02
  db file sequential read                    461688        1.15       2254.55 <<<
  SQL*Net message from client                 14363        0.01          9.77
  SQL*Net break/reset to client                   1        0.00          0.00
...

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.