dbms_stats.lock_table_stats與動態取樣（Dynamic Sampling）

有同事來問，想刪除一個index的統計資訊，原因是Oracle的執行計劃不對．

但是我們的系統是有自動收集統計資訊的，所以僅刪除還不行，還應lock住不讓分析．

仔細想想，還有個Dynamic Sampling呢？

1.看如何lock住不讓分析index

  檢視包dbms_stat裡的方法發現：lock_table_stats，lock_partition_stats，lock_schema_stats
　沒有直接lock index的（鬱悶，Oracle怎麼能這樣）
　
　那測試下lock_table_stats能否lock index

SQL> create table t  as select object_id from all_objects where rownum<=100;
 
Table created
 
SQL> create index idx_t_1 on t (object_id);
 
Index created
 
SQL> select stattype_locked from user_tab_statistics where table_name='T';
 
STATTYPE_LOCKED
---------------
 
SQL> select stattype_locked from user_ind_statistics where table_name='T';
 
STATTYPE_LOCKED
---------------
 
 
SQL>
SQL> begin
  2    dbms_stats.lock_table_stats(user,'T');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 
SQL> select stattype_locked from user_tab_statistics where table_name='T';
 
STATTYPE_LOCKED
---------------
ALL
 
SQL> select stattype_locked from user_ind_statistics where table_name='T';
 
STATTYPE_LOCKED
---------------
ALL
 
SQL>
SQL> analyze table t compute statistics;
 
analyze table t compute statistics
 
ORA-38029: object statistics are locked
 
SQL> analyze index idx_t_1 compute statistics;
 
analyze index idx_t_1 compute statistics
 
ORA-38029: object statistics are locked     --INDEX LOCK 了，不能進行統計資訊收集了．

2. 那會不會 Dynamic Sampling 呢？

SQL> explain plan for
  2  select * from t where object_id=1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2661656778
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T_1 |     1 |    13 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=1)
Note
-----
   - dynamic sampling used for this statement　　　　-- 進行了dynamic sampling
 

結論：去除統計資訊後，dynamic sampling 還是會收集的．通過刪除統計資訊來改變ＳＱＬ的執行計劃，是不可行的．正途還是應該從ＳＱＬ入手來分析．

附
關閉dynamic sampling的方法

設定optimizer_dynamic_sampling=0;
 
SQL> alter session set optimizer_dynamic_sampling=0;
 
Session altered
 
SQL> explain plan for
  2  select * from t where object_id=1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2661656778
----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |     7 |    91 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T_1 |     7 |    91 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=1)
 
沒有進行dynamic sampling

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-730954/，如需轉載，請註明出處，否則將追究法律責任。