0
0

统计信息收集后不生效的问题

小荷 发表于 2014年09月14日 11:06 | Hits: 1394
Tag: Working case | ..experience

客户的某系统升级到11g之后,收集统计信息却不生效,查dba_tables看不到其LAST_ANALYZED。这其实是因为11g的一个新特性,延时发布统计信息。

我们看下面的测试案例:

--建立一个测试表:
SQL> create table t_test as select * from dual;
 
Table created.
 
--检查这个publish属性,我们可以查系统级的熟悉和表级别的属性。在默认情况下,是true:即收集后立即发布:
--系统级的publish属性是true,收集后立即发布:
SQL> Select dbms_stats.GET_PREFS('PUBLISH') from dual;
 
DBMS_STATS.GET_PREFS('PUBLISH')
--------------------------------------------------------------------------------
TRUE
 
--表级的publish属性是true,收集后立即发布:
SQL> select dbms_stats.get_prefs('PUBLISH', 'TEST', 'T_TEST') publish from dual;
 
PUBLISH
--------------------------------------------------------------------------------
TRUE
 
SQL>

我们可以修改表的publish属性:

--我们可以设置这个表的pref,设置其publish为false,即延时发布,需要手工执行之后才能发布:
SQL> EXEC DBMS_STATS.set_table_prefs('TEST', 'T_TEST', 'PUBLISH', 'false');
 
PL/SQL procedure successfully completed.
 
SQL>
 
--更改后,检查是属性,已经变成false:
SQL> show user
USER is "TEST"
SQL>                                                                             
SQL> select dbms_stats.get_prefs('PUBLISH', 'TEST', 'T_TEST') publish from dual;
 
PUBLISH
--------------------------------------------------------------------------------
FALSE
 
SQL>

设置表的publish属性为false之后,我们来看看收集统计信息不立即生效的效果:

--收集前,统计信息的LAST_ANALYZED为空:
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T_TEST';
 
OWNER                          TABLE_NAME                     LAST_ANAL
------------------------------ ------------------------------ ---------
TEST                           T_TEST
 
--收集统计信息:
SQL> exec DBMS_STATS.gather_table_stats('TEST','T_TEST');
 
PL/SQL procedure successfully completed.
 
--我们看到,publish属性为fasle的情况下,收集完统计信息,还是不会立即发布,LAST_ANALYZED还是空:
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T_TEST';
 
OWNER                          TABLE_NAME                     LAST_ANAL
------------------------------ ------------------------------ ---------
TEST                           T_TEST
 
SQL>
 
--我们需要publish_pending_stats才能看到其统计信息发布:
SQL> EXEC DBMS_STATS.publish_pending_stats('TEST','T_TEST');
 
PL/SQL procedure successfully completed.
 
SQL> select OWNER,TABLE_NAME,LAST_ANALYZED from dba_tables where table_name='T_TEST';
 
OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ -------------------
TEST                           T_TEST                         2014-08-15 09:35:20
 
SQL>

publish属性作为表的属性一直保持:

--注意,再次收集统计信息的时候,还是上次收集的统计信息,即还是保持延时发布统计信息的特性:
SQL> exec DBMS_STATS.gather_table_stats('TEST','T_TEST');
 
PL/SQL procedure successfully completed.
 
SQL>
SQL>  select OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T_TEST'
  2  /
 
OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ -------------------
TEST                           T_TEST                         2014-08-15 09:35:20
 
--需要再次手工publish,才会更新统计信息:
SQL> EXEC DBMS_STATS.publish_pending_stats('TEST','T_TEST');
 
PL/SQL procedure successfully completed.
 
SQL> select OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T_TEST';
 
OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ -------------------
TEST                           T_TEST                         2014-08-15 09:42:27
 
SQL>

注:延时统计信息发布,只是对gather_xxx_stats才起作用。如果是用老式的analyze table xxx compute statistics,则不受此限制,还是会立即生效。

SQL> exec dbms_stats.delete_table_stats('TEST','T_TEST');
 
PL/SQL procedure successfully completed.
 
SQL> select OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T_TEST';
 
OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ -------------------
TEST                           T_TEST
 
 
SQL> analyze table TEST.T_TEST compute statistics;
 
Table analyzed.
 
SQL> select OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T_TEST';
 
OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZ
------------------------------ ------------------------------ -------------------
TEST                           T_TEST                         2014-08-15 09:50:09
 
SQL>

原文链接: http://www.oracleblog.org/working-case/statistics-not-take-effect/

0     0

评价列表(0)