ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_stats.lock_table_stats与动态采样(Dynamic Sampling)

dbms_stats.lock_table_stats与动态采样(Dynamic Sampling)

原创 Linux操作系统 作者:denglt 时间:2012-05-25 14:57:50 0 删除 编辑

有同事来问,想删除一个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 还是会收集的.通过删除统计信息来改变SQL的运行计划,是不可行的.正途还是应该从SQL入手来分析.


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

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    706338