ITPub博客

首页 > Linux操作系统 > Linux操作系统 > user_tab_modifications学习

user_tab_modifications学习

原创 Linux操作系统 作者:wei-xh 时间:2011-03-11 17:31:48 0 删除 编辑

To recognize whether object statistics are stale, the database engine counts the number of rows modified
through SQL statements for each object. The result of that counting is externalized through the data dictionary
views all_tab_modifications, dba_tab_modifications, and user_tab_modifications. The
following query is an example:
SQL> SELECT inserts, updates, deletes, truncated
2 FROM user_tab_modifications
3 WHERE table_name = 'T';

INSERTS UPDATES DELETES TRUNCATED
---------- ---------- ---------- ----------
775 16636 66 NO
Based on this information, the package dbms_stats is able to determine whether the statistics associated
with a specific object are stale. Up to Oracle Database 10g, the statistics are considered stale if at least
10 percent of the rows have been modified. As of Oracle Database 11g, you can configure the threshold through the
parameter stale_percent. Its default value is 10 percent. Later in this chapter, the section “Configuring the
Package dbms_stats: The 11g Way” will show how to change it.
In Oracle9i, counting is enabled only when it is explicitly specified at the table level. Concretely, this is
carried out by specifying the option monitoring through the CREATE TABLE or ALTER TABLE statement.
To enable it easily for a whole schema, or even for the whole database, the package dbms_stats provides the
procedures alter_schema_tab_monitoring and alter_database_tab_monitoring, respectively. Note
that these procedures just execute an ALTER TABLE statement on all available tables. In other words, the
setting has no impact on tables created after their execution.
As of Oracle Database 10g, the option monitoring is deprecated. Counting is controlled databasewide
by the initialization parameter statistics_level. If it is set to either typical (which is the default value)
or all, counting is enabled.

设定:SQL>  exec dbms_stats.set_table_prefs('APOLLO','EMP','STALE_PERCENT',1);
修改为 1%. 范围从 1-100.
恢复:
SQL> exec dbms_stats.set_table_prefs('APOLLO','EMP','STALE_PERCENT',null);
查询:
SQL> select dbms_stats.get_prefs('STALE_PERCENT','APOLLO','EMP') A from dual;
A
------------------------------
10

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-689193/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2315201