关于DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC的一些发现
任务在哪
这个是11g以后的自动收集统计信息的后台任务，10g之前是在dba_scheduler_jobs里查看
SQL> SELECT CLIENT_NAME ,STATUS ,MEAN_INCOMING_TASKS_7_DAYS,MEAN_INCOMING_TASKS_30_DAYS
FROM DBA_AUTOTASK_CLIENT
WHERE CLIENT_NAME = 'auto optimizer stats collection'
/ 2 3 4 5 6 7 CLIENT_NAME STATUS
---------------------------------------------------------------- --------
MEAN_INCOMING_TASKS_7_DAYS MEAN_INCOMING_TASKS_30_DAYS
-------------------------- ---------------------------
auto optimizer stats collection ENABLED2 2.25806452
可以在dba_autotask_job_history中看到历史执行情况
什么原理
根据1592404.1，后台的GATHER_DATABASE_STATS_JOB_PROC就是使用gather auto的option的情况。
根据1233203.1
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.
基本是先收集empty的，再收集stale的，剩下的按用户，对象名，分区名等。
观测手段
可以用以下过程查看下一次gather auto的列表
SQL>
set line 150
set serveroutput on
declareobj_auto dbms_stats.ObjectTab;
begindbms_stats.gather_database_stats(options => 'LIST AUTO',objlist => obj_auto);for i in 1..obj_auto.countloopdbms_output.put_line('Auto list---> Owner: '||obj_auto(i).ownname||' Object name: '||obj_auto(i).objName||'- -Object type: '||obj_auto(i).objType);end loop;
end;
/
