STATISTICS_LEVEL
is set toTYPICAL
orALL
.
STATISTICS_LEVEL设置为TYPICAL
或ALL时,表监控特性默认是启动的。
INSERT
s,UPDATE
s, andDELETE
s 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.
USER_TAB_MODIFICATIONS视图获得关于表变化的信息。数据修改后,通过USER_TAB_MODIFICATIONS获取修改信息可能有一些延时。使用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO立即将延时信息保持到内存中。
GATHER_DATABASE_STATS
orGATHER_SCHEMA_STATS
procedures gather new statisticsfor tables with stale statistics when theOPTIONS
parameter is set to GATHER
STALE
orGATHER
AUTO
. If a monitored table has been modified more than10%, then these statistics are considered stale and gatheredagain.
or GATHER
AUTO
,GATHER_DATABASE_STATS
或GATHER_SCHEMA_STATS
为有过期统计信息的表搜集新的统计信息,如果一个监控的表修改超过了10%,则统计信息被认为过期,需再次搜集。
部分实验如下:
SQL> select * from test01;
---------- ----------
10 rowsselected.
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;
PL/SQL procedure successfully completed.
SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;
TABLE_NAME
------------------------------ ---------- ---------- -------------
TEST01
为更好的展现监控信息,接着进行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
------------------------------ ---------- ---------- -------------
TEST01
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL>
TABLE_NAME
------------------------------ ---------- ---------- -------------
TEST01
SQL> delete from test01 where a=77;
1 row deleted.
SQL> commit;
Commit complete.
SQL>
TABLE_NAME
------------------------------ ---------- ---------- -------------
TEST01
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
------------------------------ ---------- ---------- -------------
TEST01
SQL> truncate table test01;
Table truncated.
SQL> selecttable_name,INSERTS,UPDATES,DELETES,truncated fromuser_tab_modifications;
TABLE_NAME
------------------------------ ---------- ---------- -------------
TEST01
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
------------------------------ ---------- ---------- -------------
TEST01
对表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