dbms_stats和analyze的选择(一)

)z L0}$?T#jJ$i_H0

-Ik_o:FdJ:O0dbms_stats和analyze的选择(一)

dbms_stats在很久以前ORACLE就推出了,ITPUB个人空间&q]x
Er^D$Q2M@
那么就意味着我们不使用analyze来分析吗?
Y%M{o#vU,[V/sS0其实.analyze还是有着它存在的理由.ITPUB个人空间fXS+K5cCQ
我们的客户常常会问:我到底使用哪一个分析我的表呢?ITPUB个人空间X B!Z"p"wd;ah
很多人使用analyze命令的方式仅限于compute和estimate以及生成一些直方图.然后把生成的命令置于job以.以便定期作表分析.ITPUB个人空间(E)lu'tPs'd
对于选择这两者之一将很大程度上影响你收集的准确性以及效果.

这里我们选择oracle 9i为例

ITPUB个人空间3N(LZ2u*rW
我们首先来看一下dbms_stats这包.这个包下面一共有40多个存储过程.通常使用的比较多的主要是DBMS_STATS.GATHER_TABLE_STATS 和 DBMS_STATS.GATHER_INDEX_STATS,
9O7Jz%VE:gv:j(pu0来看一下这两个过程的参数:

ITPUB个人空间HNTk,~'N
PROCEDURE GATHER_TABLE_STATSITPUB个人空间T0L,niTtF }#U
参数名称 类型 输入/输出默认值？ITPUB个人空间 N8W.i1F
DoR j9d
------------------------------ ----------------------- ------ --------
~9vcol.],`x|0OWNNAME VARCHAR2 IN
%rXP
}Zh+W0TABNAME VARCHAR2 IN
0Q}1C"zP0PARTNAME VARCHAR2 IN DEFAULT
,J_z'S"R\Jl0ESTIMATE_PERCENT NUMBER IN DEFAULTITPUB个人空间A!W.r)bS4b\exP
BLOCK_SAMPLE BOOLEAN IN DEFAULT
+@1B-]VXse;C6P9K0METHOD_OPT VARCHAR2 IN DEFAULTITPUB个人空间-PM-W4{8Z5g3R f
DEGREE NUMBER IN DEFAULTITPUB个人空间Od](i+e1Dw
GRANULARITY VARCHAR2 IN DEFAULT
YxR4fGL0j1J+lu0CASCADE BOOLEAN IN DEFAULTITPUB个人空间_[j1{3D)cB
STATTAB VARCHAR2 IN DEFAULTITPUB个人空间"T,hw(}8U*l
STATID VARCHAR2 IN DEFAULT
HmiQRia?#h
R0STATOWN VARCHAR2 IN DEFAULTITPUB个人空间q
U [^/M8Db-m-Q
NO_INVALIDATE BOOLEAN IN DEFAULT

K TE.\e8YE"z:Z6r0PROCEDURE GATHER_INDEX_STATS
Eg\)XLA__4fL)_G0参数名称 类型 输入/输出默认值ITPUB个人空间x.y
i2p]/r
------------------------------ ----------------------- ------ --------
"hRH hc(x%]Whf0OWNNAME VARCHAR2 IN
`3EY3T/U{a0INDNAME VARCHAR2 INITPUB个人空间 FI.a7vr)LP
PARTNAME VARCHAR2 IN DEFAULTITPUB个人空间C}4sd {
uM
ESTIMATE_PERCENT NUMBER IN DEFAULT
nGY3n {aH5k7N0STATTAB VARCHAR2 IN DEFAULTITPUB个人空间#cUz#F3S!e
STATID VARCHAR2 IN DEFAULT
"lLa:E@.S0STATOWN VARCHAR2 IN DEFAULT
FyJ'E.R)I0DEGREE NUMBER IN DEFAULT
sK'mE'et bp0GRANULARITY VARCHAR2 IN DEFAULTITPUB个人空间~1c4W h$m-Bs0fk
NO_INVALIDATE BOOLEAN IN DEFAULT

0w3QU9r(q9kIc0estimate_percent:这个参数相当于analyze中的"estimate statistics sample x percent".为总行数的百分比来估算.如果该参数为空.则为compute
@2Z_%o:w0当然也可以使用DBMS_STATS.AUTO_SAMPLE_SIZE 让oracle 来决定估算百分比数

$p9S(KL8C5w-l0block_sample:该参数是一个boolean.在决定是否进行随机采样.

method_opt :该参数表明数据颁布不均衡的情况下是否使用直方图.可选值为"for all columns 或者for all indexed columns.ITPUB个人空间C0~2z:{MX f}
当然也可以使用auto.让oracle来决定收集

cascade :决定是否收集相关表的索引的统计信息.

我们来看看不同的值下产生的性能:

b5U lfky0首先来看表结构:

SQL> desc test;
&q r9]#@*M d0Name Type Nullable Default Comments ITPUB个人空间+D.{0M%Gt$r
---- ------------ -------- ------- -------- ITPUB个人空间fm%oV n"h
ID NUMBER
-N{e!e\4e;{{n0NAME VARCHAR2(20) Y ITPUB个人空间2|-u(c~ \
SEX VARCHAR2(2) Y

SQL>

其中这张表有20480001条数据. 78208个块, 611MB大小.ITPUB个人空间7['ape\u
索引创建情况:ITPUB个人空间qH
q6ap}@
SQL> /

INDEX_NAME UNIQUENES DISTINCT_KEYS SAMPLE_SIZEITPUB个人空间bu#MxP w$\
------------------------------ --------- ------------- -----------
e@;G6plI"J|tYe0B_IDX NONUNIQUE <---bitmap(sex列)ITPUB个人空间-k/e O7K(['Y/B
II UNIQUE 5120000 5120000 <---btree(id 列)

我们使用analyze的estimate 来分析表

&Y1LX6O$^ PV0SQL> analyze table test estimate statistics;

表已分析。

已用时间: 00: 00: 01.00

然后执行下面这个查询:ITPUB个人空间pF2F&I[+Ls;hf
SQL> select * from test where id=1111111111111111;

未选定行

已用时间: 00: 00: 00.01

Execution PlanITPUB个人空间\jyl;L6dQ
----------------------------------------------------------
[.b-N3DM00 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=9)
)~s)m+~pE:['J~01 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Bytes=9)ITPUB个人空间jpG@'I7I c
2 1 INDEX (UNIQUE SCAN) OF 'II' (UNIQUE) (Cost=2 Card=5120000)

Statistics
6Prb#\5]C5_x6NW0----------------------------------------------------------ITPUB个人空间9\2E:p&C x$n N"|(^$dj
0 recursive callsITPUB个人空间.S'Q"n$lW*~$e$s)U
0 db block gets
E
s|8C5cV03 consistent getsITPUB个人空间8e4T#jN,k}A u
3 physical reads
V&~TovL00 redo size
jRB!OsFu0318 bytes sent via SQL*Net to client
4aH)U_7djh9_k0372 bytes received via SQL*Net from clientITPUB个人空间qDa_p:S]cG
1 SQL*Net roundtrips to/from client
^,d.J.|E+`6\ ?2H00 sorts (memory)
F|k
Lo$]00 sorts (disk)
AEJnJ00 rows processed

"H3C:Ji8R,sk1h1I"Y0SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZE
4@v g"e joT)r0---------- ---------- ------ -------- ------------- -----------ITPUB个人空间}$n+K*Y0]1r
TEST NONUNIQUE B_IDX SEX ITPUB个人空间I8q8V6HuF-FN)Y.g
TEST UNIQUE II ID 5120000 5120000

因为id是一个主键.而这里distinct_keys却只是5120000.而真实的情况却是20480001.实际情况差得很远.

我们再看一下DBA_TAB_COLUMNS

SQL> @d:col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETSITPUB个人空间'd
F/qs;k?wj
---------- -------- --------------- ------------ ----------- ------------- ----------
+y ruPj?'Hu.~0TEST ID NUMBER 5120000 5120000 16:17:16 1ITPUB个人空间[:l*U,YU7Uv\
TEST NAME VARCHAR2 1 5120000 16:17:16 1
RD.\\+|8NxPK{0TEST SEX VARCHAR2

继续进行采样:

SQL> analyze table test estimate statistics sample 5 percent;

表已分析。

\9TMS8zvU0SQL> select * from test where id=11111111111111;

-[2nV#v/Z0未选定行

0ws9D;| ^0Execution PlanITPUB个人空间[kJ.ARa
----------------------------------------------------------ITPUB个人空间8W&zl(U*mo
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=9)
:U&hr(lCtE)dk&H01 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=3 Card=1 Bytes=9)

2 1 INDEX (UNIQUE SCAN) OF 'II' (UNIQUE) (Cost=2 Card=5120000)

StatisticsITPUB个人空间~ i ^iF
Cg
----------------------------------------------------------ITPUB个人空间-\ Pd
{p
0 recursive callsITPUB个人空间#JF#R,`+Q3f
0 db block getsITPUB个人空间Z
fL&R,j3C~
v
3 consistent gets
^ b{)YR00 physical reads
aSi\!p&Io-Y]00 redo sizeITPUB个人空间,r*agM.?'drq
318 bytes sent via SQL*Net to client
0{ e Uj/? YW'{v{S0372 bytes received via SQL*Net from clientITPUB个人空间&Ut/R#?NK k
1 SQL*Net roundtrips to/from clientITPUB个人空间Aes#u R8h3n(F9d&t!H%f
0 sorts (memory)
.T*cxDZl}#g00 sorts (disk)
)sG!u-b{o:O!x
a~L00 rows processed

SQL>

同样的:
x}!rs8K0SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZEITPUB个人空间~wSM~$`,D-?f'z/^&\
---------- ---------- ------ -------- ------------- -----------
x(V] h8I+E8O1s0TEST NONUNIQUE B_IDX SEX
8b n(W$}#|K0TEST UNIQUE II ID 5120000 5120000

SQL> @col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETS
}H4GT2S o0---------- -------- --------------- ------------ ----------- ------------- ----------ITPUB个人空间&N/i/b#tc(m
TEST ID NUMBER 5120000 5120000 16:17:16 1
~_)l.A%g;Vhh#Jd`0TEST NAME VARCHAR2 1 5120000 16:17:16 1
:P6hgD9R0TEST SEX VARCHAR2

没有什么变化.与开始是一样的;

SQL> analyze table test compute statistics;

表已分析。

这个过程花了30多分钟.

再来看分析结果.ITPUB个人空间}3o7T4^ W
F
SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZEITPUB个人空间%Z:Fn9@AQo&a
---------- ---------- ------ -------- ------------- -----------ITPUB个人空间 H&R#[6QT7S Y
TEST NONUNIQUE B_IDX SEX 2 834ITPUB个人空间l mQCm]
TEST UNIQUE II ID 20480001 20480001

SQL> @col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETSITPUB个人空间o GR)A_
LC9Gy
---------- -------- --------------- ------------ ----------- ------------- ----------ITPUB个人空间-Z8m$B4Ts)E _
TEST ID NUMBER 20480001 20480001 00:18:53 1
e {:P,C i;\X!K0TEST NAME VARCHAR2 2 20480001 00:18:53 1ITPUB个人空间(x@-cV3b#\%m1V
TEST SEX VARCHAR2 2 20480001 00:18:53 1ITPUB个人空间#IG A-oq i
这一次是完全匹配了.

我们先将原来的统计结果删除:ITPUB个人空间.x5kf~ w)C(H
SQL> analyze table test delete statistics;

Table analyzed

SQL> @col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETS
'L3_%S]^ P;v _/}0---------- -------- --------------- ------------ ----------- ------------- ----------ITPUB个人空间%tD|#{(m2fB%]
TEST ID NUMBER ITPUB个人空间(`y%vIQA{p
TEST NAME VARCHAR2 ITPUB个人空间YO,}Sm`2Bq%D
TEST SEX VARCHAR2

SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZE
Oh$NT8q]L:W z:W0---------- ---------- ------ -------- ------------- -----------
sWZ"ZQn/_(V0TEST NONUNIQUE B_IDX SEX ITPUB个人空间;Go6K!O:N0C
TEST UNIQUE II ID

EXi]8O]f$i?0目前没有任何统计信息.

ITPUB个人空间^E7c'dh
d+f#M0`
再来看看dbms_stats,先按1%来进行取样收集

SQL> EXECUTE dbms_stats.gather_table_stats (ownname=>'TEST', tabname=>'TEST',estimate_percent=>1,cascade=>true)

PL/SQL procedure successfully completed

ITPUB个人空间Ca e7f KF4m;WT
SQL> @col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETSITPUB个人空间w*_~1|JE6V.c
---------- -------- --------------- ------------ ----------- ------------- ----------
.Kn+f[:v+X0TEST ID NUMBER 20559400 205594 01:36:50 1
/X ["AG3i P"H0TEST NAME VARCHAR2 1 205594 01:36:50 1
;`G.kJh;k-pS%jw0TEST SEX VARCHAR2 2 205594 01:36:50 1

SQL>

SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZE
CC"~w\:d$fhe0---------- ---------- ------ -------- ------------- -----------
w'PY6~ `6Q^ G0TEST NONUNIQUE B_IDX SEX 2 834ITPUB个人空间$CE:V'Kk8L_
TEST UNIQUE II ID 20585910 2058591

SQL>

统计结果是20585910,而正确的应该是20480001,但相差得并不多.

(hm$_9L,OV*n0再来按5%进行取样:

It R]A0SQL> EXECUTE dbms_stats.gather_table_stats (ownname=>'TEST', tabname=>'TEST',estimate_percent=>5,cascade=>true)

PL/SQL procedure successfully completed

SQL> @col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETSITPUB个人空间\lUA)SZ:\
---------- -------- --------------- ------------ ----------- ------------- ----------ITPUB个人空间-?8|*X`6A}kB
TEST ID NUMBER 20481740 1024087 02:37:35 1
r9_b(p6^0TEST NAME VARCHAR2 1 1024087 02:37:35 1ITPUB个人空间;J C0}$E"A|MG4[
TEST SEX VARCHAR2 2 1024087 02:37:35 1

SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZEITPUB个人空间 tod"b0qzA
---------- ---------- ------ -------- ------------- -----------ITPUB个人空间ZuA!R$@m+R V
TEST NONUNIQUE B_IDX SEX 2 834
1T[X;T-C]!K+{0TEST UNIQUE II ID 19941800 997090

SQL>

20481740,已经比开始接近准确的信息了.

SQL> EXECUTE dbms_stats.gather_table_stats (ownname=>'TEST', tabname=>'TEST',estimate_percent=>null,cascade=>true)

PL/SQL procedure successfully completed

SQL> @col

TABLE_NAME COLUMN_N DATA_TYPE NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED BUCKETS
K%^XH3K3Du%D$p/hR0---------- -------- --------------- ------------ ----------- ------------- ----------ITPUB个人空间.o|:ce!ks~
TEST ID NUMBER 20480001 20480001 02:41:59 1
QQ6^ER0TEST NAME VARCHAR2 2 20480001 02:41:59 1ITPUB个人空间~y-`{:UFhKA
h,Q}i
TEST SEX VARCHAR2 2 20480001 02:41:59 1

SQL> @index

TABLE_NAME UNIQUENESS INDEX_ COLUMN_N DISTINCT_KEYS SAMPLE_SIZE
$x Dx/{l$m]0---------- ---------- ------ -------- ------------- -----------ITPUB个人空间"O5}!d5M.W*F
TEST NONUNIQUE B_IDX SEX 2 834
C ykrS"z0TEST UNIQUE II ID 20480001 20480001

ITPUB个人空间/D#lW~9A?+iQ3zd
待续......