Oracle确定过期的统计信息
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 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.
表监控特性跟踪从最后一次统计搜集后,表的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 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.
当属性OPTIONS设置为GATHER STALE
or GATHER
AUTO
,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
评论暂时关闭