关于oracle自动收集统计信息

根据metalink ID 276358.1 ,看下10g 自动收集统计信息是在什么条件下

DBMS_STATS AUTOSTATS_TARGET
---------------------------
 
This is a new parameter in Oracle10g for the DBMS_STATS package.

According to the documentation for this package in file dbmsstat.sql
(under ORACLE_HOME/rdbms/admin):

This parameter is applicable only for auto stats collection.
The value of this parameter controls the objects considered for stats collection.
It takes the following values:
 'ALL'    -- statistics collected for all objects in system
 'ORACLE' -- statistics collected for all oracle owned objects
 'AUTO'   -- oracle decides for which objects to collect stats

In Oracle10g and Oracle11g Release 1 'ALL' and 'AUTO' are equivalent and 'AUTO' is the default.

可以看到 文档中说all=auto,而这个意思开始另我误解成了auto就是收集all的所有统计信息(statistics collected for all objects in system)

 

_optimizer_autostats_job 是否自动 收集 也受 这个隐藏参数控制,false代表不收集,默认是true

#每天晚上10点跑的 自动收集 实际就是执行  execute dbms_stats.gather_database_stats_job_proc

#object统计信息的时效性(所谓的过时的统计信息)
关于*_tab_modifications
Columns
   ___________________________
 
   TABLE_OWNER
      Owner of modified table
   TABLE_NAME
      Modified table
   PARTITION_NAME
      Modified partition
   SUBPARTITION_NAME
      Modified subpartition
   INSERTS
      Approximate number of rows inserted since last analyze
   UPDATES
      Approximate number of rows updated since last analyze
   DELETES
      Approximate number of rows deleted since last analyze
   TIMESTAMP
      Timestamp of last time this row was modified*********************
   TRUNCATED
      Was this object truncated since the last analyze

Oracle uses these views to identify tables with stale statistics.
Whenever there is 10% change in data in a table, Oracle considers its
statistics to be stale.

oracle 用记录在此表中的信息 来决定统计信息的时效性，当sql执行时oracle查看此表看修改表数据的行数，10G 超过10%就算过期了dbms_stats就用这个判断(11g可以修改这个阀值)

In 10G, the MONITORING and NOMONITORING keywords have been deprecated.
If you do specify these keywords, they are ignored.

In 10g, table-monitoring feature is controlled by the STATISTICS_LEVEL
parameter.

When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.

When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.

Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE
operations for the table since the last time statistics were gathered.
information about how many rows are affected is maintained in the SGA,
until periodically (about every 15 minutes) SMON flush the data into the data
dictionary.
可以看到oracle说是15分钟smon 执行刷新操作

 

#首先创建一个表,insert一行,然后手动刷新
SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

 

SQL> select * from user_tab_modifications where table_name='YY';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
YY                                                                                                    1          0          0 22-JUN-10 NO              0

 

 

 

 

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

TO_CHAR(LAST_ANALYZ OWNER                          MON
------------------- ------------------------------ ---
                    XH                             YES

 

SQL> execute dbms_output.put_line(dbms_stats.get_param(pname=>'autostats_target'))
AUTO

PL/SQL procedure successfully completed.

 

SQL> execute dbms_stats.gather_database_stats_job_proc;(SYS 执行)

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

TO_CHAR(LAST_ANALYZ OWNER                          MON
------------------- ------------------------------ ---
2010-06-22 07:51:33 XH                             YES

SQL> select * from user_tab_modifications where table_name='YY';

no rows selected

 

SQL> delete xh.yy
  2  ;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

SQL> select * from user_tab_modifications where table_name='YY';

no rows selected

SQL> execute dbms_stats.gather_database_stats_job_proc;(SYS 执行)

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where table_name='YY';

no rows selected

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

TO_CHAR(LAST_ANALYZ OWNER                          MON
------------------- ------------------------------ ---
2010-06-22 08:06:48 XH                             YES

 

SQL> execute dbms_stats.gather_database_stats_job_proc;(SYS 执行)

PL/SQL procedure successfully completed.

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

TO_CHAR(LAST_ANALYZ OWNER                          MON
------------------- ------------------------------ ---
2010-06-22 08:06:48 XH                             YES

#从这个看到 即便 SMON 还没刷新到 user_tab_modifications ,但执行自动收集时 SMON 会先刷下,然后在收集,另外没过期的 不会刷,10g是10% 的行数变动就过期了

 

SQL> insert into yy values(2);

1 row created.

SQL> execute dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select * from user_tab_modifications where table_name='YY';

TABLE_NAME                     PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS    UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- --------- --- -------------
YY                                                                                                    1          0          0 22-JUN-10 NO              0

 

SQL> execute dbms_stats.gather_database_stats_job_proc;

PL/SQL procedure successfully completed.

 

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

TO_CHAR(LAST_ANALYZ OWNER                          MON
------------------- ------------------------------ ---
2010-06-22 10:26:10 XH                             YES

SQL> select * from user_tab_modifications where table_name='YY';

no rows selected

#从这里可以看到一个问题,只要修改了数据,产生了dirty buffer,即便没提交(以后ROLLBACK)但是刷新到user_tab_modifications后,自动收集时 还是按 过期的统计信息算,收集了

************
SQL> execute dbms_stats.set_param(pname=>'autostats_target',pval=>'ALL');

PL/SQL procedure successfully completed.

SQL> execute dbms_output.put_line(dbms_stats.get_param(pname=>'autostats_target'))
ALL

PL/SQL procedure successfully completed

 

 

SQL> execute dbms_stats.gather_database_stats_job_proc;

PL/SQL procedure successfully completed.

 

 

SQL> select to_char(last_analyzed,'YYYY-MM-DD HH24:MI:SS'),owner,monitoring from dba_tables where table_name='YY';

TO_CHAR(LAST_ANALYZ OWNER                          MON
------------------- ------------------------------ ---
2010-06-22 10:26:10 XH                             YES

SQL> select * from user_tab_modifications where table_name='YY';

no rows selected

#可以看到all=auto在11GR1 都是如此,只分析 过实效性的

 

 

 

Optimizer statistics are automatically gathered with the job GATHER_STATS_JOB. This job gathers statistics on all objects in the database which have:

    *

      Missing statistics  收集缺少的统计信息
    *

      Stale statistics    收集过期的统计信息

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-666411/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-666411/