High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access (文档 ID 875472.1)
来源:互联网 发布:python 输出input 编辑:程序博客网 时间:2024/05/18 11:50
In this Document
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 DFROM BIG_TABLEWHERE A = 1253AND 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.
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 NoteNOTE:76374.1 - Multiple Buffer Pools
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS
- High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access (文档 ID 875472.1)
- High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access
- Oracle waits event:DB File Sequential Read
- db file sequential read
- db file sequential read
- db file sequential read
- Db file sequential read while doing full table scan?
- about FTS(Full Table Scan) V db file scattered read&db file sequential read
- db file sequential read事件
- 模拟DB File Sequential Read
- db file sequential read 详解
- Oracle Waits event:DB File Scattered Read
- db file sequential read与db file scattered read
- 数据库中db file sequential read事件
- db file sequential read等待事件
- db file sequential read等待事件
- db file sequential read等待事件
- db file sequential read等待事件
- Linux shell用法和技巧(部分)
- js实现生日倒计时的代码一例
- 实践出来的软件开发模型
- 【数组】14周项目五(一)。统计A出现的次数
- cocos2d-x学习:架构与目录结构
- High Waits on 'Db File Sequential Read' Due to Table Lookup Following Index Access (文档 ID 875472.1)
- 今天开通csdn博客,以后开始写博文了,把我的东西都弄出来
- iOS setter & getter
- 安装XAMPP时启动Apache失败解决方法 xampp-win32-1.8.3-1-VC11-installer
- ipv4设置
- Android使用UncaughtExceptionHandler捕获全局异常
- 菜单栏或按钮式的【全选】【复制】或【粘贴】
- 中国互联网的5大生死逻辑,我认为:必读
- VB.NET函数——日期时间函数