[20141006]analyze与dbms_stats.txt

--别人问一个奇怪的问题,如何知道表使用analyze分析的还是使用dbms_stats分析的.
--一般oracle不建议再使用analyze来分析表,这个命令保留分析表和索引的完整性,以及分析表是否存在行链接还是行迁移.
--不过,问题提出来,还是看看有什么方法确定?

1.建立测试环境:
@ver
SCOTT@test01p> @ver

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

create table t1 as select rownum id ,cast('name' as varchar2(10)) name from dual connect by level<=1e4;
create table t2 as select rownum id ,cast('name' as varchar2(10)) name from dual connect by level<=1e4;
analyze table t1 compute statistics;
exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T2', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

2.如何确定使用analyze分析的还是使用dbms_stats分析的:
--只能看看几个视图,看看是否可以找到线索:
SCOTT@test01p> select table_name,last_analyzed,avg_space,avg_row_len,global_stats,user_stats from dba_tables where owner=user and table_name in ('T1','T2');
TABLE_NAME LAST_ANALYZED        AVG_SPACE AVG_ROW_LEN GLO USE
---------- ------------------- ---------- ----------- --- ---
T1         2014-10-06 22:17:19       1056          12 NO  NO
T2         2014-10-06 22:17:28          0           9 YES NO

--对比可以发现,其它字段的信息都一样,仅仅可以发现global_stats不同,也许可以看出global_stats='NO'表示使用analyze分析的.
--当然可以发现avg_space不同.但是通过global_stats确定还是比较准确的.

3.补充一些内容:
--如果lock表统计信息,从那里看出来呢?
SCOTT@test01p> exec DBMS_STATS.LOCK_TABLE_STATS (ownname=>user, tabname=>'T1');
PL/SQL procedure successfully completed.

--通过这个dba_tables视图看不出来,自己经常忘记,还有DBA_TAB_STATISTICS视图.

SELECT table_name,
       last_analyzed,
       avg_space,
       avg_row_len,
       global_stats,
       user_stats,
       stattype_locked
  FROM DBA_TAB_STATISTICS
WHERE owner = USER AND table_name IN ( 'T1', 'T2');

TABLE_NAME LAST_ANALYZED        AVG_SPACE AVG_ROW_LEN GLO USE STATT
---------- ------------------- ---------- ----------- --- --- -----
T1         2014-10-06 22:17:19       1056          12 NO  NO  ALL
T2         2014-10-06 22:17:28          0           9 YES NO

-- stattype_locked='ALL' ,表示这个表的统计信息被lock锁定.

SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T1', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');
BEGIN dbms_stats.gather_table_stats(ownname=>user, tabname=>'T1', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 33859
ORA-06512: at line 1

--从这里也推断出来与stattype=ALL有关.必须加入force=>true.

SCOTT@test01p> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T1', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1',force=>true);
PL/SQL procedure successfully completed.