db file sequential read wait event等待事件之二

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而非索引掃描，因為索引掃描的成本更高