Does GATHER_STATS_JOB gather all objects' stats every time?-白红宇

周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致SQL执行计划偏差的问题，这是一个10g的库并且禁用了自动收集统计信息的定时作业GATHER_STATS_JOB；当问及应用程序开发商为何要禁用自动统计信息收集时，开发商的一位工程师说因为该库的数据量较大，考虑到该JOB每天都会将所有大表统计一遍可能要花费大量时间所以予以停用。这里就存在一个问题，GATHER_STATS_JOB自动统计作业是每次打开都会将数据库中所有的对象的统计信息都收集一遍吗？细心的朋友一定会发觉实际上该JOB的运行时间是时长时短的，同时绝对不是如这位开发工程师所说的会每天都重复统计所有表。 10g的官方文档中对该GATHER_STATS_JOB描述为"The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.The

stop_on_window_close

attribute controls whether the

GATHER_STATS_JOB

continues when the maintenance window closes. The default setting for the

stop_on_window_close

attribute is

TRUE

, causing Scheduler to terminate

GATHER_STATS_JOB

when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The

GATHER_DATABASE_STATS_JOB_PROC

procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows)." 以上这段描述还是比较清晰的，MAINTENANCE_WINDOW_GROUP维护窗口组中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)会在每个工作日的22:00启动并于第二天的6:00结束，在周末该维护窗口组中的周末窗口(WEEKEND_WINDOW)从周六Sat的0点开始并持续48小时（你不难发现这2个窗口在周六0点到6点之间存在overlay，实际的情况是WEEKEND_WINDOW窗口是从周六的0点整到周一的0点，具体可以观察dba_scheduler_windows视图的NEXT_START_DATE列，这里不再

赘述)。在数据库一直打开的情况下，

GATHER_STATS_JOB会伴随维护窗口一起被启动，默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性

stop_on_window_close)，剩下的有待收集信息的对象将在下一个维护窗口中得到处理；如果数据库一直处于关闭的状态，并在某维护窗口的时间范围内该DB被打开，那么相应的维护窗口会被立即激活(ACTIVE)，同时

GATHER_STATS_JOB自动作业也会被启动，但该自动作业仅会在一个窗口中自动运行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失败的不算做一次)。

以上介绍了

GATHER_STATS_JOB的运行周期，和我们要介绍的问题没有直接的联系。我们这里要谈的是，

GATHER_STATS_JOB自动统计信息收集作业每次启动时是由针对性地收集统计信息的而非对数据库中所有schema下的对象都分析一遍；以上引用的文字中介绍了该JOB挑选分析对象的条件，即:

对象之前从未收集过统计信息，或由于某些原因没有统计信息

对象的统计信息相对陈旧(stale)，是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数超过10%

条件1显得理所当然，剔除一些复杂的情况，一个对象没有统计信息的原因往往是这个对象刚刚被创建或者加载到数据库中，并且用户没有手动地去收集过统计信息，那么Oracle有充分的理由去分析这些对象。而后者则体现了查询优化器对统计信息陈旧度的容忍在超过10%的情况下导致执行计划偏差的可能性将大幅上升，为了遏制这种势头有必要再次统计这些对象。让我们来看看

GATHER_STATS_JOB

针对"陈旧"(stale)统计信息的实际表现:

                         
SQL> 
select
* 
from
global_name;
GLOBAL_NAME
------------------------------------
www.oracledatabase12g.com
SQL> 
select
* 
from
v$version;
BANNER
----------------------------------------------------------------
Oracle 
Database
10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0  Production
TNS 
for
Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> conn maclean/maclean
Connected.
SQL> 
create
table
need_analyze tablespace users 
as
select
rownum t1 
from
dba_objects 
where
rownum<10001;
Table
created.
SQL> 
select
count
(*)  
from
need_analyze;
  
COUNT
(*)
----------
     
10000
SQL> 
select
num_rows,blocks 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
  
NUM_ROWS     BLOCKS
---------- ----------
/* 以上创建了一张具有10000行记录的测试用表,因为是新建的所以没有num_rows和blocks等等统计信息 */
/* 手动调用GATHER_STATS_JOB自动作业 */
SQL> 
begin
dbms_scheduler.run_job(job_name=>
'SYS.GATHER_STATS_JOB'
,use_current_session=>
true
);
end
;
/
SQL> 
select
num_rows,blocks 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
  
NUM_ROWS     BLOCKS
---------- ----------
     
10000     20
/* 删除999条记录，即不到10%的数据 */
SQL> 
delete
need_analyze 
where
rownum<1000;
999 
rows
deleted.
SQL> 
commit
;
Commit
complete.
/* 再次调用GATHER_STATS_JOB */
begin
dbms_scheduler.run_job(job_name=>
'SYS.GATHER_STATS_JOB'
,use_current_session=>
true
);
end
;
/
/* 可以看到统计信息并未被更新 */
SQL> 
select
num_rows,blocks 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
  
NUM_ROWS     BLOCKS
---------- ----------
     
10000     20
SQL> 
delete
need_analyze 
where
rownum<2;
1 row deleted.
SQL> 
commit
;
Commit
complete.
SQL> 
begin
dbms_scheduler.run_job(job_name=>
'SYS.GATHER_STATS_JOB'
,use_current_session=>
true
);
end
;
/  2    3    4
PL/SQL 
procedure
successfully completed.
SQL> 
select
num_rows,blocks 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
  
NUM_ROWS     BLOCKS
---------- ----------
     
10000     20
SQL>  
delete
need_analyze 
where
rownum<2;
1 row deleted.
SQL> 
commit
;
Commit
complete.
SQL> 
begin
dbms_scheduler.run_job(job_name=>
'SYS.GATHER_STATS_JOB'
,use_current_session=>
true
);
end
;
/  2    3    4
PL/SQL 
procedure
successfully completed.
SQL> 
select
num_rows,blocks 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
  
NUM_ROWS     BLOCKS
---------- ----------
      
8999     20
/* 可以看到修改的行数必须超过10%后才会被收集 */
        
      

有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作，而DML操作又涉及到了多少行数据呢？这都是通过表监控特性(a table monitoring facility)来实现的，当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断，并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:

                         
SQL> 
delete
need_analyze;
8999 
rows
deleted.
SQL> 
commit
;
Commit
complete.
SQL> 
select
* 
from
user_tab_modifications 
where
table_name=
'NEED_ANALYZE'
;
no
rows
selected
/* 从实际的DML操作完成到*_tab_modifications视图到更新可能存在几分钟的延迟 */
/* 通过dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存储过程可以
   
将这些监控数据刷新到字典中  */
SQL> 
exec
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL 
procedure
successfully completed
SQL> col table_name 
for
a20
SQL> 
select
table_name,inserts,updates,deletes,
timestamp
from
user_tab_modifications 
where
table_name=
'NEED_ANALYZE'
;
TABLE_NAME      INSERTS    UPDATES    DELETES 
TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
NEED_ANALYZE              0      0   8999 26-MAR-11
/* 可以看到*_tab_modifications视图中记录了上次收集统计信息以来
   
NEED_ANALYZE表上删除过8999条记录，因为测试时仅用少量的串行DML,所以这里十分精确 */
SQL> 
set
autotrace 
on
;
        
      

                         
/* 通过以上执行计划可以猜测，monitoring监控数据来源于MON_MODS_ALL$基表上 */
SQL> 
desc
sys.MON_MODS_ALL$;
 
Name
Null
?    Type
 
----------------------------------------- -------- ----------------------------
 
OBJ#                           NUMBER
 
INSERTS                        NUMBER
 
UPDATES                        NUMBER
 
DELETES                        NUMBER
 
TIMESTAMP
DATE
 
FLAGS                          NUMBER
 
DROP_SEGMENTS                      NUMBER
SQL> 
select
* 
from
mon_mods_all$ 
where
obj#=(
select
object_id 
from
dba_objects 
where
object_name=
'NEED_ANALYZE'
);
      
OBJ#    INSERTS    UPDATES    DELETES 
TIMESTAMP
FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ---------- -------------
     
52565      0          0       8999 26-MAR-11          0         0
/* 需要注意的该mon_mods_all$修改监控基表仅记录上次该对象统计信息以来的修改(
modify
)情况，
   
并不能做为某张表的实际修改历史记录来利用 */
        
      

虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了，但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗？这似乎有悖于我们的常识，试看下例子:

                         
/ * NEED_ANALYZE现在扮演一张静态表，它上次被分析是在2011年3月26日 */
SQL> 
select
last_analyzed 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
LAST_ANAL
---------
26-MAR-11
SQL> 
select
sysdate 
from
dual;
SYSDATE
---------
26-MAR-11
SQL> shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL> host
[maclean@rh8 ~]$ su - root
Password
:
/* 我们把时钟调快到2012年的12月30日，希望我们能安全度过2012! */
[root@rh8 ~]# 
date
-s 
"2012-12-30 00:00:00"
Sun 
Dec
30 00:00:00 CST 2012
[root@rh8 ~]# 
date
Sun 
Dec
30 00:00:01 CST 2012
[maclean@rh8 ~]$ exit
exit
SQL> startup;
ORACLE instance started.
Total System 
Global
Area  167772160 bytes
Fixed 
Size
1218292 bytes
Variable 
Size
75499788 bytes
Database
Buffers       83886080 bytes
Redo Buffers            7168000 bytes
Database
mounted.
Database
opened.
SQL> 
select
sysdate 
from
dual;
SYSDATE
---------
30-
DEC
-12
/* 再次手动调用GATHER_STATS_JOB自动作业 */
SQL> 
set
timing 
on
;
SQL> 
begin
dbms_scheduler.run_job(job_name=>
'SYS.GATHER_STATS_JOB'
,use_current_session=>
true
);
end
;
/
  
2    3    4
PL/SQL 
procedure
successfully completed.
Elapsed: 00:00:00.33
/* :-)，运行结束不到1s */
SQL>  
select
last_analyzed 
from
dba_tables 
where
table_name=
'NEED_ANALYZE'
;
LAST_ANAL
---------
26-MAR-11
        
      

是的，默认情况下GATHER_STATS_JOB不会反复去分析那些静态表，无论过去"多久"。好了，我们需要对GATHER_STATS_JOB和DBMS_STATS包下属的统计信息收集存储过程(gather_*_stats)有一个饱满的认识，他们远没有我们想象的那么2，实际上这个GATHER_STATS_JOB调用的PROGRAM存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC，虽然这是一个内部存储过程(interal procedure)；但实际上它和我们经常手动调用的DBMS_STATS.GATHER_DATABASE_STATS收集整库统计信息的存储过程在以GATHER AUTO选项运行时的行为方式上几乎一致，主要的区别是GATHER_DATABASE_STATS_JOB_PROC总是优先收集那些急需收集统计信息的对象，这保证了在维护窗口关闭之前那些最需要收集的统计信息总是能得到满足。而在手动调用GATHER_DATABASE_STATS等存储过程时因为不需要考虑窗口时间线的限制，而不论优先级。 to be continued .............

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277727