使用dbms_stats包收集统计数据

execdbms_stats.gather_schema_stats( -51Testing软件测试网{
FW-_1|R.g6h St
ownname => 'SCOTT', -51Testing软件测试网;{._eJW_7_i*iW
options => 'GATHER AUTO', -51Testing软件测试网0B]8]Nj'z;~
estimate_percent => dbms_stats.auto_sample_size, -
,aZh ]H3w lx0method_opt => 'for all columns size repeat', -
;e;a x@jIj.J0degree => 15 -
v4Pf7MVe M Q,\0)
wCJS;K0
n8Q"G2~+f,z H A.r0  为了充分认识dbms_stats的好处，你需要仔细体会每一条主要的预编译指令（directive）。下面让我们研究每一条指令，并体会如何用它为基于代价的SQL优化器收集最高质量的统计数据。

options参数

使用4个预设的方法之一，这个选项能控制Oracle统计的刷新方式：

• gather——重新分析整个架构（Schema）。
• gather empty——只分析目前还没有统计的表。
• gather stale——只重新分析修改量超过10%的表（这些修改包括插入、更新和删除）。
• gather auto——重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。注意，使用gather auto类似于组合使用gather stale和gather empty。

L/@)i2Ho'A\)?)c0注意，无论gather stale还是gather auto，都要求进行监视。如果你执行一个alter table xxx monitoring命令，Oracle会用dba_tab_modifications视图来跟踪发生变动的表。这样一来，你就确切地知道，自从上一次分析统计数据以来，发生了多少次插入、更新和删除操作。

estimate_percent选项

以下estimate_percent参数是一种比较新的设计，它允许Oracle的dbms_stats在收集统计数据时，自动估计要采样的一个segment的最佳百分比：
D6d-i%hx;O0M-P0estimate_percent => dbms_stats.auto_sample_size51Testing软件测试网&t8kmo.I n'B~;A5p@

(]lr0zQYo"G0要验证自动统计采样的准确性，你可检视dba_tables sample_size列。一个有趣的地方是，在使用自动采样时，Oracle会为一个样本尺寸选择5到20的百分比。记住，统计数据质量越好，CBO做出的决定越好。

method_opt选项

G7QC)BM0dbms_stats的method_opt参数尤其适合在表和索引数据发生变化时刷新统计数据。method_opt参数也适合用于判断哪些列需要直方图（histograms）。51Testing软件测试网"L'NF"_/D/u

某些情况下，索引内的各个值的分布会影响CBO是使用一个索引还是执行一次全表扫描的决策。例如，假如在where子句中指定的值的数量不对称，全表扫描就显得比索引访问更经济。

如果你有一个高度倾斜的索引（某些值的行数不对称），就可创建Oracle直方图统计。但在现实世界中，出现这种情况的机率相当小。使用CBO时，最常见的错误之一就是在CBO统计中不必要地引入直方图。根据经验，只有在列值要求必须修改执行计划时，才应使用直方图。51Testing软件测试网9^YN_)H!X R`4pY
51Testing软件测试网2l)F)B%m^)ra
为了智能地生成直方图，Oracle为dbms_stats准备了method_opt参数。在method_opt子句中，还有一些重要的新选项，包括skewonly，repeat和auto：method_opt=>'for all columns size skewonly'
e['nE
L9v$W+v0method_opt=>'for all columns size repeat'
L3B\ws0method_opt=>'for all columns size auto'
Z cy`u+a)M
i#z051Testing软件测试网5iY8h[Ky0p0P]0F
skewonly选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。51Testing软件测试网6c U#LHnI$r[0c

假如dbms_stat发现一个索引的各个列分布得不均匀，就会为那个索引创建直方图，帮助基于代价的SQL优化器决定是进行索引访问，还是进行全表扫描访问。例如，在一个索引中，假定有一个列在50%的行中，如清单B所示，那么为了检索这些行，全表扫描的速度会快于索引扫描。--*************************************************************
C5xl)MWyb0-- SKEWONLY option—Detailed analysis
Uo'DTSd0--51Testing软件测试网r6K#t0K s@C;Z$T
-- Use this method for a first-time analysis for skewed indexes
GGm3v;wZ0-- This runs a long time because all indexes are examined51Testing软件测试网N*~MgT0Q }7Q
--*************************************************************
EkwR,Kj&[[0begin51Testing软件测试网5q~6N+P3~ga/R
dbms_stats.gather_schema_stats(
RS/JW&t2f0ownname => 'SCOTT',51Testing软件测试网o1__1h:^*g7wonN
estimate_percent => dbms_stats.auto_sample_size,51Testing软件测试网hR+r}e9]+y
method_opt => 'for all columns size skewonly',51Testing软件测试网 t? H&O5F?
degree => 7
wU$N#w WPPN0);51Testing软件测试网t"|dr~pJev
end;51Testing软件测试网e^:M2xnrc7m's0Q2q"Y

vRdDSg0重新分析统计数据时，使用repeat选项，重新分析任务所消耗的资源就会少一些。使用repeat选项（清单C）时，只会为现有的直方图重新分析索引，不再搜索其他直方图机会。定期重新分析统计数据时，你应该采取这种方式。--**************************************************************
J.R;~ u\!}z!DZ0-- REPEAT OPTION - Only reanalyze histograms for indexes51Testing软件测试网c!w!O&UvD0p { Q3y
-- that have histograms
{.mc:u^%u*m.]k#?0--
9_"Q:q[!H1y0-- Following the initial analysis, the weekly analysis
8z0`2Jy'S qlZX0-- job will use the “repeat” option. The repeat option51Testing软件测试网 V:\ l~9m
-- tells dbms_stats that no indexes have changed, and51Testing软件测试网@U#\@q$Tx
-- it will only reanalyze histograms for
(WD'y s S|3Kh0-- indexes that have histograms.
Re D)j7e M0--**************************************************************begin51Testing软件测试网%d9yk:e}SZ
dbms_stats.gather_schema_stats(
0o%|0g*F4Bu0ownname => 'SCOTT',
zYu8b]4i4{0estimate_percent => dbms_stats.auto_sample_size,51Testing软件测试网6e\1C#xl"Z0w_
method_opt => 'for all columns size repeat',51Testing软件测试网5odh$Wq o
degree => 751Testing软件测试网(M`D#ox?-Q$AeD
);
[A:cB3[)\*? O G{SD0end;51Testing软件测试网:q\E8rx/| kC.D
使用alter table xxx monitoring;命令来实现Oracle表监视时，需要使用dbms_stats中的auto选项。如清单D所示，auto选项根据数据分布以及应用程序访问列的方式（例如通过监视而确定的一个列的工作量）来创建直方图。使用method_opt=>’auto’类似于在dbms_stats的option参数中使用gather auto。begin
&t^,E4Y ?9bJ|u0dbms_stats.gather_schema_stats(51Testing软件测试网"h.~ZzoW(I
ownname => 'SCOTT',
V,Xm~XU0estimate_percent => dbms_stats.auto_sample_size,51Testing软件测试网:H+| ZN&fR4f
method_opt => 'for all columns size auto',51Testing软件测试网9o:h8dW7WX
degree => 7
$dK9NTy
V.S0);
m Y'u8U/I;G/f j0end;51Testing软件测试网)bKi(BI7bn

并行收集

Oracle允许以并行方式来收集CBO统计数据，这就显著提高了收集统计数据的速度。但是，要想并行收集统计数据，你需要一台安装了多个CPU的SMP服务器。

更快的执行速度

D"D? cIXd
] NIc0dbms_stats是提高SQL执行速度的一种出色机制。通过使用dbms_stats来收集最高质量的统计数据，CBO能够正确判断执行任何SQL查询时的最快途径。dbms_stats还在不断地改进。目前，它的一些令人激动的新特性（自动样本大小和自动直方图生成）已经显著简化了Oracle专家的工作。