[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 levelcreate table t2 as select rownum id ,cast('name' as varchar2(10)) name from dual connect by levelanalyze 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.