優化器統計自動收集的一點總結

從10g開始 oracle預設會自動收集優化器統計資訊，自動蒐集只會為下面2種情況蒐集優化器統計資訊：

1、missing statistics

2、stale statistics

[@more@]

1、missing statistics

如果sql語句中訪問的物件沒有optimizer statistics，那麼oracle會自動估算optimizer statistics，但是這樣會影響sql的解析效率

2、stale statistics

statistics陳舊是指表裡的資料修改超過10%，oracle從10g開始自動對錶上的dml和truncate操作進行監控，自動監控的條件是：

statistics_level為typical或者all，監控的結果可以通過dba_tab_modifications來檢視。資料被修改之後oracle監控的結果
是駐留在記憶體裡面的，所以不是修改之後就可以實時的在dba_tab_modifications裡體現出來。8i的時侯每隔3小時oracle會把監控的結果從記憶體重新整理到disk，9i的時侯這個間隔調整為15分鐘，如果你想手動重新整理，可以使用過程：
dbms_stats.flush_database_monitoring_info.
下面是簡單的測試過程：
--===============================
SQL> create table t tablespace users as select * from dba_objects where 1=2;

Table created.

SQL> exec dbms_stats.gather_table_stats('A','T');

PL/SQL procedure successfully completed.

SQL> insert into t select * from dba_objects;

18008 rows created.

SQL> commit;

Commit complete.

SQL> select * from ALL_tab_modifications where table_owner='A';

no rows selected

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select a.table_name,a.inserts,a.updates,a.deletes,a.timestamp,a.truncated,a.drop_segments from user_tab_modifications a;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------------- --- -------------
T 18008 0 0 2013/07/27 19:37:33 NO 0

SQL> insert into t select * from dba_objects;

18008 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select a.table_name,a.inserts,a.updates,a.deletes,a.timestamp,a.truncated,a.drop_segments from user_tab_modifications a;

TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ---------- ---------- ---------- ------------------- --- -------------
T 36016 0 0 2013/07/27 19:41:52 NO 0

SQL>
--===============================
optimizer statistics是否stale，可以通過下面過程驗證：
SQL> exec dbms_stats.gather_table_stats('A','T');

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics a where table_name='T';

STA
---
NO

SQL> insert into t select * from dba_objects;

18008 rows created.

SQL> commit;

Commit complete.

SQL> select stale_stats from user_tab_statistics a where table_name='T';

STA
---
NO

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

SQL> select stale_stats from user_tab_statistics a where table_name='T';

STA
---
YES

SQL>

--=============================
有關對錶資料的修改的自動監控是從10g開始的，10g以前需要通過下面命令開啟對錶的monitoring：
SQL> select table_name,monitoring from user_tables where table_name='T';

TABLE_NAME MON
------------------------------ ---
T YES

SQL> alter table t nomonitoring;

Table altered.

SQL> select table_name,monitoring from user_tables where table_name='T';

TABLE_NAME MON
------------------------------ ---
T YES

SQL> alter table t monitoring;

Table altered.

SQL> select table_name,monitoring from user_tables where table_name='T';

TABLE_NAME MON
------------------------------ ---
T YES

SQL>
--=====================
所以從10g開始單獨對錶設定monitoring或者nomonitoring已經不起作用了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/19602/viewspace-1060786/，如需轉載，請註明出處，否則將追究法律責任。