Oracle確定到期的統計資訊

Youmust regularly gather statistics on database objects as thesedatabase objects are modified over time. To determine whether agiven database object needs new database statistics, OracleDatabase provides a table monitoring facility. This monitoring isenabled by default when STATISTICS_LEVEL is set toTYPICAL orALL.

經過一段時間，隨著資料庫物件被修改，必須定期搜集統計資訊。為了確定資料庫物件需要新的資料庫統計資訊，oracle資料庫提供了一個表監控特性。當STATISTICS_LEVEL設定為TYPICAL或ALL時，表監控特性預設是啟動的。

 

Monitoring tracks the approximate number of INSERTs,UPDATEs, andDELETEs for that table andwhether the table has been truncated since the last time statisticswere gathered. You can access information about changes of tablesin theUSER_TAB_MODIFICATIONS view. Following adata-modification, there may be a few minutes delay while OracleDatabase propagates the information to this view. Use theDBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure toimmediately reflect the outstanding monitored information kept inthe memory.

 

表監控特性跟蹤從最後一次統計搜集後，表的insert、update、delete操作的近似數，和表是否被truncate。可以通過查詢USER_TAB_MODIFICATIONS視圖獲得關於表變化的資訊。資料修改後，通過USER_TAB_MODIFICATIONS擷取修改資訊可能有一些延時。使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO立即將延時資訊保持到記憶體中。

 

TheGATHER_DATABASE_STATS orGATHER_SCHEMA_STATS procedures gather new statisticsfor tables with stale statistics when theOPTIONSparameter is set to GATHER STALE orGATHERAUTO. If a monitored table has been modified more than10%, then these statistics are considered stale and gatheredagain.

 

當屬性OPTIONS設定為GATHER STALEor GATHERAUTO，GATHER_DATABASE_STATS或GATHER_SCHEMA_STATS為有到期統計資訊的表搜集新的統計資訊，如果一個監控的表修改超過了10%，則統計資訊被認為到期，需再次搜集。


 

部分實驗如下：

SQL> select * from test01;

A B
---------- ----------
21 10
9 10
22 10
23 10
24 10
25 10
1 1
2 2
44 44
55 55

10 rowsselected. ------表資料10行

SQL> select * fromuser_tab_modifications;

no rowsselected ------實驗前已對測試庫做了搜集統計資訊，此時顯示為空白

SQL> insert into test01 values(66,66);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from user_tab_modifications;

no rowsselected ------表的修改未及時顯示

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; ------將修改的資訊keep到記憶體

PL/SQL procedure successfully completed.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 10 0 NO ------ 1 代表剛才的1條insert

 

為更好的展現監控資訊，接著進行update、delete、truncate操作示範：

SQL> update test01 set a=77 where b=66;

1 row updated.

SQL> commit;

Commit complete.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 1 0 0 NO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 1 1 0 NO

SQL> delete from test01 where a=77;

1 row deleted.

SQL> commit;

Commit complete.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 1 1 0 NO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 1 1 1 NO

SQL> truncate table test01;

Table truncated.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 1 1 1 NO

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES TRU
------------------------------ ---------- ---------- -------------
TEST01 1 1 11YES

 

對錶test01執行搜集統計資訊，監控表的資訊會清除：

SQL> execdbms_stats.gather_table_stats(null,'test01');

PL/SQL procedure successfully completed.

SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;

no rows selected