ITPub博客

首页 > 数据库 > Oracle > obj$等数据字典表的统计信息采集机制一些整理

obj$等数据字典表的统计信息采集机制一些整理

原创 Oracle 作者:darren__chan 时间:2020-09-03 14:34:20 0 删除 编辑
从11g开始,自动统计信息收集任务的时间窗口:
11g+ Automatic Maintenance Tasks
  • What is the name of the default stats gathering job on 11g?
The automatic statistics gathering job on 11g is called "auto optimizer stats collection".
  • What are the default windows for the automated maintenance task to run?
In 11g daily maintenance windows are provided. by default these are defined as :
  • Weeknights: Starts at 10 p.m. and ends at 2 a.m.
  • Weekends: Starts at 6 a.m. is 20 hours long.
自动统计信息收集 调用的任务是gather_stats_prog:
SQL> SELECT client_name,task_name, status        FROM dba_autotask_task        WHERE client_name = 'auto optimizer stats collection';  2    3   CLIENT_NAME                      TASK_NAME           STATUS -------------------------------- --------------------  -------

auto optimizer stats collection   gather_stats_prog  ENABLED


这个任务调用的program是 DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC :


SELECT program_action, number_of_arguments, enabled FROM dba_scheduler_programs WHERE owner = 'SYS'  AND program_name = 'GATHER_STATS_PROG'; PROGRAM_ACTION                             NUMBER_OF_ARGUMENTS ENABL -----------------------------------------  ------------------- -----

dbms_stats.gather_database_stats_job_proc            0         TRUE


参考《 FAQ: Automatic Statistics Collection (Doc ID 1233203.1)
自动统计信息收集默认是收集所有schema的,同时12c会收集所有fixed 对象,11g是部分。
Does the auto stats gathering job gather statistics on all schemas in the instance?
By default ' auto optimizer stats collection' is controlled by the Global preference AUTOSTATS_TARGET which defaults to AUTO collecting all schemas including SYS . On 12c this includes fixed object statistics, however, on 11g it does not.
What is the AUTOSTATS_TARGET of SET_GLOBAL_PREFS?
This additional parameter controls which objects the automatic statistic gathering job (that runs in the nightly maintenance window) will monitor. The possible values for this parameter are:
  • ALL
This setting means that the automatic statistics gathering job will gather statistics on all objects in the database.
From 12c this includes  statistics on fixed objects. 
On 11g and below this was  APART FROM statistics on fixed objects.
  • ORACLE
ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, system, etc)
  • AUTO (default)
means that Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.
  • How does auto optimizer stats collection prioritize which tables are analyzed first?
Accurate statistics are important on all objects. The GATHER_DATABASE_STATS_JOB_PROC procedure called by the 'auto optimizer stats collection' job prioritizes database objects that have no statistics. This means that objects that most need statistics are processed first. Once these are done then objects with stale statistics are addressed. For these, there is no particular prioritization. The statistics may be ordered in some way but it is cursory, ordering by owner,object_name,part_name just to be consistent. 
参考《 FAQ: Statistics Gathering Frequently Asked Questions (Doc ID 1501712.1)
包括查了很多资料,基本上决定是否收集统计信息的标准都是10%的过期,我尝试通过10046追踪,无奈抓出来的过程比较复杂,很耗时间。
Can you tell whether statistics are "too old"?
As above, statistics need to be gathered as often as the data requires. By default we define 'STALE' data as around 10% of that data . If the data changes more than 10% then we recommend that you re-gather . With your data and application you may find that a smaller percentage is enough to make a difference or that you can get away with a lot more dependent on the data distribution.
统一收集统计信息的STALE_PERCENT,默认为10%:
SQL> select dbms_stats.get_prefs('STALE_PERCENT') from dual; DBMS_STATS.GET_PREFS -------------------- 10
DBA_TAB_STATISTICS 中STALE_STATS的判断:
case      when t.analyzetime is null then null      when ((m.inserts + m.deletes + m.updates) >     t.rowcnt * to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',    DBMS_STATS_INTERNAL.DQ(u.name),    DBMS_STATS_INTERNAL.DQ(o.name))      )/100 or   bitand(m.flags,1) = 1) then 'YES'      else  'NO'    end    sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m
1.analyzetime 为空  等于 null
2.m.inserts + m.deletes + m.updates 大于 rowcnt*10% 等于YES

3.bitand(m.flags,1) = 1) 等于YES  <<<===与truncate有关



测试部分:
1.当前obj$的stale_status情况,当前是过期的:
select  u.name,o.name,m.inserts , m.deletes , m.updates ,m.inserts + m.deletes + m.updates modify_cnt,t.rowcnt,t.rowcnt *  to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',              u.name,              o.name)          )/100 "10%cnt",bitand(m.flags,1) , case      when t.analyzetime is null then null      when ((m.inserts + m.deletes + m.updates) >       t.rowcnt *  to_number(DBMS_STATS.GET_PREFS('STALE_PERCENT',              u.name,              o.name)          )/100 or     bitand(m.flags,1) = 1) then 'YES'      else  'NO'    end "stale_status" from  sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m where   o.owner# = u.user#    and o.obj# = t.obj#    and bitand(t.property, 1) = 0 /* not a typed table */    and o.obj# = ts.obj# (+)    and t.obj# = m.obj# (+)    and o.name='OBJ$';

2.调用统计信息收集dbms_stats.gather_database_stats_job_proc。


SQL> exec dbms_stats.gather_database_stats_job_proc; PL/SQL procedure successfully completed.


3.再次查询obj$的stale_status情况,过期状态变为no,modify都会清空:

4.像obj$这种表,在日常做任何操作只要涉及object的一般都有可能对obj$进行dml操作,像update的频率会更高,因此不能以单方面看对象增多来看。
SQL> create table tt (id int);
Table created.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
SQL> drop table tt;
Table dropped.
SQL>  exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL>exec dbms_stats.gather_database_stats_job_proc;

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

请登录后发表评论 登录
全部评论
一思尚存,此志不懈

注册时间:2014-09-09

  • 博文量
    175
  • 访问量
    1133899