MON_MODS$和MON_MODS_ALL$统计DML操作次数
*_TAB_MODIFICATIONS视图是用来记录自从收集统计信息后的DML(包括truncate)操作的次数,通过试验分析数据库是如何实现该功能,并且应用该功能来实现数据库自动收集功能(表变化比例决定是否收集统计信息)
这里很清楚,通过union all关联了三个select 语句,分别是查询普通表,分区表,子分区表,这里也可以看出来
对应基表
这两个基表结构完全相同,通过收集信息dml操作MON_MODS$然后记录MON_MODS_ALL$中
测试MON_MODS$和MON_MODS_ALL$关系
这里测试证明DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO实现MON_MODS$刷新到MON_MODS_ALL$,但是未完全刷新MON_MODS$
测试MON_MODS_ALL$
从10GR2开始,数据库每15分钟就要把数据库的DML操作写入到mon_mods$(从SGA中写入到mon_mod$),但是这个写入过程1分钟,因此可能不是全部记录所有DML操作到mon_mods$.默认情况下,数据库每天会写入SGA中表的DML操作和mon_mods$到mon_mods_all$,也可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来人工写入到mon_mods_all$中,收集统计信息后mon_mods_all$表中信息清空
mon_mods$ is used for DML monitoring
链接: http://www.eygle.com/archives/2009/09/mon_mods_is_use.html
Parse Calls | Executions | % Total Parses | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|
784 | 784 | 1.50 | 350f5yrnnmshs | lock table sys.mon_mods$ in ex... | |
784 | 784 | 1.50 | g00cj285jmgsw | update sys.mon_mods$ set inser... |
SQL的完整文本如下:
lock table sys.mon_mods$ in exclusive mode nowait
update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
那么这两个SQL是做什么用的呢?其底层表mon_mods$的功能又是什么呢?
从Oracle的代码里可以看到这个表是进行DML跟踪的,Oracle的自动统计数据收集功能会据此判断哪些表的数据发生了变化,需要进行重新的信息收集:
Rem DML monitoring在某些版本中,会因为这个语句导致如下错误提示:
create table mon_mods$
(
obj# number, /* object number */
inserts number, /* approx. number of inserts since last analyze */
updates number, /* approx. number of updates since last analyze */
deletes number, /* approx. number of deletes since last analyze */
timestamp date, /* timestamp of last time this row was changed */
flags number, /* flags */
/* 0x01 object has been truncated */
drop_segments number /* number of segemnt in part/subpartition table */
)
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_mon_mods$_obj on mon_mods$(obj#)
storage (maxextents unlimited)
/
Rem DML monitoring, has info aggregated to global level for paritioned objects
create table mon_mods_all$
(
obj# number, /* object number */
inserts number, /* approx. number of inserts since last analyze */
updates number, /* approx. number of updates since last analyze */
deletes number, /* approx. number of deletes since last analyze */
timestamp date, /* timestamp of last time this row was changed */
flags number, /* flags */
/* 0x01 object has been truncated */
drop_segments number /* number of segemnt in part/subpartition table */
)
storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_mon_mods_all$_obj on mon_mods_all$(obj#)
storage (maxextents unlimited)
/
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
如果数据库关闭了自动统计信息收集功能,启用这个监控就是没有必要的了。
-The End-
Rem DML monitoring create table mon_mods$ ( obj# number, /* object number */ inserts number, /* approx. number of inserts since last analyze */ updates number, /* approx. number of updates since last analyze */ deletes number, /* approx. number of deletes since last analyze */ timestamp date, /* timestamp of last time this row was changed */ flags number, /* flags */ /* 0x01 object has been truncated */ drop_segments number /* number of segemnt in part/subpartition table */ ) storage (initial 200K next 100k maxextents unlimited pctincrease 0) / create unique index i_mon_mods$_obj on mon_mods$(obj#) storage (maxextents unlimited) / Rem DML monitoring, has info aggregated to global level for paritioned objects create table mon_mods_all$ ( obj# number, /* object number */ inserts number, /* approx. number of inserts since last analyze */ updates number, /* approx. number of updates since last analyze */ deletes number, /* approx. number of deletes since last analyze */ timestamp date, /* timestamp of last time this row was changed */ flags number, /* flags */ /* 0x01 object has been truncated */ drop_segments number /* number of segemnt in part/subpartition table */ ) storage (initial 200K next 100k maxextents unlimited pctincrease 0) / create unique index i_mon_mods_all$_obj on mon_mods_all$(obj#) storage (maxextents unlimited) / Rem ========================================================================= Rem End Usage monitoring tables Rem ========================================================================= VIEW DBA_TAB_MODIFICATIONS select u.name, o.name, null, null, m.inserts, m.updates, m.deletes, m.timestamp, decode(bitand(m.flags,1),1,'YES','NO'), m.drop_segments from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user# union all select u.name, o.name, o.subname, null, m.inserts, m.updates, m.deletes, m.timestamp, decode(bitand(m.flags,1),1,'YES','NO'), m.drop_segments from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19 union all select u.name, o.name, o2.subname, o.subname, m.inserts, m.updates, m.deletes, m.timestamp, decode(bitand(m.flags,1),1,'YES','NO'), m.drop_segments from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2, sys.user$ u where o.obj# = m.obj# and o.owner# = u.user# and o.obj# = tsp.obj# and o2.obj# = tsp.pobj#现象: SMON后台进程会每15分钟将SGA中的DML统计信息刷新到SYS.MON_MODS$基表中(SMON flush every 15 minutes to SYS.MON_MODS$), 同时会将SYS.MON_MODS$中符合要求的数据MERGE合并到MON_MODS_ALL$中,并清空原MON_MODS$中的数据。 MON_MODS_ALL$作为dba_tab_modifications视图的数据来源,起到辅助统计信息收集的作用,详见拙作 。 SMON具体将DML统计数据刷新到SYS.MON_MODS$、合并到MON_MODS_ALL$、并清除数据的操作如下:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- www.oracledatabase12g.com /* 填充mon_mods$字典基表 */ lock table sys.mon_mods$ in exclusive mode nowait insert into sys.mon_mods$ (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (:1, :2, :3, :4, :5, :6, :7) update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn lock table sys.mon_mods_all$ in exclusive mode /* 以下merge命令会将mon_mods$中的记录合并到mon_mods_all$, 若有匹配的记录,则在原记录的基础上增加inserts、updates、deletes总数, 否则插入新的记录 */ merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm) dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */ into sys.mon_mods_all$ mm using (select m.obj# obj#, m.inserts inserts, m.updates updates, m.deletes deletes, m.flags flags, m.timestamp timestamp, m.drop_segments drop_segments fr om sys.mon_mods$ m, tab$ t where m.obj# = t.obj#) v on (mm.ob j# = v.obj#) when matched then update set mm.inserts = mm.inserts + v.inserts, mm.updates = mm.updates + v.updates, mm.deletes = mm.deletes + v.deletes, mm.flags = mm.flags + v.flags - bitand(mm.flags, v.flags) /* bitor(mm.flags,v.flags) */, mm.timestamp = v.timestamp, mm.drop_segments = mm.drop_segments + v.drop_segments when NOT matched then insert (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (v.obj#, v.inserts, v.updates, v.deletes, sysdate, v.flags, v.drop_segments) / all merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm) dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */ into sys.mon_mods_all$ mm using (select m.obj# obj#, m.inserts inserts, m.updates updates, m.deletes deletes, m.flags flags, m.timestamp timestamp, m.drop_segments drop_segments fr om sys.mon_mods$ m, tab$ t where m.obj# = t.obj#) v on (mm.ob j# = v.obj#) when matched then update set mm.inserts = mm.inserts + v.inserts, mm.updates = mm.updates + v.updates, mm.deletes = mm.deletes + v.deletes, mm.flags = mm.flags + v.flags - bitand(mm.flags, v.flags) /* bitor(mm.flags,v.flags) */, mm.timestamp = v.timestamp, mm.drop_segments = mm.drop_segments + v.drop_segments when NOT matched then insert (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (v.obj#, v.inserts, v.updates, v.deletes, sysdate, v.flags, v.drop_segments) /* 最后删除sys.mon_mods$上的相关记录 */ delete /*+ dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */ from sys.mon_mods$ m where exists (select /*+ unnest */ * from sys.tab$ t where t.obj# = m. obj#) select obj# from sys.mon_mods$ where obj# not in (select obj# from sys.obj$) Used to have a FULL TABLE SCAN on obj$ associated with monitoring information extracted in conjunction with mon_mods$ executed by SMON periodically.因为当SMON或用户采用"DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO"存储过程将DML数据刷新到mon_mods$或mon_mods_all$中时会要求持有表上的排它锁,所以在RAC环境中可能出现死锁问题。 另外在早期版本中SMON可能因维护监控表而造成shutdown immediate缓慢或系统性能下降的问题,详见: SMON维护MON_MODS$时相关的Stack CALL
kglpnal <- kglpin <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- opiall0 <- opikpr <- opiodr <- PGOSF175_rpidrus <- skgmstack <- rpiswu2 <- kprball <- kprbbnd0 <- kprbbnd <- ksxmfmel <- ksxmfm <- ksxmfchk <- ksxmftim <- ktmmon <- ktmSmonMain <- ksbrdp <- opirip <- opidrv <- sou2o <- opimai_real <- ssthrdmain <- main <- libc_start_main <- start如何禁止SMON维护MON_MODS$ 注意在缺省参数环境中创建的表总是启用table monitoring的:
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> create table maclean1 (t1 int); Table created. /* 在10g以后nomonitoring或monitoring选项不再有效 */ SQL> create table maclean2 (t1 int) nomonitoring; Table created. SQL> select table_name,monitoring from dba_tables where table_name like 'MACLEAN%'; TABLE_NAME MON ------------------------------ --- MACLEAN1 YES MACLEAN2 YES通常来说我们不需要禁止SMON维护MON_MODS$,除非是在SMON维护过程中遭遇shutdown过慢、性能降低或者异常情况恢复SMON随机terminate实例的问题。 在10g以前可以使用MONITORING和NOMONITORING这2个选项来控制表级别的监控是否被开启,此外我们还可以通过dbms_stats.ALTER_SCHEMA_TAB_MONITORING('maclean',false)存储过程在schema级别的monitoring是否被开启,但是在10g以后这些方法不再有效,MONITORING和NOMONITORING选项被废弃(In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored.),其原有功能被STATISTICS_LEVEL参数所覆盖。 Table-monitoring特性现在完全由STATISTICS_LEVEL参数所控制: 当STATISTICS_LEVEL设置为BASIC时,Table-monitoring将被禁用 当STATISTICS_LEVEL设置为TYPICAL或ALL时,Table-monitoring将启用 换而言之我们可以通过设置STATISTICS_LEVEL为BASIC达到禁止SMON后台进程该种功能的作用,具体修改该参数的命令如下:
show parameter statistics_level alter system set statistics_level = basic;但是请注意如果你正在使用AMM或ASMM自动内存管理特性的话,那么STATISTICS_LEVEL参数是不能设置为BASIC的,因为Auto-Memory或Auto-Sga特性都依赖于STATISTICS_LEVEL所控制的性能统计信息。若一定要这样做那么首先要diable AMM&ASMM:
#diable 11g AMM ,have to bounce instance #alter system set memory_target =0 scope=spfile; #diable 10g ASMM alter system set sga_target=0; alter system set statistics_level = basic;
在 Oracle9i 中,您可以通过打开表监视选项 (ALTER TABLE ...MONITORING) 然后检查这些表的视图 DBA_TAB_MODIFICATIONS,检查表中的数据是否已明显更改过。
在 10g 中,已不再使用 MONITORING 语句了。而通过设置初始化参数 STATISTIC_LEVEL 为 TYPICAL 或 ALL,就可以自动收集统计信息。(默认值为 TYPICAL,因此可以随即启用自动收集统计信息的功能。)Oracle 数据库 10g 具有一个预定义的调度程序作业,名称为 GATHER_STATS_JOB,它由 STATISTIC_LEVEL 参数的适当数值所激活。
SQL> show parameter statistics_
NAME TYPE VALUE
------------------------------------ ----------- -----------------
statistics_level string TYPICAL
statistics_level 默认是typical,在10g中表监控是激活的,强烈建议在10g中此参数的值是typical。如果STATISTICS_LEVEL设置为basic,不仅不能监控表,而且将禁掉如下一些10g的新功能:
ASH(Active Session History)
ASMM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)
Oracle 9i 下面建立table默认是 NOMONITORING ,必须通过 alter table xxxx monitoring ; 来设置表的属性 。
CREATE TABLE SFIS1.C_BOM_KEYPART_T
(
BOM_NO VARCHAR2(16 BYTE) NOT NULL,
KEY_PART_NO VARCHAR2(25 BYTE) NOT NULL,
KP_RELATION NUMBER(2),
KP_COUNT NUMBER(2),
VERSION_CODE VARCHAR2(10 BYTE),
GROUP_NAME VARCHAR2(16 BYTE)
)
TABLESPACE BASE_DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
不过到了Oracle10g , 建立table默认是 MONITORING 。
CREATE TABLE DFMS.WIP_D_FPF_DATA
(
WORK_DATE VARCHAR2(8 BYTE) NOT NULL,
PLANT_CODE VARCHAR2(25 BYTE) NOT NULL,
SKU_NO VARCHAR2(25 BYTE) NOT NULL,
ISCFI VARCHAR2(1 BYTE) NOT NULL,
PRODUCTION_TYPE VARCHAR2(20 BYTE) NOT NULL
)
TABLESPACE LOG_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 20M
NEXT 20M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
9i, 10g 下的 statistics_level 参数默认都是typical .
在数据库技术大会上,做了《构建高可用数据库监控系统》的分享以后,很多朋友对北斗如何实现表的DML次数监控有兴趣,会上因为时间的原因,我只是说有系统视图可以查到这个信息,因此有了本文,可以稍微详细一点来说明是如何实现的。
我说的系统视图,具体指的是dba_tab_modifications/all_tab_modifications/user_tab_modifications ,这几个视图收集了表自从上一次分析之后的DML累积次数。但是要注意,考虑到性能的影响,Oracle并不是实时统计这个数据的,在Oracle9i之前,约3个小时SMON进程会刷新一次数据,而Oracle9i以后这个时间间隔变成了15分钟。
因此以较高的频率来实时监控这个表的话,得到的并不是当前的准确数据。Oracle在dbms_stat包中提供了一个过程来手动刷新统计数据,假如在一天的业务低峰期采集一次数据的话,可以先执行该过程,就能得到较为准确的数据。但是,不建议在业务高峰期执行该过程。
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
在Oracle10g之前,必须手工开启表的monitoring属性,才会将DML统计信息收集到这个视图中。可以通过dba_tables.monitoring列查看表是否已经开启了监控。关于这个变化,可以参考Metalink ID 252597.1
alter table test monitoring;
Oracle10g之后,只要statistics_level是TYPICAL(默认)或者ALL,就能自动收集信息了,即使给表设置为nomonitoring也不能阻止,这个表的属性已经被废弃了。
SQL> desc dba_tab_modificationsName Null? Type
----------------------------------------- -------- ----------------------------
TABLE_OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
SUBPARTITION_NAME VARCHAR2(30)
INSERTS NUMBER
UPDATES NUMBER
DELETES NUMBER
TIMESTAMP DATE
TRUNCATED VARCHAR2(3)
DROP_SEGMENTS NUMBER
Relationship between Table Monitoring and STATISTICS_LEVEL Parameter (文档 ID 252597.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 and laterOracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
PURPOSE
Describes the relationship between Table monitoring and STATISTICS_LEVEL parameter
SCOPE
All DBA's.
DETAILS
Prior to Oracle 10g, automated collection of statistics for objects that had become stale was controlled by the setting of the MONITORING flag on table.
Depending on the MONITORING flag, the GATHER_STATS_JOB job collected "GATHER EMPTY" and "GATHER STALE" on the flagged objects.
In 10g the MONITORING and NOMONITORING keywords are deprecated and will be ignored when executing a CREATE or ALTER TABLE DDL statement with these keywords.
For Example:
CREATE TABLE <tablename> MONITORING;
Table-monitoring feature is now controlled by the STATISTICS_LEVEL parameter.
- When STATISTICS_LEVEL is set to BASIC, monitoring is disabled on the table.
- When STATISTICS_LEVEL is set to TYPICAL, then monitoring is enabled.
By default STATISTICS_LEVEL is set to TYPICAL and monitoring of tables is enabled.
It is strongly recommended to set STATISTICS_LEVEL to TYPICAL in 10g.
By setting this parameter to BASIC, you will be disabling most of the manageability features in 10g.
The following will be disabled:
- ASH (Active Session History)
- AWR (Automatic Workload Repository)
- ASMM(Automatic Shared Memory Management)
- ADDM(Automatic Database Diagnostic Monitor)
Monitoring tracks the approximate number of INSERT, UPDATE, and DELETE operations for the table since the last time statistics were gathered. This information on "changes made" is maintained in the SGA and periodically (about every 15 minutes) the SMON flushes the data into the data dictionary tables. You can manually flush the information by calling dbms_stats.FLUSH_DATABASE_MONITORING_INFO(). The data dictionary information is made visible through the views: DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS and USER_TAB_MODIFICATIONS.
Oracle uses these views to identify tables that have stale statistics.
Whenever there is 10% change in data in a table, Oracle considers its statistics to be stale.
Up to date statistics are important to generate good execution plans. Automatic statistics collection job using DBMS_STATS packages depend on the monitoring data to determine when to collect statistics on objects with stale statistics.
Stats are considered stale when #(INSERTS + UPDATES + DELETES) >= 10% of NUM_ROWS from dba_tables:
t.last_analyzed,
u.table_name,
u.inserts,
u.updates,
u.deletes,
d.num_rows,
decode(t.num_rows,0,'Table Stats indicate No Rows',
nvl(TO_CHAR(((U.inserts+u.deletes+u.updates)/d.num_rows) * 100,'999.99')
,'Null Value in USER_TAB_MODIFICATIONS')
) percent
from user_tables t,USER_TAB_MODIFICATIONS u,dba_tables d
where u.table_name = t.table_name
and d.table_name = t.table_name
and d.owner = '&Owner'
and (u.inserts > 10000 or u.updates > 10000 or u.deletes > 10000)
order by t.last_analyzed
/
The 10000 is there so as to pick up only the biggest changes.
Example:
Step 1:
Create table EMP. Its description is as follows
SQL> desc emp Name Null? Type ----------------------------------------- ------------------------------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select count(*) from emp; COUNT(*) ---------- 14 SQL> select * from user_tab_modifications; no rows selected
Initially there are 14 rows in EMP.
Step 2:
Set parameter STATISTICS_LEVEL='TYPICAL'
System altered.
Step 3:
Insert additional 14 rows. This will increase the data in EMP by 50% and therefore the statistics in EMP will be regarded as stale by Oracle.
14 rows created.
SQL>commit;
Step 4:
The information about the inserts, updates and deletes on tables will be in SGA. After every 15 minutes SMON will push this data in data-dictionary tables. You can view them by querying table dba_tab_modifications.
In this case, we have manually flushed the information using dbms_stats.FLUSH_DATABASE_MONITORING_INFO() to push the information about the modifications in the tables from SGA to data-dictionary tables:
SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); PL/SQL procedure successfully completed. SQL> select * from user_tab_modifications; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU ------------------------------ ---------- ---------- ---------- --------- --- EMP 14 0 0 16-OCT-03 NO
Step 5:
If a monitored table has been modified more than 10%, then these statistics are considered stale
Prior to Oracle11g, the staleness threshold is hardcoded at 10%. This means that an object is considered stale if the number of rows inserted, updated or deleted since the last statistics gathering time is more than 10% of the number of rows. There is no way to modify this value prior to Oracle 11g.
Starting with Oracle11g, the staleness threshold can be set using the STALE_PERCENT statistics preference. This can be set globally using DBMS_STATS.SET_GLOBAL_PREFS or at the table level using DBMS_STATS.SET_TABLE_PREFS.
E.g. to modify and check the staleness threshold for table EMP in SCOTT schema:
PL/SQL procedure successfully completed.
Gather statistics on the tables whose statistics have become stale using the following command:
NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT',
TRUE, NULL, NULL, 'GATHER STALE','LIST' );
Step 6:
Query dba_tab_modifications to check whether statistics have been gathered on the table.
no rows selected
No rows in dba_tab_modifications indicates that statistics have been gathered on the table.
About Me
.............................................................................................................................................
● 本文整理自网络,若有侵权请联系小麦苗删除
● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新
● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址:http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号:230161599(满)、618766405
● 微信群:可加我微信,我拉大家进群,非诚勿扰
● 联系我请加QQ好友(646634621),注明添加缘由
● 于 2017-09-01 09:00 ~ 2017-09-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解
● 版权所有,欢迎分享本文,转载请保留出处
.............................................................................................................................................
● 小麦苗的微店:https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书:http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店
.............................................................................................................................................