優化案例--重建索引引發的sql效能問題
週一開發人員報告說上週六一個job跑了整整一天都沒有執行完，但是到了週日該job執行又恢復正常，要求查詢一下原因
首先生成了一下週六當天的AWR報告，檢視sql部分的資訊
發現buffer gets排名第二高sql的executions為0，與開發人員確認，確實是該儲存過程
檢查該儲存過程pkd_justin.pro_execute(sysdate)，裡面針對表justin的所有查詢大致如下
select *
from justin t
where t.time < trunc(sysdate) + 1
and t.time >= trunc(sysdate);
檢視該語句對應的執行計劃，由於job執行的都是前一天的資料，所以檢視週五當天的，執行計劃明顯有問題
SQL> explain plan for select * from justin t
2 where t.time < trunc(sysdate-3) + 1
3 and t.time >= trunc(sysdate-3);
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1449K| 710M| 1 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR | | 1449K| 710M| 1 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| justin | 1449K| 710M| 1 (0)| 00:00:01 | KEY | KEY |
|* 4 | INDEX SKIP SCAN | PK_justin | 15258 | | 1 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-3)+1>TRUNC(SYSDATE@!-3))
4 - access("T"."time">=TRUNC(SYSDATE@!-3) AND "T"."time"=TRUNC(SYSDATE@!-3))
檢視索引分割槽統計資訊，相比之下，2月19號對應的分割槽統計資訊有問題
SQL> select index_name,partition_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows,sample_size from user_ind_partitions where partition_name in('P219','P220','P218') order by partition_name;
INDEX_NAME PARTITION_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------- ---------- -----------
PK_justin P218 2 5055 1450676 113018 1450676 322833
IDX_justin_TIME_D P218 2 3854 82474 364667 1452752 1452752
IDX_justin_TIME_D P219 1 25 100 800 2500 1452752
PK_justin P219 0 0 0 0 0
IDX_justin_TIME_D P220 2 3854 82474 364667 1452752 1452752
PK_justin P220 2 5055 1450676 113018 1450676 322833
18 rows selected
檢視user_objects,可以看到分割槽P219對應的索引在18日也就是週五下午重建過，由此可以大致推斷出原因，由於索引重建後沒有收集統計資訊
導致第二天job執行的時候選擇了錯誤的執行計劃
SQL> select object_name, to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from user_objects where subobject_name ='P219';
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'YYYY-MM
-------------------------------------------------------------------------------- ------------------------------
PK_justin 2011-02-18 17:42:14
IDX_justin_TIME_D 2011-02-18 17:42:19
justin 2011-01-11 14:34:52
重新收集一下該分割槽上的所有索引的統計資訊, 由於
SQL> begin
2 dbms_stats.gather_table_stats(ownname => user,tablename => 'justin',partname => 'P219',force => true,no_invalidate => false);
3 end;
4 /
PL/SQL procedure successfully completed
然後在檢視統計資訊
SQL> select index_name,partition_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows,sample_size from user_ind_partitions where partition_name in('P219') order by partition_name;
INDEX_NAME PARTITION_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------- ---------- -----------
PK_justin P219 2 5884 1688581 130830 1688581 319144
IDX_justin_TIME_D P219 2 4510 83136 419426 1700253 421486
檢視執行計劃，已經恢復正常
SQL> explain plan for select *
2 from justin t
3 where t.time < trunc(sysdate - 3) + 1
4 and t.time >= trunc(sysdate - 3);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 926600260
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1449K| 710M| 24462 (2)| 00:04:54 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1449K| 710M| 24462 (2)| 00:04:54 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | justin | 1449K| 710M| 24462 (2)| 00:04:54 | KEY | KEY |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-3)+1>TRUNC(SYSDATE@!-3))
3 - filter("T"."time"=TRUNC(SYSDATE@!-3))
至此該問題算是告一段落
總結: oracle在9i的時候，建立或者重建索引的時候需要顯示指定compute statistics才會收集統計資訊，演化到10g，該功能已變成預設的；
我們資料庫版本是10.2.0.5，卻遇到此問題，那是因為該表上所有分割槽的統計資訊都已被lock;
這個案例也為我們提了個醒，以後重建索引後需要檢查一下其相應統計資訊才行
SQL> select partition_name,stattype_locked from user_tab_statistics where table_name='JUSTIN' and partition_name ='P219';
PARTITION_NAME STATTYPE_LOCKED
------------------------------ ---------------
P219 ALL
首先生成了一下週六當天的AWR報告，檢視sql部分的資訊
發現buffer gets排名第二高sql的executions為0，與開發人員確認，確實是該儲存過程
檢查該儲存過程pkd_justin.pro_execute(sysdate)，裡面針對表justin的所有查詢大致如下
select *
from justin t
where t.time < trunc(sysdate) + 1
and t.time >= trunc(sysdate);
檢視該語句對應的執行計劃，由於job執行的都是前一天的資料，所以檢視週五當天的，執行計劃明顯有問題
SQL> explain plan for select * from justin t
2 where t.time < trunc(sysdate-3) + 1
3 and t.time >= trunc(sysdate-3);
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1449K| 710M| 1 (0)| 00:00:01 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR | | 1449K| 710M| 1 (0)| 00:00:01 | KEY | KEY |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| justin | 1449K| 710M| 1 (0)| 00:00:01 | KEY | KEY |
|* 4 | INDEX SKIP SCAN | PK_justin | 15258 | | 1 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-3)+1>TRUNC(SYSDATE@!-3))
4 - access("T"."time">=TRUNC(SYSDATE@!-3) AND "T"."time"
檢視索引分割槽統計資訊，相比之下，2月19號對應的分割槽統計資訊有問題
SQL> select index_name,partition_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows,sample_size from user_ind_partitions where partition_name in('P219','P220','P218') order by partition_name;
INDEX_NAME PARTITION_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------- ---------- -----------
PK_justin P218 2 5055 1450676 113018 1450676 322833
IDX_justin_TIME_D P218 2 3854 82474 364667 1452752 1452752
IDX_justin_TIME_D P219 1 25 100 800 2500 1452752
PK_justin P219 0 0 0 0 0
IDX_justin_TIME_D P220 2 3854 82474 364667 1452752 1452752
PK_justin P220 2 5055 1450676 113018 1450676 322833
18 rows selected
檢視user_objects,可以看到分割槽P219對應的索引在18日也就是週五下午重建過，由此可以大致推斷出原因，由於索引重建後沒有收集統計資訊
導致第二天job執行的時候選擇了錯誤的執行計劃
SQL> select object_name, to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from user_objects where subobject_name ='P219';
OBJECT_NAME TO_CHAR(LAST_DDL_TIME,'YYYY-MM
-------------------------------------------------------------------------------- ------------------------------
PK_justin 2011-02-18 17:42:14
IDX_justin_TIME_D 2011-02-18 17:42:19
justin 2011-01-11 14:34:52
重新收集一下該分割槽上的所有索引的統計資訊, 由於
SQL> begin
2 dbms_stats.gather_table_stats(ownname => user,tablename => 'justin',partname => 'P219',force => true,no_invalidate => false);
3 end;
4 /
PL/SQL procedure successfully completed
然後在檢視統計資訊
SQL> select index_name,partition_name,blevel,leaf_blocks,distinct_keys,clustering_factor,num_rows,sample_size from user_ind_partitions where partition_name in('P219') order by partition_name;
INDEX_NAME PARTITION_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------- ---------- -----------
PK_justin P219 2 5884 1688581 130830 1688581 319144
IDX_justin_TIME_D P219 2 4510 83136 419426 1700253 421486
檢視執行計劃，已經恢復正常
SQL> explain plan for select *
2 from justin t
3 where t.time < trunc(sysdate - 3) + 1
4 and t.time >= trunc(sysdate - 3);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 926600260
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1449K| 710M| 24462 (2)| 00:04:54 | | |
|* 1 | FILTER | | | | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1449K| 710M| 24462 (2)| 00:04:54 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | justin | 1449K| 710M| 24462 (2)| 00:04:54 | KEY | KEY |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TRUNC(SYSDATE@!-3)+1>TRUNC(SYSDATE@!-3))
3 - filter("T"."time"
至此該問題算是告一段落
總結: oracle在9i的時候，建立或者重建索引的時候需要顯示指定compute statistics才會收集統計資訊，演化到10g，該功能已變成預設的；
我們資料庫版本是10.2.0.5，卻遇到此問題，那是因為該表上所有分割槽的統計資訊都已被lock;
這個案例也為我們提了個醒，以後重建索引後需要檢查一下其相應統計資訊才行
SQL> select partition_name,stattype_locked from user_tab_statistics where table_name='JUSTIN' and partition_name ='P219';
PARTITION_NAME STATTYPE_LOCKED
------------------------------ ---------------
P219 ALL
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-688367/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）Oracle優化索引SQL
- 效能優化案例-SQL優化優化SQL
- SQL效能優化案例分析SQL優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- Oracle優化案例-IB網及會話登陸審計引發的效能問題（十七）Oracle優化會話
- 一個SQL效能問題的優化探索SQL優化
- SQL優化案例-正確的使用索引（二）SQL優化索引
- oracle效能問題：sql語句優化OracleSQL優化
- SQL Server SQL效能優化之引數化SQLServer優化
- sql server datediff函式引發的效能問題SQLServer函式
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- 【SQL 效能優化】引數設定SQL優化
- SQL優化案例-自定義函式索引（五）SQL優化函式索引
- 效能優化問題優化
- 資料庫效能優化-索引與sql相關優化資料庫優化索引SQL
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- SQL優化-索引SQL優化索引
- SQL Server索引優化系列之二：索引效能考慮 (轉)SQLServer索引優化
- SQL SERVER資料庫datediff函式引發的效能問題SQLServer資料庫函式
- MySQL 效能優化之索引優化MySql優化索引
- 優化由直方圖資訊導致的sql效能問題優化直方圖SQL
- 全文索引的效能優化索引優化
- SQL優化--not in和or出的問題SQL優化
- SQL優化引出的問題（二）SQL優化
- SQL優化引出的問題（一）SQL優化
- 【案例】常駐查詢引發的thread pool 效能問題之二thread
- 【案例】常駐查詢引發的thread pool 效能問題之一thread
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- reflow和repaint引發的效能問題AI
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- 一個效能優化的案例優化
- iOS開發那些事-效能優化–autorelease的使用問題iOS優化
- 關於sap效能優化的問題優化
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- sql優化案例一:使用了表示式不會使用索引SQL優化索引