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

原创 2013年12月02日 09:16:25

In this Document

  Symptoms
  Cause
  Solution
  References

APPLIES TO:

Oracle Server - 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

In most cases like this, the execution of the query waits on "TABLE ACCESS BY INDEX ROWID" much longer than on INDEX SCAN. It is because the random access to the table rows is much more expensive than index scan.

 

SOLUTION

You can try one or more of the following.

 

1. Check if there is a better index or plan. You may need to re-design index configuration.

 

2. Try table full scan. Full scan often runs faster than Index scan, though its CBO cost is higher than the cost of Index scan.

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

 

3. Create a concatenated index to avoid the table access if the query has only a few columns of the table in SELECT and WHERE clauses. 

For example:

CREATE INDEX <INDEX_NAME> ON BIG_TABLE (A, B, C, D);

 NOTE : This can only be applied to SELECT for the table. If the query updates the table, this will not help.

 

4. Move the table into the tablespace with a larger block size.  A larger block has more rows in it, so it may help to reduce block I/O.

    And it will also be helpful to reorganize the table so that the index may have a smaller clustering factor.

 

5. Consider increasing buffer cache so that more table blocks can be cached. It is a good idea to use keep buffer pool if the table is frequently accessed.

Note 76374.1 Multiple Buffer Pools

 

6. Consider using IOT(Index Organized Table). IOT may reduce I/O because it stores data in a B*Tree index structure.
   For example, if the column 'A' is the primiary key of BIG_TABLE, you can try to create IOT as follows.
   
   create table BIG_TABLE (A number primary key, B char(2), C number, D varchar2(10)) 
   organization index;

 

7. Consider using parallel execution if there is sufficient free resource(CPU, memory) in the server.
   This option does not reduce I/O. But, this may help to reduce execution time.

 

8. Bad disk I/O can be a reason. In this case, improve the I/O of devices where the table resides. This requires help from a system administrator.

REFERENCES

NOTE:34559.1 - WAITEVENT: "db file sequential read" Reference Note
NOTE:76374.1 - Multiple Buffer Pools
 
 

相关内容

   
 

High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access

最近某些系统AWR的top 5中“Db File Sequential Read”占据的时间百分比非常大,通常这种等待事件是一种正常的。但当前系统性能是有些问题的,并发量大,有些缓慢,因此需要判断这种...
  • bisal
  • bisal
  • 2013年10月19日 22:59
  • 1485

浅析oracle常见等待事件之 db file scattered read

浅析oracle常见等待事件之 db file scattered read(转)  原文地址: http://www.hellodml.com/2011/12/%E6%B5%...
  • haiross
  • haiross
  • 2014年07月17日 13:48
  • 5298

db file sequential read等待事件

最近某个应用的AWR中总显示“db file sequential read“等待事件位于top 5之首,下面检索下MOS关于这个等待事件的说明。 等待事件: "db file sequentia...
  • bisal
  • bisal
  • 2013年10月01日 23:33
  • 9870

详解 db file sequential read 等待事件

db file sequential read (本文由thomaswoo_dba翻译,转载请注明出处) db file sequential read 事件有三个参数:file#,first ...
  • wuweilong
  • wuweilong
  • 2015年02月28日 20:14
  • 11994

全表扫描引发的db file sequential read

今天我要做一个SQL调优,监控该SQL, 利用ASH 监控 该SQL是在sid=4848 上面跑的 SQL> select SESSION_ID,NAME,P1,P2,P3,WAIT_TIM...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2014年03月12日 11:06
  • 1125

db file sequential read事件

Oracle db file sequential read   这一事件通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著,可能表示在多表连接中,表的连接顺序存在问题,可能...
  • weiwangsisoftstone
  • weiwangsisoftstone
  • 2013年01月24日 21:08
  • 8874

db file sequential read等待事件总结

db file sequential read The db file sequential read wait event has three parameters: file#, first b...
  • f88520402
  • f88520402
  • 2013年12月29日 20:34
  • 2927

I/O上的等待事件 —— db file sequential read

如果db file scattered read事件是伴随Multi Block I/O发生的等待事件,那db file sequential read事件就是伴随Single Block I/O发生...
  • zq9017197
  • zq9017197
  • 2012年08月31日 12:32
  • 1610

Oracle waits event:DB File Sequential Read

The DB File Sequential Read wait event generally indicates a single block read (an index read, for...
  • wanggangytsoft
  • wanggangytsoft
  • 2010年04月19日 15:59
  • 354

oracle之 db file sequential read等待事件优化思想

为什么db file sequential read事件在full table scan操作中显现,为什么在多块读中为什么会有单块读存在 ? extent的大小 :当扩展区中的最后一组块仅是1个...
  • zhang123456456
  • zhang123456456
  • 2017年08月28日 17:52
  • 343
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access (文档 ID 875472.1)
举报原因:
原因补充:

(最多只允许输入30个字)