關於oracle自動收集統計資訊

fufuh2o發表於2010-06-26
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章