[zt] Oracle 10g 统计信息自动收集功能（automatic statistics gathering）

1、什么是STATISTICS：优化统计信息是为了更详细的描述数据库及数据库对象而收集的数据，这些信息被用于为sql语句选择最优的执行计划。优化统计信息包括的项有：ITPUB个人空间*S;H;VJ$w k;`"bl9]w
Table statistics（表统计信息）：Number of rows、Number of blocks、Average row length；
3C3ZY(K;C0Column statistics（列统计信息）：Number of distinct values (NDV) in column、Number of nulls in column、Data distribution (histogram)；ITPUB个人空间*ksj-A!Gj8uA8V'P0GJ
Index statistics（索引统计信息）：Number of leaf blocks、Levels、Clustering factor；
fe4uG-OIli0System statistics（系统统计信息）：I/O performance and utilization、CPU performance and utilization。ITPUB个人空间*sm:RykUi
其中表、列和索引统计信息都可以通过统计信息自动收集功能来收集，系统统计信息在oracle 10g中只能通过手动收集来完成。
&BRjA_;D4@Z(UV1|$?02、如何得到STATISTICS：Orcale10g中，STATISTICS由GATHER_STATS_JOB作业收集得到，只有当数据库对象没有统计信息或者统计信息已经过期（Oracle10G中是否过期的标准是数据库对象被修改的记录行数超过10%，该信息由ModificationMonitoring来追踪完成）时才对该对象进行信息统计，该作业在数据库创建或升级时由Scheduler自动创建，这些作业可以从视图DBA_SCHEDULER_JOBS中查到。ITPUB个人空间7sj&/`h6VY.B
如：SELECT d.owner,d.job_name,d.program_name,d.schedule_type,d.comments FROM DBA_SCHEDULER_JOBS dITPUB个人空间&FV.x:pTDGS&r
结果：ITPUB个人空间/f(wn-C%L%`
默认情况下，Scheduler在维护窗口（maintenancewindow，默认启动时间为周内晚上10：00至次日早上6：00及整个周六周日）打开时运行GATHER_STATS_JOB作业，作业GATHER_STATS_JOB则是通过调用系统内部过程DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC来完成信息统计的，该过程可根据数据库对象统计信息需求的优先级（即数据库对象被修改的多少）按先后顺序来完成统计信息收集任务。GATHER_STATS_JOB作业是否随维护窗口的关闭而关闭则由属性stop_on_window_close决定，stop_on_window_close的默认值为TRUE，此时GATHER_STATS_JOB作业随维护窗口的关闭而关闭。统计信息的收集是资源相当密集的工作，因此您可能希望确保它不影响数据库的正常操作。ITPUB个人空间%t#uT9qg6W;fQ^:fT
统计信息收集还可由过程DBMS_STATS.GATHER_DATABASE_STATS应用GATHERAUTO选项，来完成，与系统地内部过程DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC相比，DBMS_STATS.GATHER_DATABASE_STATS不区分数据库对象统计信息需求的优先级。
__T;K*vz7ra0非默认情况时，Oracle10g可通过设置初始化参数 STATISTIC_LEVEL，来控制是否启用统计信息自动收集功能。STATISTIC_LEVEL参数的信息如（表-1）：ITPUB个人空间JCoXB}R']1c

参数类型	String
语法	STATISTICS_LEVEL = {ALL | TYPICAL | BASIC}
默认值	TYPICAL
参数类别	动态ALTER SESSION,ALTER SYSTEM

[co{ xZi/|0（表-1）ITPUB个人空间2P.|�l8`}oX
该参数用于控制数据库统计信息收集的级别。当其为默认值TYPICAL时，系统将自动收集所有主要的有关自身管理的信息以使系统提供最优性能，该值适合于绝大多数情况；当取值ALL时，相对TYPICAL值系统增加timed OS statistics和planexecution statistics两项信息统计；当取值BASIC时：有关系统特性和功能的许多信息统计功能都将被关闭（详细见附录1）。因此oracle强烈建意参数STATISTICS_LEVEL的值尽量不要设成BASIC。同时当修改参数以语法“alter system set statistics_level='typical';”完成时，修改后的STATISTICS_LEVEL的作用范围为整个系统，但当使用“ALTER SESSION”时，STATISTICS_LEVEL的作用范围仅为本SESSION。STATISTIC_LEVEL参数所控制的所有统计、报告功能的状态信息都可以从视图V$STATISTICS_LEVEL中得到，具体有关视图V$STATISTICS_LEVEL各字段的含义可参见网页http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2135.htm#I1030264。
`s+u9B)^/L4{0举例如下：
X}#MZ+YI0BEGINITPUB个人空间9~t8xb gC
EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=all';ITPUB个人空间+U(s$C3|"F] U
END;--设置本session statistics_level为‘all’ITPUB个人空间9v b}j$mc
SELECT v.STATISTICS_NAME,v.SESSION_STATUS,v.SYSTEM_STATUS,v.ACTIVATION_LEVEL,v.SESSION_SETTABLE FROM V$STATISTICS_LEVEL v;ITPUB个人空间&xi:R!i+]Dgk2@r
结果如下：
u%S Qn5yF ]e0BEGIN
}7p2f9x7Vi0EXECUTE IMMEDIATE 'ALTER SESSION SET statistics_level=basic';
!SQ-x#Wx#`IjAz0END;--设置本session statistics_level为‘basic’
?V.y`Q#oQ8s"`F E0SELECT v.STATISTICS_NAME,v.SESSION_STATUS,v.SYSTEM_STATUS,v.ACTIVATION_LEVEL,v.SESSION_SETTABLE FROM V$STATISTICS_LEVEL v;
o5nj;j/v0结果如下：ITPUB个人空间5[*riU lT"NZ7COB8b2v
3、保存以前版本的统计信息在优化器收集统计信息时可能出现原来的优化方法在收集统计信息之前一直工作良好，但是在此之后，由于新收集的统计信息产生了不良计划，导致查询突然出错或效率降低。为避免这种情况，统计信息的收集作业在收集新信息之前保存当前的统计信息。如果出现问题，则可以返回到原有的统计信息，或者通过历史统计检查二者之间的不同之处，以解决问题。ITPUB个人空间2/ ]%~%k�|h-}%T
例如，假设在 5 月 31 日晚上 10:00 开始运行表 emp 上的统计信息收集作业，而随后查询的性能变差。Oracle 保存了原有的统计信息，可以通过执行以下命令重新获取这些信息：
[e-mail]:Fy0beginITPUB个人空间jhk]!LR-]QQ
dbms_stats.restore_table_stats (ITPUB个人空间(vhIg9{[3t
'ARUP',ITPUB个人空间6F1|tn4O a3GO9b
'REVENUE',
0/�F-] ?k/-sJcR0'10-DEC-08 10.00.00.000000000 PM -04:00');
9D&}|CIU4U~0end;
J&v's4FU1nG G0此命令将统计信息恢复到 12月 10 日晚上 10:00 为止的统计信息,时间信息是以 TIMESTAMP数据类型提供。历史统计信息能够恢复的时间长度是由保留参数所决定的。要查看当前的保留参数，可使用以下查询：ITPUB个人空间(k(D^AS_^D
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
3MZ8y!xg0GET_STATS_HISTORY_RETENTIONITPUB个人空间)g M/]m"O)ml
---------------------------
/}3n*nIf:E031
|:WK E!Yjxd0表示可以保存相当于 31 天的统计信息，但并不能予以保证。要了解统计信息所覆盖到的确切时间和日期，只需使用以下查询：ITPUB个人空间U uFq9{;?2wI
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;ITPUB个人空间Pj-AA%v:SgO
GET_STATS_HISTORY_AVAILABILITYITPUB个人空间I7n7`7T,o#ustj
---------------------------------------------------------------------ITPUB个人空间4z1s4QuFNVw%S
10-DEC-08 09.21.33.594053000 PM -04:00
6VN2Z%D Gv3e^0该查询表明可用的最陈旧统计信息日期为 12 月 10 日上午9:21。 同时，也可以通过执行内建的函数将保留时间设为不同的值。例如，要将其设为 45 天，可使用：
4Q(I9VD%pn0execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)
v`+Nnkd#I"]04、查看统计信息表、索引一击列的统计信息都存储在数据字典里，可以通过选取数据字典视图的某些字段来查看相应统计信息，此处具体涉及到的数据字典视图见附件2。具体各视图各字段的含义可见网页：http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm。ITPUB个人空间@;{:B2J�Uo9hZ
此处就以列统计信息为列，说明查看列统计信息。
1Zpl.c,DS:W0列统计信息可以按柱状统计图的形式存储，柱状统计图为列数据提供了准确的描述信息，尤其是在数据列有倾斜（列为某种值的记录行数非常多，而某种值的记录行数又非常少）的时候。Oracle中包含两种柱状统计图，高度正方图（height-balanced）和频率直方图（frequencyhistograms），该类型存储在视图*TAB_COL_STATISTICS (* 可为USER或 DBA)，其取值为HEIGHTBALANCED, FREQUENCY或 NONE
bFrml$Hh0（1）高度直方图高度直方图中，数据列的值被分成组，每组包含的数据数据可数基本相等。比如有一个列c其值在1到100之间，当均匀分布，其列的高度直方图如下图：
G3QO(p;f K0每个间隔中都包含数据列的10行数据；当不均匀分布时其列直方图如下图ITPUB个人空间'TZH/o0xW&]
这时，大多数数据行的值为5。查询时最有价值的统计信息就是各个组范围的两个端点的值.
]k~9KW%}t,@0查看统计信息的语法如下：
G7Fs&B`;t0BEGINITPUB个人空间AMkK[B/
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'scott', TABNAME => 'EMP',ITPUB个人空间z;Q6JY7_N}(mL
METHOD_OPT => 'FOR COLUMNS SIZE 6 SAL');ITPUB个人空间XY5|0U~I)N
END;ITPUB个人空间N-l+pi+a@.O
/ITPUB个人空间-NC$H*qu+[e!YA
SELECT column_name, num_distinct, num_buckets, histogramITPUB个人空间m/B,OzA[^'W
FROM USER_TAB_COL_STATISTICS
&H