周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致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
有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作，而DML操作又涉及到了多少行数据呢？这都是通过表监控特性(a table monitoring facility)来实现的，当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断，并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:
虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了，但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗？这似乎有悖于我们的常识，试看下例子:
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%
GATHER_STATS_JOB
针对"陈旧"(stale)统计信息的实际表现:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
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%后才会被收集 */
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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
;
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
/* 通过以上执行计划可以猜测，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
)情况，
并不能做为某张表的实际修改历史记录来利用 */
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
|
/ * 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
