一次簡單的效能優化診斷,聚簇因子過高導致全表掃描。
一次簡單的效能優化診斷,聚簇因子過高導致全表掃描。
業務人員反映一個查詢非常慢:
--------------------------------------------------------------------------------
select * from ab44 where aae002=201006;
--------------------------------------------------------------------------------
檢視執行計劃,是全表掃描
SQL> explain plan for select * from ab44 where aae002=201006;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 781340439
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10554 | 865K| 8777 (3)| 00:01:46 |
|* 1 | TABLE ACCESS FULL| AB44 | 10554 | 865K| 8777 (3)| 00:01:46 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("AAE002"=201006)
已選擇13行。
看看查詢應該返回多少資料量,還有這個表有多少記錄。
SQL> select count(*) from ab44 where aae002='201006';
COUNT(*)
----------
576
SQL> select count(*) from ab44;
COUNT(*)
----------
3310023
SQL> select 576/3310023 from dual;
576/3310023
-----------
.000174017
查詢所需返回的行數僅佔表的很小比例,如果有索引的話,應該索引掃描才對。
檢視錶的索引,發現在aae002欄位上有一個複合索引,四個欄位組成AAE002, AAE003, AAB001, AAE140。既然有索引,為什麼沒有使用呢?莫非是缺失統計資訊。
檢視錶、索引、直方圖的資訊都有。而且統計資訊相對還是比較新的。
SQL> select num_rows,blocks,avg_row_len from user_tables where table_name='AB44';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
3310017 44538 84
SQL> select distinct_keys,clustering_factor,num_rows from USER_IND_STATISTICS WHERE table_name='AB44' and index_name='PK_AB44';
DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------- ----------------- ----------
3309447 3299907 3309447
SQL> SELECT * FROM USER_HISTOGRAMS WHERE table_name='AB44';
略。。。。。。。。。。。。。。。。。。。。。。。。
查詢到索引的統計資訊的時候,發現索引的聚簇因子非常高,非常接近表的行數。重新分析表,依然如此。
修改聚簇因子後,檢視執行計劃,已經是索引掃描了。
begin
dbms_stats.set_index_stats(ownname => 'NCSI',indname => 'PK_AB44',clstfct => '7800');
end;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-------------
Plan hash value: 1618544176
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10554 | 865K| 239 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| AB44 | 10554 | 865K| 239 (1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | PK_AB44 | 10554 | | 45 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AAE002"=201006)
已選擇14行。
但是到這裡並不能說一點是聚簇因子導致的,因為很可能是還有直方圖的因素。查詢列AAE002上的唯一值個數為420,而表的記錄總數是330萬,如果沒有直方圖的話,ORACLE評估返回的行數應該是3300000/420=7857條記錄,按照這個記錄量來看,返回的行數佔表記錄總數的0.2%.根據經驗,應該也能使用到索引才對。
於是重新收集統計資訊,取消直方圖。檢視執行計劃,還是全表掃描。看來直方圖在本例中所佔影響因素較小,還是聚簇因子過大惹的禍。
暫時通過修改聚簇因子暫時改善了效能問題,晚上的時候,按照索引欄位的順序重新建立了表。
SQL>create table AB44_TEMP as select * from ab44 where 1=0;
SQL>INSERT /*+ append */INTO AB44_TEMP SELECT * FROM AB44 ORDER BY AAE002, AAE003, AAB001, AAE140;
SQL>commit;
SQL>drop table ab44;
SQL>alter table ab44_temp rename to ab44;
重新建立索引,分析表。重建後的聚簇因子只有60197,遠遠小於之前的 3299907。檢視執行計劃,也對了。
SQL> explain plan for select * from ab44 where aae002=201006;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------
Plan hash value: 2627288474
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10799 | 885K| 249 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| AB44 | 10799 | 885K| 249 (1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | AB44_TEMP_IND | 10799 | | 50 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AAE002"=201006)
已選擇14行。
而且為了驗證本例確實是由於聚簇因子過大佔了決定因素。我把重建後的表直方圖取消掉,重新查詢,每一個AAE002的值都是索引掃描了。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NCSI',
TABNAME => 'AB44',
CASCADE => TRUE,
METHOD_OPT => 'for ALL columns SIZE 1');
END;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=201002;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=201006;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=198701;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=199101;
SQL> select object_name,operation,options from plan_table where id=2;
OBJECT_NAME OPERATION OPTIONS
-------------------- ------------------------------ --------------------
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
--------------------------------------------------------------------------------
select * from ab44 where aae002=201006;
--------------------------------------------------------------------------------
檢視執行計劃,是全表掃描
SQL> explain plan for select * from ab44 where aae002=201006;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 781340439
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10554 | 865K| 8777 (3)| 00:01:46 |
|* 1 | TABLE ACCESS FULL| AB44 | 10554 | 865K| 8777 (3)| 00:01:46 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("AAE002"=201006)
已選擇13行。
看看查詢應該返回多少資料量,還有這個表有多少記錄。
SQL> select count(*) from ab44 where aae002='201006';
COUNT(*)
----------
576
SQL> select count(*) from ab44;
COUNT(*)
----------
3310023
SQL> select 576/3310023 from dual;
576/3310023
-----------
.000174017
查詢所需返回的行數僅佔表的很小比例,如果有索引的話,應該索引掃描才對。
檢視錶的索引,發現在aae002欄位上有一個複合索引,四個欄位組成AAE002, AAE003, AAB001, AAE140。既然有索引,為什麼沒有使用呢?莫非是缺失統計資訊。
檢視錶、索引、直方圖的資訊都有。而且統計資訊相對還是比較新的。
SQL> select num_rows,blocks,avg_row_len from user_tables where table_name='AB44';
NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
3310017 44538 84
SQL> select distinct_keys,clustering_factor,num_rows from USER_IND_STATISTICS WHERE table_name='AB44' and index_name='PK_AB44';
DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
------------- ----------------- ----------
3309447 3299907 3309447
SQL> SELECT * FROM USER_HISTOGRAMS WHERE table_name='AB44';
略。。。。。。。。。。。。。。。。。。。。。。。。
查詢到索引的統計資訊的時候,發現索引的聚簇因子非常高,非常接近表的行數。重新分析表,依然如此。
修改聚簇因子後,檢視執行計劃,已經是索引掃描了。
begin
dbms_stats.set_index_stats(ownname => 'NCSI',indname => 'PK_AB44',clstfct => '7800');
end;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-------------
Plan hash value: 1618544176
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10554 | 865K| 239 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| AB44 | 10554 | 865K| 239 (1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | PK_AB44 | 10554 | | 45 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AAE002"=201006)
已選擇14行。
但是到這裡並不能說一點是聚簇因子導致的,因為很可能是還有直方圖的因素。查詢列AAE002上的唯一值個數為420,而表的記錄總數是330萬,如果沒有直方圖的話,ORACLE評估返回的行數應該是3300000/420=7857條記錄,按照這個記錄量來看,返回的行數佔表記錄總數的0.2%.根據經驗,應該也能使用到索引才對。
於是重新收集統計資訊,取消直方圖。檢視執行計劃,還是全表掃描。看來直方圖在本例中所佔影響因素較小,還是聚簇因子過大惹的禍。
暫時通過修改聚簇因子暫時改善了效能問題,晚上的時候,按照索引欄位的順序重新建立了表。
SQL>create table AB44_TEMP as select * from ab44 where 1=0;
SQL>INSERT /*+ append */INTO AB44_TEMP SELECT * FROM AB44 ORDER BY AAE002, AAE003, AAB001, AAE140;
SQL>commit;
SQL>drop table ab44;
SQL>alter table ab44_temp rename to ab44;
重新建立索引,分析表。重建後的聚簇因子只有60197,遠遠小於之前的 3299907。檢視執行計劃,也對了。
SQL> explain plan for select * from ab44 where aae002=201006;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------
Plan hash value: 2627288474
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10799 | 885K| 249 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| AB44 | 10799 | 885K| 249 (1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | AB44_TEMP_IND | 10799 | | 50 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AAE002"=201006)
已選擇14行。
而且為了驗證本例確實是由於聚簇因子過大佔了決定因素。我把重建後的表直方圖取消掉,重新查詢,每一個AAE002的值都是索引掃描了。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'NCSI',
TABNAME => 'AB44',
CASCADE => TRUE,
METHOD_OPT => 'for ALL columns SIZE 1');
END;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=201002;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=201006;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=198701;
SQL>EXPLAIN PLAN FOR SELECT * FROM ab44 WHERE aae002=199101;
SQL> select object_name,operation,options from plan_table where id=2;
OBJECT_NAME OPERATION OPTIONS
-------------------- ------------------------------ --------------------
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
AB44_TEMP_IND INDEX RANGE SCAN
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-666008/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- ORACLE 聚簇因子優化Oracle優化
- 優化全表掃描優化
- 【效能優化】Oracle 效能優化:降低列值聚簇因子 提高查詢效率優化Oracle
- 聚簇因子的理解
- oracle優化:避免全表掃描Oracle優化
- 隱形轉換導致全表掃描案例
- 優化Oracle with全表掃描的問題優化Oracle
- 關於索引聚簇因子(CLUSTERING_FACTOR)引起的sql優化過程索引SQL優化
- 優化Oracle with全表掃描的問題(二)優化Oracle
- 累計的力量,delete全表掃描導致程式執行時間過長。delete
- 聚簇表簡介
- sql導致資料庫整體效能下降的診斷和解決的全過程SQL資料庫
- Oracle JDBC驅動使用setDate()、setTimestamp()導致全表掃描OracleJDBC
- 一次.net code中的placeholder導致的高cpu診斷
- 【效能最佳化】Oracle 效能最佳化:降低列值聚簇因子 提高查詢效率Oracle
- oracle sql tuning 8--優化全表掃描OracleSQL優化
- 熊貓大俠一次效能診斷優化十一問優化
- 一條sql導致資料庫整體效能下降的診斷和解決的全過程(轉)SQL資料庫
- MySQL中的全表掃描和索引樹掃描MySql索引
- delete 與全表掃描delete
- 聚簇因子和執行計劃的聯絡
- Oracle聚簇表Oracle
- 【實驗】【聚簇】聚簇(Cluster)和聚簇表(Cluster Table)的建立與總結
- 查詢全表掃描的sqlSQL
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- 資料庫表,索引(索引組織表,聚簇表,聚簇索引,)資料庫索引
- ORACLE全表掃描查詢Oracle
- Oracle中存取資料掃描Table及索引的方式(全表掃描,索引掃描等)Oracle索引
- 效能分析(5)- 軟中斷導致 CPU 使用率過高的案例
- Spark效能優化:診斷記憶體的消耗Spark優化記憶體
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- 抓取全表掃描的表,篩選和分析
- 索引全掃描和索引快速全掃描的區別索引
- 查詢全表掃描語句
- 淺談索引系列之聚簇因子(clustering_factor)索引
- clustering factor索引聚簇因子和執行計劃索引
- React.js 一次動畫效能的簡單優化ReactJS動畫優化