db file sequential read在什么情况下会产生
1,rowid定位记录
2,index scan
3,读取控制文件
4,读取文件头
db file sequential read产生过高的原因：
1，选择不佳的索引
2, 行链接或行迁移
3, buffer cache过小
4, 统计信息不准
5, binding peek导致执行计划不准,配置_optim_peek_user_binds=false,禁用binding peek
---创建测试表并构建索引且插入大量数据
SQL> show user
USER is "TBL_BCK"
SQL> create table t_index(a int);
Table created.
SQL> create index idx_t_index on t_index(a);
Index created.
SQL> insert into t_index select object_id from dba_objects;
69780 rows created.
SQL> commit;
Commit complete.
SQL> select count(a),count(distinct a) from t_index;
COUNT(A) COUNT(DISTINCTA)
---------- ----------------
69779 69779
SQL> select index_name,clustering_factor from user_indexes where index_name='IDX
_T_INDEX';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------ -----------------
IDX_T_INDEX 0
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed.
---收集统计信息后查看索引的clustering_factor
SQL> select index_name,clustering_factor from user_indexes where index_name='IDX
_T_INDEX';
INDEX_NAME CLUSTERING_FACTOR
------------------------------------------------------------ -----------------
IDX_T_INDEX 108
---发现index的clustering_factor大小与表的block数量相同
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) as block_counts from t_index;
BLOCK_COUNTS
------------
106
SQL> delete from t_index where rownum<=2000;
2000 rows deleted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
--删除表记录后索引的clustering_factor会减少
SQL> select index_name,clustering_factor from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T_INDEX 105
SQL> insert into t_index select level+300000 from dual connect by level<=10000;
10000 rows inserted
SQL> commit;
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
--增加表记录索引的clustering_factor会增加
SQL> select index_name,clustering_factor from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T_INDEX 121
理想情况下index的clustering_factor与表的块数相同，这样扫描的数据块很少即通过rowid只扫描很少的数据块就可以定位到表的记录
最差情况下是index的clustering_factor与表的记录数相同，这样要扫描数据块很多，才可以定位到表的记录
也就是说索引块存储的rowid与表存储的记录并不能从顺序进一一对应，这样就会产生通过rowid扫描表的数据块很多所以扫描的成本很高，导致大量的物理io出现
也就可能会出现db file sequential read很高的情况
处理这种情况，有如下几个方法；
1,采用全表扫描替换索引扫描，即不用索引了，直接用全表索引，可以用hint full实现
2,如果表上有其它的索引，可以考虑下采用其它的索引替换这个CF高的索引
3,根据表的索引列创建新个表，这样cf自然就减少了，即:create 新表 as select * from 老表 order by 索引列；
注意：cf高并不定就会导致产生性能问题，这只能出现性能问题一个因素，要综合分析
---如果索引的存储顺序与表的存储顺序不同，则索引的cf很高，导致产生大量的物理io,从而db file sequential read很高
SQL> create table t_index as select object_id,object_name from dba_objects order by object_name;--以object_name顺序存储表记录
Table created
SQL> create index idx_t_index on t_index(object_id);--而索引则以object_id顺序方式存储，导致cf极高与表的记录数相同,导致rowid很多次重复扫描同一个数据块才到得到表记录
Index created
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
SQL> select index_name,clustering_factor from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T_INDEX 34544
SQL> select blocks from user_tables where table_name='T_INDEX';
BLOCKS
----------
341
--刷新缓冲池
SQL> alter system flush buffer_cache;
System altered
---要加where 条件，不然会走快速索引全扫描，而它不会产生等待事件db file sequential read
SQL> select count(object_id) from t_index where object_id between 2300 and 4800;
COUNT(OBJECT_ID)
----------------
2501
--上述用户会话的db file sequential read等待
SQL> select event,total_waits,time_waited,average_wait from v$session_event where sid=41 order by total_waits desc;
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
SQL*Net message to client 35 0 0
SQL*Net message from client 34 3265 96.02
db file sequential read 20 18 0.92 ----
Disk file operations I/O 3 0 0.07
events in waitclass Other 1 2 1.67
log file sync 1 0 0.04
db file scattered read 1 6 6.13
7 rows selected
---如果以有序方式存储表记录，此时索引的cf很低与表数据块一样
SQL> create table t_index as select object_id,object_name from dba_objects order by object_id;--以object_name顺序存储表记录
Table created
SQL> create index idx_t_index on t_index(object_id);--而索引则以object_id顺序方式存储，导致cf极高与表的记录数相同,导致rowid很多次重复扫描同一个数据块才到得到表记录
Index created
SQL> exec dbms_stats.gather_table_stats(user,'t_index',cascade=>true);
PL/SQL procedure successfully completed
SQL> select index_name,clustering_factor from user_indexes where index_name='IDX_T_INDEX';
INDEX_NAME CLUSTERING_FACTOR
------------------------------ -----------------
IDX_T_INDEX 333
SQL> select blocks from user_tables where table_name='T_INDEX';
BLOCKS
----------
341
EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------------------------------------------------------------- ----------- ----------- ------------
SQL*Net message to client 41 0 0
SQL*Net message from client 40 2983 74.56
db file sequential read 3 0 0.08 ---当index cf很低时，db file sequential read很高了
Disk file operations I/O 3 0 0.06
events in waitclass Other 1 7 7.49
log file sync 1 0 0.41
db file scattered read 1 3 2.53
7 rows selected
小结：
如果db file sequential read很高，先从sql查起，先看下sql对应的统计信息是否准确,索引的clustering factor是否过高，
考虑是否要重建下索引
clustering factor如果过高，oracle cbo会优先选取full table scan而非索引扫描，因为索引扫描的成本更高
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-761688/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-761688/
扫一扫
729
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
