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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-730954/