CBO对于Oracle SQL执行计划的影响
上一篇 / 下一篇 2009-05-24 15:48:04 / 个人分类：Oracle
- 文件版本: V1.0
- 开发商: 本站原创
- 文件来源: 本地
- 界面语言: 简体中文
- 授权方式: 免费
- 运行平台: Win9X/Win2000/WinXP
1. 原始SQL语句
&n.G-s ` I C&H/q0
IW4i,X/i0 这个SQL语句是一个动态查询语句的一部分，该查询根据不同条件生成不同的SQL语句。
!a[SX\ h0
`N&nnLf&a0 本例为查询2003年以来的入库单据，很少的数据。
i R4|A GW0 51Testing软件测试网E.?&R\9y
SELECT "SP_TRANS"."TRANS_NO",51Testing软件测试网%H!o4m4TvJH
"SP_TRANS"."TRANS_TYPE",51Testing软件测试网 i&zy1k ?-tW+B
"SP_TRANS"."STORE_NO",51Testing软件测试网7{'e z K s-m0?2I
"SP_TRANS"."BILL_NO",51Testing软件测试网v7Y.g*Q0{*T6_ Q.E
"SP_TRANS"."TRANSDATE",51Testing软件测试网pJ1z.Hp1z0h8_q_ y
"SP_TRANS"."MANAGER_ID",51Testing软件测试网3c.s0{*|5SUk-E
"SP_TRANS"."REMARK",
} r aH5Z LG?.H0 "SP_TRANS"."STATE",
^ O6D s X\ N0 "SP_TRANS_SUB"."TRANS_NO",51Testing软件测试网 m5co-vL Tq2L w
"SP_TRANS_SUB"."ITEM_CODE",51Testing软件测试网\+m9d'kb:tsqR n
"SP_TRANS_SUB"."COUNTRY",51Testing软件测试网&d6VLJ.x
"SP_TRANS_SUB"."QTY",
-X y.b(D*PI0 "SP_TRANS_SUB"."PRICE",51Testing软件测试网W Q @&a%p PW
"SP_TRANS_SUB"."TOTAL",
;l I)e(cx9|.[9h%i0 "SP_CHK"."CHK_NO",
S3t)y Ug*S'D ~ x T0 "SP_CHK"."RECEIVE_NO",
3`:u)Zr Ot z.P1N0 "SP_CHK"."CHECKER",
7a#L%WY l&^)}0 "SP_CHK_SUB"."CHK_NO",51Testing软件测试网F&i*Ii t
"SP_CHK_SUB"."ITEM_CODE",
?'Z&g*I P:i2W B"a0 "SP_CHK_SUB"."COUNTRY",
H2z~(r T,`V ^ l0 "SP_CHK_SUB"."PLAN_NO",51Testing软件测试网p n6i9Qik$I
"SP_CHK_SUB"."PLAN_LINE",
Z!V7Fr3H0 "SP_CHK_SUB"."QTY_CHECKOUT",
$`}'N y TI9w0 "SP_CHK_SUB"."NOW_QTY",51Testing软件测试网b1j ab#q W@ q.p
"SP_RECEIVE"."RECEIVE_NO",
!WEp'{3t(N1}.X0 "SP_RECEIVE"."VENDOR_NAME",51Testing软件测试网3?;[+Hf*Bv;a}
"SP_RECEIVE"."BUYER",51Testing软件测试网IS)X"t7?H"U
"SP_RECEIVE_SUB"."RECEIVE_NO",
O j9F6I;|3X3l0L7U0 "SP_RECEIVE_SUB"."PLAN_NO",
p6[k(B/K b/w W B W0 "SP_RECEIVE_SUB"."PLAN_LINE",51Testing软件测试网+eUO9X!I
"SP_RECEIVE_SUB"."ITEM_NAME",51Testing软件测试网UG5z` `
"SP_RECEIVE_SUB"."COUNTRY",
F-k rhfv%gc0 "SP_ITEM"."ITEM_CODE",
r RM!H9HN qU0 "SP_ITEM"."CHART_ID",51Testing软件测试网"VA5y8P/`"`;J
"SP_ITEM"."SPECIFICATION"
)Ri g[3S5d {0 FROM "SP_TRANS",51Testing软件测试网 pu2x [0T-j ?y
"SP_TRANS_SUB",
Wo+l"s*FMr!H#zk0 "SP_CHK",51Testing软件测试网Q O it@A3i,U
"SP_CHK_SUB",51Testing软件测试网kz(E V}5]
"SP_RECEIVE",51Testing软件测试网yZc Oe] G
"SP_RECEIVE_SUB",51Testing软件测试网5CRql!Y,?of
"SP_ITEM"
A(v,L2F"@1i {0 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and51Testing软件测试网 r{'BS N j
("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
gy GUE5f Z#DJi0 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and51Testing软件测试网 l(z\.ld7a_t
( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
!J;@5jo],h r0 ( "SP_CHK"."STATE" = 15 ) and
F*N#v.D6wU8zdC0 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and51Testing软件测试网\ cezv#R
( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
t#z3a Ad_Ekt&`0 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and51Testing软件测试网u [;v+wj7IU
( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and51Testing软件测试网9wU(\BGy%f ?
( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and51Testing软件测试网W,N3x ll9Z;C/XG
( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and
,h](Gz#K y\0 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
!z b+Lh|L0 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and
!z7QC,Y4p L0 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')51Testing软件测试网z2D[xQ7s5e e
/51Testing软件测试网%G1C%e*E7m
_|.Lw.J Gzv M N c4~0 2. 执行计划51Testing软件测试网)a1_/~ ?O XeV
;Ky/~9J!M O&v+?;n0 我们的数据库使用dbms_stats.gather_schema_stats分析过，具有足够及时的所有数据，然而在CBO的执行计划下，优化器选择了完全
m G \H5S O'm6@TU0
,a c"C+g]7c#k0 不同的执行计划.
e?*_u.b0
`'U#S%c z+A i0 a. no hints
-fmm+zZ I(C/F0
?+H+t$R A$k a@0 这是未加任何提示时，Oralce选择的执行路径，在实际程序中，用户说死掉了，通过执行计划我们知道，不是死掉了，是慢！！！
d vd OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)51Testing软件测试网 y Q.[ l sqw gW P
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)51Testing软件测试网)Uc0T S0D P
12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)51Testing软件测试网$Ae lm-o` EEZ
13 4 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
!h w)_H'~7L.Y#d0 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)51Testing软件测试网ChB)\#x
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
d,y Z+pN0 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
9?)i2]-N*k@ z \0 17 16 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)51Testing软件测试网ON"o _"q
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
l'P b6YApJ0 19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)51Testing软件测试网 [6qK }t'Q)HV3Qn T
51Testing软件测试网3A8?1h6yFLP
用足够的耐心，我们得到了该计划的执行结果。
4r$WC q:r&]0 51Testing软件测试网7FLg7u Ofe_
SQL> SELECT "SP_TRANS"."TRANS_NO",
@:X}"Zt5e5L n0 2 "SP_TRANS"."TRANS_TYPE",51Testing软件测试网my1Vy/~sb*s#jA
3 "SP_TRANS"."STORE_NO",51Testing软件测试网$~"I8mo {#?
4 "SP_TRANS"."BILL_NO",51Testing软件测试网7` X8A;K@
5 "SP_TRANS"."TRANSDATE",51Testing软件测试网w ~8O,Z V
6 "SP_TRANS"."MANAGER_ID",51Testing软件测试网[X6{8O#Zi:tyZM
7 "SP_TRANS"."REMARK",
B-\? Q _f _0 8 "SP_TRANS"."STATE",
H.Bq nR"b3a0 9 "SP_TRANS_SUB"."TRANS_NO",
&y6U x ]Sj&|M0 10 "SP_TRANS_SUB"."ITEM_CODE",
@&@3_2L tt ]9ye.Q(G0 11 "SP_TRANS_SUB"."COUNTRY",51Testing软件测试网 |` |$_0uCm8U,J
12 "SP_TRANS_SUB"."QTY",51Testing软件测试网hQTrK$gO)Qe'A
13 "SP_TRANS_SUB"."PRICE",
{ rAb H?KZ0 14 "SP_TRANS_SUB"."TOTAL",
uh T!~0f f(P9H0 15 "SP_CHK"."CHK_NO",51Testing软件测试网3k _7Pw4}[3?T'X
16 "SP_CHK"."RECEIVE_NO",51Testing软件测试网%CJ#z*R!r?t r
17 "SP_CHK"."CHECKER",
1QIno C2H+_;u0 18 "SP_CHK_SUB"."CHK_NO",51Testing软件测试网F`]E y
19 "SP_CHK_SUB"."ITEM_CODE",
z}YA'w uv*G hQ:F0 20 "SP_CHK_SUB"."COUNTRY",51Testing软件测试网F'^[2t:Y$mKc|8b&S*m
21 "SP_CHK_SUB"."PLAN_NO",
-q#b6eFE!l9iG#a0 22 "SP_CHK_SUB"."PLAN_LINE",51Testing软件测试网N xk$L,W E
23 "SP_CHK_SUB"."QTY_CHECKOUT",51Testing软件测试网{a,MF/} T2TKx
24 "SP_CHK_SUB"."NOW_QTY",
O*f F@L@-R0 25 "SP_RECEIVE"."RECEIVE_NO",
2N.[ ofN:X9^"z0 26 "SP_RECEIVE"."VENDOR_NAME",51Testing软件测试网(I6\.UaQ E3] iv
27 "SP_RECEIVE"."BUYER",51Testing软件测试网+C7m'Y;Pd$cC9Z;c3M
28 "SP_RECEIVE_SUB"."RECEIVE_NO",51Testing软件测试网6E%k:D|8|
29 "SP_RECEIVE_SUB"."PLAN_NO",
.}3?d#v)[|E5z'U0 30 "SP_RECEIVE_SUB"."PLAN_LINE",51Testing软件测试网8}om:T|\Y
31 "SP_RECEIVE_SUB"."ITEM_NAME",
U v M}JI*Og y%f0 32 "SP_RECEIVE_SUB"."COUNTRY",
LQ)fj)o0 33 "SP_ITEM"."ITEM_CODE",
_6[h c P`0 34 "SP_ITEM"."CHART_ID",51Testing软件测试网zD'v9n6J4g`
35 "SP_ITEM"."SPECIFICATION"
U0K:Dm Xa0 36 FROM "SP_TRANS",51Testing软件测试网0q#{1O3X2G$P b7i
37 "SP_TRANS_SUB",51Testing软件测试网#S ` yG#nZ7P
38 "SP_CHK",51Testing软件测试网V0H&cR;]{0z
39 "SP_CHK_SUB",51Testing软件测试网 O6{*r%qn*| l*O&r1l
40 "SP_RECEIVE",
dxi-S TvC"])\0 41 "SP_RECEIVE_SUB",51Testing软件测试网k5cY"p9P6U
42 "SP_ITEM"51Testing软件测试网a1Q bP z:rZ;d
43 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
*] nL/Q kk4V6I A EE0 44 ( "SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
.|St+r ?0 45 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and51Testing软件测试网l5w0R6f O9v/cE3Sz
46 ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and51Testing软件测试网:V9[i(h4]
47 ( "SP_CHK"."STATE" = 15 ) and
/{ q:D8|9C0IS}'Y0 48 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
!T"|&E6jM3dR h0 49 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
hn f^:q-e y;I0 50 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and
q P-k xAjwK0 51 ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and51Testing软件测试网9B_,dV a u NZ.B
52 ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and
:f["X Od;y X d |0 53 ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and
fc#O"SF$a0 54 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
7X&e o0c-W p\0 55 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and51Testing软件测试网 J)E]3N:{@2F*W
56 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')51Testing软件测试网U1@#}"v:U1r$x i1d
57 /51Testing软件测试网 Z D i,Hx$eN
51Testing软件测试网oQl7~eP|
130 rows selected.
eQ3r(}&~F${F0
\@$`6` Y0 Elapsed: 00: 29: 1785.4751Testing软件测试网&S[+e A6C
51Testing软件测试网$Fo9J1cAv@p1|
Execution Plan51Testing软件测试网 F6[ L x"_+y4NR
----------------------------------------------------------51Testing软件测试网 q4i|B-T3a+e
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)
-f a'VH c @"M d x0 1 0 NESTED LOOPS (Cost=2057 Card=1 Bytes=288)
L8n;cCZ;bHc%GQ0 2 1 NESTED LOOPS (Cost=2056 Card=1 Bytes=256)
4J(uM f2a4zkC5~ a0 3 2 NESTED LOOPS (Cost=2054 Card=1 Bytes=219)51Testing软件测试网*c!d^3g eH3wA XK$@
4 3 NESTED LOOPS (Cost=2053 Card=1 Bytes=178)51Testing软件测试网~/{QYav8b(z0?/\
5 4 NESTED LOOPS (Cost=2009 Card=1 Bytes=131)
K,J'PX ~0 6 5 MERGE JOIN (Cost=2008 Card=1 Bytes=100)51Testing软件测试网R5Xy)A9r VWZI
7 6 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
`2]$u?%{0 8 7 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)51Testing软件测试网b d$t VC Q
9 6 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)51Testing软件测试网%E*g o){1{$@ {(Zu2p%~
10 9 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)51Testing软件测试网9f&| P!g3B4\ x
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)51Testing软件测试网wP U pDli,u i_
12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
#j ]8w [ q T;Sg$H s s0 13 4 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
do I @)ATF,M D0 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)51Testing软件测试网lQn A'D @.F%N8^ y!e@i
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)51Testing软件测试网 j d4kp)o(|!} Ze
16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)51Testing软件测试网?9U c W)[p1z4P6x0n
17 16 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)51Testing软件测试网v f*R1DJ i"cx
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)51Testing软件测试网F{vq1N1l)?
19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
5j3yW3Fg g0_ D0 51Testing软件测试网-xb Yll l,P4I
Statistics
\l.w Z Wa%K uc0 ----------------------------------------------------------
K#x c{@ M"~ u0 16 recursive calls51Testing软件测试网5y \3@*|"q#l
186307 db block gets
B Byx/fw:dn0 10685361 consistent gets
:s0?[~6M0 2329 physical reads51Testing软件测试网7R#] JA c
0 redo size
"]*CV h\ q1Z0 38486 bytes sent via SQL*Net to client
{3VsCP5] q5Xi:w:m{0 1117 bytes received via SQL*Net from client
ifG3A V$x'um0 10 SQL*Net roundtrips to/from client51Testing软件测试网)g}T&} w-H*t
7 sorts (memory)
;xNv(HP J.rV0 2 sorts (disk)
#le n S1H6h)c/q0 130 rows processed
p@O+zk ^0
S` ~/O)yWi%\3[0 可以看到，该执行计划消耗了大量的资源以及时间，这种情况是无法忍受的。51Testing软件测试网f C0f)c8}L
51Testing软件测试网 Jq$L eb
b. rule
Jb*u#D+?0 51Testing软件测试网c/] Ok1G X ~!C
在RBO条件下，该语句是执行很快的51Testing软件测试网0}bg ^-aV
;mDCh0Q%T XVu0 加入rule提示，我们得到以下执行计划：
W({-M ` E IX0
;R]b ]d3p'\ A z0 Execution Plan51Testing软件测试网 z;uD d*l"O/C.Ab
----------------------------------------------------------
pi YJ2J0 0 SELECT STATEMENT ptimizer=HINT: RULE51Testing软件测试网*V-q]*w&]] \ `
1 0 NESTED LOOPS51Testing软件测试网;S| zS \
2 1 NESTED LOOPS
)r'^^3lr d jG8E0 3 2 NESTED LOOPS
9A V bZC4n aV0 4 3 NESTED LOOPS
r)bd*m!w Z0 5 4 NESTED LOOPS51Testing软件测试网r#n aW0e xM-^
6 5 NESTED LOOPS51Testing软件测试网G,G9h.n6W9q&F
7 6 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'51Testing软件测试网*z#x7h rmV
8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
x[QI(itk-e"l0 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
%H%x ^(\3O T _*b0 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
E&u,S i~0 11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
o Dj1I/v0 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'
9p-AP1X8mNU v0 13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)51Testing软件测试网.o_%i`F5BT
14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'51Testing软件测试网:C@#yE G8L(D ]
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
0G ^7W'?7Z2V y+O0 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'51Testing软件测试网 db R8J5Q0U(m
17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)51Testing软件测试网g;S%L:I#? k~
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'51Testing软件测试网ezIWF$V-m
19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)51Testing软件测试网KV%r ui7n)V;Z ?
51Testing软件测试网5| o2iI)}S q&l
执行该计划，我们得到以下输出：
4r G4e)l3HpQ2e0 51Testing软件测试网(Q(p ? B5_8G{
SQL>@sql51Testing软件测试网? m K*n(dtS+C
51Testing软件测试网 j9h j&q]y8F XL
130 rows selected.
P:u1zF ^*G k4W0
;xz7X S/F-W F*_;`0 Elapsed: 00: 00: 12.17
W*v/\] p'{t0
){/x^ |7J0[ p0 Execution Plan51Testing软件测试网FV-a A)U\!M,D@Z
----------------------------------------------------------51Testing软件测试网 QD+b5x\Dd.Xj
0 SELECT STATEMENT ptimizer=HINT: RULE
*\r J3o6Y'y P X0 1 0 NESTED LOOPS
.rcZ st#[3c0 2 1 NESTED LOOPS
Y"[? Q-f_3v].]0 3 2 NESTED LOOPS
}L7C#V.J0 4 3 NESTED LOOPS51Testing软件测试网7}-w1z0|P4{8xB+\;i
5 4 NESTED LOOPS
2d?0R ~5_&? A0 6 5 NESTED LOOPS
,n)_ vFket"p vz0 7 6 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'
K)} nW5R(m2B0?0 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'51Testing软件测试网d%b$H C a
9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)51Testing软件测试网p uk$R _
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'51Testing软件测试网7Pbm3cKp
11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
"\~/K:o d%A5pT8V0 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'51Testing软件测试网'T ?%Z+d&Ux }YZJ
13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
5JD e p8p @0 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'51Testing软件测试网x M1K ujrU
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
LK8wH;? x#l0 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'51Testing软件测试网(}oo?V4iNQ'E2z
17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)51Testing软件测试网s L*W \C1n;w1Yj j
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'51Testing软件测试网M { l_ w,c3Q/h
19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)51Testing软件测试网%W-sZ1S1`'Ii&l
51Testing软件测试网.S V:b$ce9E P4}`6PN d
Statistics
(NPAjz L6o0 ----------------------------------------------------------
1J"fq"DzW-Qw9xV0 0 recursive calls51Testing软件测试网0S I#?"? L_.D'n
6 db block gets51Testing软件测试网C7u(P'_C2bU*{
829182 consistent gets
4}AM%z RO5N0TG4Z0 0 physical reads51Testing软件测试网&i+D%F?D N*b
0 redo size51Testing软件测试网p3e^)] j
37383 bytes sent via SQL*Net to client51Testing软件测试网1V%?O'e+B-Z bN/l
1127 bytes received via SQL*Net from client51Testing软件测试网/MW j*L#w hG }
10 SQL*Net roundtrips to/from client51Testing软件测试网 E;d6yb?i
0 sorts (memory)
T E0IHy"N0 0 sorts (disk)51Testing软件测试网 Clj0nP&Dd
130 rows processed51Testing软件测试网 D5z%u a j{%D
d6h vy~ D&d#ke#m"r0 SQL>51Testing软件测试网"Y}-t/v'x:k]
+f:J!`3`X;G&Z-i0 c. ordered51Testing软件测试网w'`}1m#n T.mVE;p
h r"Qhf\.`0 然后我想起了Ordered提示
a4^6Y8y8d H:BPS0
~J0D6r @ A#Vb7~0 使用该提示的执行计划如下：51Testing软件测试网ute'UK4LQ ]
z3d;E3Q3g0 SQL>@sql
0`&X]%[B7A;g-i0 51Testing软件测试网 j m$UKe0KU
已选择130行。
6z"W#|1o6l Fn0
|aA[!B J%A5Md t0 已用时间: 00: 00: 05.6751Testing软件测试网1DVS*UR
S"R3F U aj%U z3z.k0 Execution Plan
w1c8`4fij;IN0 ----------------------------------------------------------51Testing软件测试网,? gm1{+xc
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3284 Card=1 Bytes=288)
+_z/N V^0 1 0 NESTED LOOPS (Cost=3284 Card=1 Bytes=288)51Testing软件测试网 l.a*g&TI kNX+C
2 1 NESTED LOOPS (Cost=3283 Card=1 Bytes=256)51Testing软件测试网:G'`4pf8_
3 2 MERGE JOIN (Cost=3282 Card=1 Bytes=204)51Testing软件测试网8zi%R~#X5q t7R b0V&?
4 3 SORT (JOIN) (Cost=2333 Card=6823 Bytes=1064388)
!W0l_ J kts0 5 4 HASH JOIN (Cost=1848 Card=6823 Bytes=1064388)
9e m`wH0 6 5 HASH JOIN (Cost=216 Card=1717 Bytes=204323)51Testing软件测试网%wJ |0vv~6d
7 6 HASH JOIN (Cost=96 Card=1717 Bytes=133926)
x%S ]3n#C |_ V ]0 8 7 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
N2ge/A Q L |O0 9 7 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)
iN5s2?yDB(u)}:gJ0 10 6 TABLE ACCESS (FULL) OF 'SP_RECEIVE' (Cost=17 Card=7816 Bytes=320456)
A'Ms$z5T0 11 5 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=155 Card=136371 Bytes=5045727)
'N&c l(bP0 12 3 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)51Testing软件测试网-D#@0K:k`9|({f6q
13 12 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
}-K/W xI2pR0 14 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' (Cost=1 Card=36730 Bytes=1909960)
`z _#H@&[k,Z!f0 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
$PW Y_H5D.@ k0 16 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)51Testing软件测试网#rW3M8g1i0uY r F dG
17 16 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)51Testing软件测试网M6Q{&FRrRD V b
l$S:`)k A'}:C0 Statistics51Testing软件测试网 L-tMu)]t x UcY
----------------------------------------------------------
6jS;fn7psn0 8 recursive calls
W!oO%mx B` ~0@0 88 db block gets
%U9b&~)U"E h-w0 2667 consistent gets
g _J&e&m0 1093 physical reads51Testing软件测试网 }/wf/\ s9\
0 redo size51Testing软件测试网t+e M(sva-kq
37285 bytes sent via SQL*Net to client
Ty-o+g}.w)Z0 1109 bytes received via SQL*Net from client
9wK%zd \3Hc%A3E l0 10 SQL*Net roundtrips to/from client
:X-Mt$\l2x|0 8 sorts (memory)
B_ j] vNO!J0 1 sorts (disk)
&sz.P0Z J}1~/N5j0 130 rows processed51Testing软件测试网l U,c7Y*tqu
51Testing软件测试网d zR;R%iZ2Ys
SQL>51Testing软件测试网:R W8u2n:_,Y4y
51Testing软件测试网-ghP4@f ES%lWg
很幸运，Ordered提示使Oracle选择了较好的执行计划。51Testing软件测试网I P@4[[ P4i
J W:v3G6Pp YF.V@0 所以会产生这样的效果，是因为在CBO的执行计划中，对于7张数据表，Oracle需要计算7！(5040)个连接顺序，然后比较各个顺序的
M)S-o1q(^J.lWj^%h5g0
f3m['[@&t^-D V4?0 成本，最后选择成本较低的执行计划51Testing软件测试网:o9^.t+FPoyk/`
6E s8|%@U O$J-N E)A3A.u0 显然，在这一判断上耗费了大量的时间。当我们使用ordered hints的时候，Oracle就不需要这一计算步骤，它只需要使用我们指定的51Testing软件测试网'h,fzT2n d
51Testing软件测试网 ZS P.L Y_;~
顺序，然后快速的给出结果。然后问题迎刃而解。
H,o?6A0w R"B,Ce0
C.{hRZv"F0 初试化参数对于执行计划的影响51Testing软件测试网.a6]\@K
51Testing软件测试网l2ui'jl(C
有几个初试化参数对于多表连接的执行计划有重要的关系。
TI l%\%Gk^)j Y7t5`0 51Testing软件测试网p-MB:Y/H'`Fh0eH
在Oracle 8 release 8.0.5中引入了两个参数OPTIMIZER_MAX_PERMUTATIONS 和 OPTIMIZER_SEARCH_LIMIT51Testing软件测试网6b(q2M|:sPr\
51Testing软件测试网+iv#G1Y X n.W$i#j
optimizer_search_limit参数指定了在决定连接多个数据表的最好方式时，CBO需要衡量的数据表连接组合的最大数目。51Testing软件测试网~8fa(|,{d+S
51Testing软件测试网 x:@3f-y^4J(C
该参数的缺省值是5。51Testing软件测试网yO4A:i9R/ZAY
'~~?sr(k0 如果连接表的数目小于optimizer_search_limit参数，那么Oracle会执行所有可能的连接。可能连接的组合数目是数据表数目的阶乘。51Testing软件测试网c"m_K Jx1{N_
51Testing软件测试网7`z2C ~9c7\%D.n
我们刚才有7张表，那么有7!（5040）种组合。51Testing软件测试网LW cf S? tA?
51Testing软件测试网B1Gmr,^ qe
optimizer_max_permutations参数定义了CBO所考虑的表连接的最大数目的上限。
&p n B*Y*RGC x0
!jP#h'kY;s b8q'T0 当我们给这个参数设置很小的一个值的时候，Oracle的计算比较很快就可以被遏制。然后执行计划，给出结果。51Testing软件测试网 R:H$j0_W \_ E*|#E
V9UushU&M yhw0 optimizer_search_limit参数和optimizer_max_permutations参数和Ordered参数不相容，如果定义了ordered提示，那么
f,W2k8b7bD V0 51Testing软件测试网7li%N+AD-|
optimizer_max_permutations参数将会失效。
2U;X@%H s&M#l yOI:M0 51Testing软件测试网 L|y:Sx8G
实际上，当你定义了ordered提示时，oracle已经无需计算了。
%n x8xt3tlR0 51Testing软件测试网wv&`U m s
optimizer_search_limit参数和optimizer_max_permutations参数要结合使用，优化器将在optimizer_search_limit参数或
o1hc3L/q:}0
FTA u;R0 optimizer_max_permutations参数值超出之前，生成可能的表连接转换。当优化器停止对表连接的评估时，它将选择成本最低的组合。
8hpy9u$E w @0 51Testing软件测试网R A D2e U
例如，需要连接9个表的查询已经超出了optimizer_search_limit参数的限制，但是仍然可能要花费大量的时间去试图评估所有362880个
PG5N^4q0 51Testing软件测试网)wm zM[j C5Df
可能的连接顺序(9!),直到超过了optimizer_max_permutations参数的默认值(80000个表连接顺序)。
7Zq y)p3`0 51Testing软件测试网l+\_2h Y }#{
optimizer_max_permutations参数为CBO需要评估的排列数量的最大值。
!|"P%c;R d0
_ r-O*Q N,l0 optimizer_max_permutations的默认值是80000。
sd^nR0H0 51Testing软件测试网 QE8oDk.A2i
在确定查询排列评估数量的上限时，CBO采用的原则是：51Testing软件测试网 W,Ce3_({6s"?
51Testing软件测试网'}(S@R%A.}:bD0m
如果查询中存在的非单一记录表的数目小于optimizer_search_limit+1,那么排列的最大值等于下面两个表达式中较大的数值：
#h,PWO)T_0 51Testing软件测试网uR3EXxv
optimizer_max_permutations51Testing软件测试网 c^5}'rg/Ts$z&` k
______________________________
.y(W9P `/^,c ` M0 (可能启动表的数目+1)
^ yy9w \0p2R0 51Testing软件测试网y p/R*Cr1u9FA
和51Testing软件测试网 md\Ah\ LDz2e
51Testing软件测试网BS6s{6DX~ F Wh
optimizer_search_limit!51Testing软件测试网-Dv cj_k.H*S;pJ/s
___________________________51Testing软件测试网 kfdxPSd8ki
(可能启动表的数目+1)51Testing软件测试网%B?8z&eB {"cs.S(Bm
EA$S7ye A0]@0 例如5个表连接51Testing软件测试网*}[7d \*W
51Testing软件测试网 pm N'Auc(N&FM
排列的最大值= 80000/6=1333351Testing软件测试网-O} {,`(a s3sE
____________________________
(jm'],^ _0~)f Hn I fu0 搜索限制=5!/6=120/6=2051Testing软件测试网"I%i r#AB7o
51Testing软件测试网2@ d%bd*tGd
较大值是13333，这就是优化器要考虑的排列的最大数值(当然实际的数值要比这个小的多,Oracle会排除掉大部分不可能组合)。
g']+f?q Q6} H0~6] d0
&yd8cqk0F L0 SQL> alter session set optimizer_search_limit = 3;51Testing软件测试网6A/` `!N7gO Z
51Testing软件测试网E'g-n,TI&r;^Q
会话已更改。51Testing软件测试网 Ty dbY [c_;F
51Testing软件测试网 V;W U s e
已用时间: 00: 00: 00.60
2Oa%\[a1z)M0
;B$E0E+@v P w iz U0 SQL> alter session set optimizer_max_permutations = 100;51Testing软件测试网1p0u%Q#L$q~k
7Wi7S };c9x(l._ f0 会话已更改。51Testing软件测试网 ink v h:`+b
ezk~9z9N*U)b0 已用时间: 00: 00: 00.90
NM3V(mW0S p,p1B7h!O1L0 SQL> set autotrace traceonly51Testing软件测试网5n,E2m azOh
SQL> SELECT "SP_TRANS"."TRANS_NO",51Testing软件测试网"@H!p cM-I#l*` v
2 "SP_TRANS"."TRANS_TYPE",51Testing软件测试网*^ o3s y8xm2[
3 "SP_TRANS"."STORE_NO",51Testing软件测试网~%mK:[ h
4 "SP_TRANS"."BILL_NO",51Testing软件测试网-wg[ZCn
5 "SP_TRANS"."TRANSDATE",51Testing软件测试网8^3Imc*Gf
6 "SP_TRANS"."MANAGER_ID",51Testing软件测试网#w G F2f#A*?E
7 "SP_TRANS"."REMARK",51Testing软件测试网CWZ(r+TB
8 "SP_TRANS"."STATE",
4FxS c"k3CN ~0 9 "SP_TRANS_SUB"."TRANS_NO",51Testing软件测试网4r4h1K8G?dEe
10 "SP_TRANS_SUB"."ITEM_CODE",
Q R'q`2gY M%R"P)Z W0 11 "SP_TRANS_SUB"."COUNTRY",
6TyG)w8E$A0 12 "SP_TRANS_SUB"."QTY",
w$d N7~w0V%Y0 13 "SP_TRANS_SUB"."PRICE",51Testing软件测试网0^.^;`m7Q.T
14 "SP_TRANS_SUB"."TOTAL",
2H!TI M:|2c5W5h0 15 "SP_CHK"."CHK_NO",
g"h`"F8a`r`'x0 16 "SP_CHK"."RECEIVE_NO",51Testing软件测试网7L H!W S [s ts s
17 "SP_CHK"."CHECKER",
y)Al{Qn;F'p0 18 "SP_CHK_SUB"."CHK_NO",
usu,b I Ti0 19 "SP_CHK_SUB"."ITEM_CODE",51Testing软件测试网;l1kj NP
20 "SP_CHK_SUB"."COUNTRY",
.T7r V P ^J_0 21 "SP_CHK_SUB"."PLAN_NO",51Testing软件测试网 e Vr zoZh.p
22 "SP_CHK_SUB"."PLAN_LINE",
7g9R \3`j6IM_6Zr"Y+@0 23 "SP_CHK_SUB"."QTY_CHECKOUT",
a0{9K[(nl+A0 24 "SP_CHK_SUB"."NOW_QTY",51Testing软件测试网?/t9l/Ec!s
25 "SP_RECEIVE"."RECEIVE_NO",51Testing软件测试网}6RD FH
26 "SP_RECEIVE"."VENDOR_NAME",
])H$G,B+t!e \W0 27 "SP_RECEIVE"."BUYER",51Testing软件测试网 spt zq(O.F1j(rb
28 "SP_RECEIVE_SUB"."RECEIVE_NO",
2D:~_Z9^[}s0 29 "SP_RECEIVE_SUB"."PLAN_NO",
;U%x"F0j)lI L0 30 "SP_RECEIVE_SUB"."PLAN_LINE",
4j.^ ^7Cs[ U3q0 31 "SP_RECEIVE_SUB"."ITEM_NAME",51Testing软件测试网Z(YJF \2y
32 "SP_RECEIVE_SUB"."COUNTRY",
_I)at:P n0 33 "SP_ITEM"."ITEM_CODE",51Testing软件测试网&xe)r-hBH1E
34 "SP_ITEM"."CHART_ID",51Testing软件测试网/J X-YO/dE R9[[
35 "SP_ITEM"."SPECIFICATION"51Testing软件测试网F n7A)s(~
36 FROM "SP_TRANS" ,51Testing软件测试网z!h ~S V qE
37 "SP_CHK" ,51Testing软件测试网 M5WFX oeDO{ c
38 "SP_RECEIVE" ,51Testing软件测试网1l x'o^R/c|]n i
39 "SP_TRANS_SUB" ,
i Cw B]~5L%h/|0 40 "SP_CHK_SUB" ,
3kP/|Ss+S-s6U x0 41 "SP_RECEIVE_SUB" ,
jwvp-H*? F J0 42 "SP_ITEM"51Testing软件测试网p N)l4A/v"[_
43 WHERE
GjL4] D3[ l1R i-Nx0 44 ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and51Testing软件测试网:?mQ'w J&t1R
45 ("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and51Testing软件测试网9u Dk Q3N
46 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and51Testing软件测试网(b YZyYB+Y Io
47 ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
o] ] B H0 48 ( "SP_CHK"."STATE" = 15 ) and51Testing软件测试网+G p D YE&^!SG9ze8pS
49 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and51Testing软件测试网;A D4P&h9l-h9J B
50 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
0tL?2Nr)v#c0 51 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and
J z(D]/a&L"Lo/z7]0 52 ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and
GZ5G ]8bRS!^0 53 ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and51Testing软件测试网ou D1\8?(s{ v
54 ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and51Testing软件测试网l ~Q3Wz%hp;F
55 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
G cG uh TC },B2X!n0 56 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and51Testing软件测试网2c/X1M2N rpE
57 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')
4} k,`4n Q x0 58 /51Testing软件测试网W hB&IR'_G!v
#_4tDP(?0 已选择130行。51Testing软件测试网4R"e)hSF {*X D B
M/aXvU[[ o0 已用时间: 00: 00: 05.78
7x| |_#z]Gf0
#^ DeY0q f[0 Execution Plan
y {fP d`x0 ----------------------------------------------------------51Testing软件测试网M.k)xANv
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2177 Card=1 Bytes=288)51Testing软件测试网;_:t0?Y!Bw
1 0 NESTED LOOPS (Cost=2177 Card=1 Bytes=288)51Testing软件测试网~`s'^"e s m.k |z S
2 1 NESTED LOOPS (Cost=2176 Card=1 Bytes=256)
M-Z o,AX1z y rM4T0 3 2 NESTED LOOPS (Cost=2174 Card=1 Bytes=219)51Testing软件测试网,c"Z%?Z+\Yvh
4 3 MERGE JOIN (Cost=2173 Card=1 Bytes=178)
,n+C2a n m"t0 5 4 SORT (JOIN) (Cost=1115 Card=8081 Bytes=1018206)
}z)SY u0bD(I\0 6 5 HASH JOIN (Cost=645 Card=8081 Bytes=1018206)51Testing软件测试网%RX2]'`wD:w
7 6 HASH JOIN (Cost=96 Card=1717 Bytes=133926)
;^.w(sP(beQ0 8 7 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)51Testing软件测试网7d*x*W `#Wb\B
9 7 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)51Testing软件测试网/P3X{hP
10 6 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
2V9oQJMY6]s;R0 11 4 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
S,n2l w%S!F({X0 12 11 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)51Testing软件测试网V'B9\ l1rS
13 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)51Testing软件测试网O Te/Q WU$e
14 13 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
@f0C%^ U+m0 15 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
"l3uj/u'l0 16 15 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)51Testing软件测试网7]GX)Bx3DP
17 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)51Testing软件测试网 aA&S"]"\8N/x
18 17 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
I |c(Fz HH0
z k*Tj/kJb0 Statistics51Testing软件测试网 Z Ts}8`9M
----------------------------------------------------------
)E"Dt q)a"nNB0 8 recursive calls
FVp2`;g J3j0 131 db block gets
'C J Vy2wZ/x e0 3436 consistent gets51Testing软件测试网;HI|_ WE
1397 physical reads51Testing软件测试网-??MzQy w-l
0 redo size
0{i2{C*~+h8W1[n}0 38555 bytes sent via SQL*Net to client51Testing软件测试网]&vk@&oX s!h
1085 bytes received via SQL*Net from client
z R$cF:l5^E0 10 SQL*Net roundtrips to/from client51Testing软件测试网#h gL6L:m
8 sorts (memory)51Testing软件测试网'[ \ g%lF
1 sorts (disk)51Testing软件测试网"l)p}3_s Yw
130 rows processed
?;dc'p#EB;TC0
Sb|hAU CC0 SQL>51Testing软件测试网 JNi*V+b!r e
51Testing软件测试网6TC+f[;ve+[$i |
3. 其他
9S%^ X"e _Y.vh0
B~3L m)h6M C9s0 在有的系统视图查询中，很多时候会出现问题，比如以下的SQL：
h4a m7C `*j6a MM0R"h0 51Testing软件测试网MjB"sW-t D"^ O
select a.username, a.sid, a.serial#, b.id1
Prr:V |g]7`#W0 from v$session a, v$lock b
DD&W4W EK0 where a.lockwait = b.kaddr51Testing软件测试网+Z5f tY CC
/51Testing软件测试网 A#P Hmb#H/k E
${$d jn2Z%f'C;k L0 这个语句用来查找锁，在Oracle7的年代，这样的SQL语句执行的很快，但是在Oracle8以后的数据库，如果碰巧你用的是CBO，那么51Testing软件测试网ys h6N`)k3a
51Testing软件测试网aprQ PH;d
这样的语句执行结果可能是Hang了（其实不是死了，只是很多人没有耐心等而已），在Oracle7里，这样的语句毫无疑问使用RBO，51Testing软件测试网Im,f3|,[&E
51Testing软件测试网 q'f*]t'oXY$o6S
很快你就可以得到执行结果。可以对于CBO，你所看到的两个视图，对于数据库来说，实际上是6个表，单只6个表的可能顺序组合就有
O7\o c*}-M0 51Testing软件测试网.J1UCZQ2u8J
6!（720）种，数据库时间都消耗在计算这些执行路径上了，所以你得到的就是hang的结果。
q7s U%US|*pgA&}-]B0 51Testing软件测试网 ^h6i.q {Y%t
最简单的解决办法就是使用rule提示，或者使用ordered提示51Testing软件测试网)JG(~4| n(B
J+QEz1H5q0 我们可以看一下这两种方式的执行计划，如果你有兴趣的话，还可以研究一下X$视图：
zl R`7\!L0
gS)@$v7@,b {0 SQL> select /*+ rule */ a.username, a.sid, a.serial#, b.id151Testing软件测试网.y#}2~c S y a:{
2 from v$session a, v$lock b51Testing软件测试网rv%iU;IG
3 where a.lockwait = b.kaddr51Testing软件测试网Ou E!\-nQ;y
4 /51Testing软件测试网 A-R0i&X3A CQ
3}3i3~u({0 未选定行51Testing软件测试网LS` B'Uha
+n/~2n!qQD$}w0 Execution Plan
&gCbT1D x1W-}2a0 ----------------------------------------------------------
V-\ y1RT6P0 0 SELECT STATEMENT ptimizer=HINT: RULE
-r3UZ t;?W \5D5R0 1 0 MERGE JOIN51Testing软件测试网^2nsp%mR%C G R
2 1 SORT (JOIN)
'XF-frTZfY0 3 2 MERGE JOIN
i2r8j2Y5HV `|%V0 4 3 SORT (JOIN)
3bwg _"f C0 5 4 MERGE JOIN51Testing软件测试网-W&i r Y;D
6 5 FIXED TABLE (FULL) OF 'X$KSQRS'51Testing软件测试网8F2G8d(c.O3V)^T*g+Y
7 5 SORT (JOIN)51Testing软件测试网|0q(Zg MJ/h1D
8 7 VIEW OF 'GV$_LOCK'51Testing软件测试网7TQC*I-` A
9 8 UNION-ALL51Testing软件测试网Vp5u$u2P e
10 9 VIEW OF 'GV$_LOCK1'
k7M x$~2bK0 11 10 UNION-ALL
2O }7O[-p0 12 11 FIXED TABLE (FULL) OF 'X$KDNSSF'
2J{H`/J q I }0 13 11 FIXED TABLE (FULL) OF 'X$KSQEQ'51Testing软件测试网5}u)K q.h g;H5\
14 9 FIXED TABLE (FULL) OF 'X$KTADM'
`W Xdp X7J0 15 9 FIXED TABLE (FULL) OF 'X$KTCXB'51Testing软件测试网/aC#],j3m GC0I9\
16 3 SORT (JOIN)51Testing软件测试网~q4Z m ETR-]/B7l1x
17 16 FIXED TABLE (FULL) OF 'X$KSUSE'51Testing软件测试网R9^-hbh P3r+k3e
18 1 SORT (JOIN)
Wo7` gjr!Q$@0 19 18 FIXED TABLE (FULL) OF 'X$KSUSE'51Testing软件测试网/K6e yFn5g
aC `Iq N0 Statistics
(l a { qq4I!qptfp]0 ----------------------------------------------------------51Testing软件测试网.g%H0S6lY
0 recursive calls51Testing软件测试网 M$V P8\B HD
0 db block gets51Testing软件测试网*ju%_(P n!` h ?
0 consistent gets
5v&yW([%C0s0 0 physical reads51Testing软件测试网;zG_)j'I6K
0 redo size
+d V'a$^7k o O-^0 196 bytes sent via SQL*Net to client
W Hg [o0 246 bytes received via SQL*Net from client
t.| N-U? k J%~0 1 SQL*Net roundtrips to/from client
u*X0p f+o0 5 sorts (memory)
ByR!WQ u S/^4f0 0 sorts (disk)51Testing软件测试网$A[.L6^b
0 rows processed51Testing软件测试网W1e'CV TU T8v
51Testing软件测试网;X;_$c5Rr9iVo0W)`;q
对于Ordered提示：51Testing软件测试网'ki,uvv7u z2c&F
+O6Z]/dun*Pt v!A0 SQL> select /*+ ordered */ a.username, a.sid, a.serial#, b.id151Testing软件测试网[J fHpA(Q8{ L
2 from v$session a, v$lock b51Testing软件测试网mO2^/c(H
3 where a.lockwait = b.kaddr51Testing软件测试网 G2]:bVb:U7j{%A2D
4 /51Testing软件测试网%g l3{ d\ z Fp
6~-s%u _$d'Dcc0 未选定行51Testing软件测试网$Yn&XQA X
51Testing软件测试网r(m)O B(unhk
Execution Plan51Testing软件测试网3~&TVD0w[L
----------------------------------------------------------
]$V2xCRHBn0 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=112 Card=1 Bytes=145 )51Testing软件测试网 K@0flW1|n8h
1 0 NESTED LOOPS (Cost=112 Card=1 Bytes=145)51Testing软件测试网 K z$JD$l.q
2 1 NESTED LOOPS (Cost=96 Card=1 Bytes=128)51Testing软件测试网C l"p2n;I
3 2 NESTED LOOPS (Cost=80 Card=1 Bytes=111)51Testing软件测试网g9{"@*s?4H
4 3 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=86)51Testing软件测试网b3_"yf`1P [@ pi
5 3 VIEW OF 'GV$_LOCK'
|B {6Mr3? pT[LW5@#P&Q0 6 5 UNION-ALL
e0pB s)X Y0 7 6 VIEW OF 'GV$_LOCK1' (Cost=32 Card=2 Bytes=162)51Testing软件测试网1{@W3m,X.M
8 7 UNION-ALL
Os_,N l Y-B s0 9 8 FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost=16 Card=1 Bytes=94)
)^1S&S6R6c;U2t0 10 8 FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=16 Card=1 Bytes=94)51Testing软件测试网y"xo5r!G!RT/HN J*Z
11 6 FIXED TABLE (FULL) OF 'X$KTADM' (Cost=16 Card=1 Bytes=94)
L)w _*i.N0 12 6 FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=16 Card=1 Bytes=94)
(X.F:j0hAH[ aNL0 13 2 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=17)51Testing软件测试网 J$qR[ t3E
14 1 FIXED TABLE (FIXED INDEX #1) OF 'X$KSQRS' (Cost=16 Card=100 Bytes=1700)
.s'g7ujho|`8u4K!k0
9K Z)] t&A&e Z0 Statistics
]/W0^ A)M k'n$t&qB5x0 ----------------------------------------------------------
3kb] CZ)i0 0 recursive calls
kEi3q|!Aj0 67 db block gets51Testing软件测试网Q|%Nz J
0 consistent gets51Testing软件测试网f5rJ$]7@E(oN
0 physical reads
:SN5h @Jj0 0 redo size51Testing软件测试网 H9t;} q2J+Lxf
202 bytes sent via SQL*Net to client
RJ n*qcI1V%V D0 244 bytes received via SQL*Net from client51Testing软件测试网7_NQ H"M7[0U
1 SQL*Net roundtrips to/from client51Testing软件测试网8v.F fFQ~:A0W
17 sorts (memory)51Testing软件测试网rz h \U y
0 sorts (disk)
r Mq rWA g0 0 rows processed
3C|%JeR!yQ.b;n0 51Testing软件测试网 T RiD y
SQL>
`1YAJ i k*W0 51Testing软件测试网-m7`f pza!K'Q;H
类似的
.r} l EX4z%kn H0 51Testing软件测试网!i,z U9RG]4huO
SELECT /*+ RULE */
.O7YM.j0Ms(p0 s.SID, s.serial#, l.TYPE, l.id1, l.id2, l.lmode, l.request, l.addr,51Testing软件测试网6bo(~qO z0d'n J
l.kaddr, l.ctime, l.BLOCK, s.username, s.osuser, s.machine,51Testing软件测试网\M3rSeyQ
DECODE (l.id2,
igLP2xv0 0, TO_CHAR (o.owner#) || '-' || o.NAME,51Testing软件测试网6RQ R? q4i O4q7{M GM5g
'Trans-' || TO_CHAR (l.id1) || '-' || l.id2
(\7a t.Q$t!i-?2S+S$c/lj0 ) object_name,51Testing软件测试网7^%M3Vi v
DECODE (l.lmode,51Testing软件测试网 MV bC#HeU5b!fs*_
0, '--Waiting--',51Testing软件测试网9}^pP8K
1, 'Null',51Testing软件测试网 {e1q ^\ |
2, 'Row Share',
o Up} q AC0 3, 'Row Excl',
?'@"V)@0eB7Rr0 4, 'Share',51Testing软件测试网K3H'u!W `CFl
5, 'Sha Row Exc',
X3^fK i\2Vb \0 6, 'Exclusive',
^K i*y_ }1O1Si0 'Other'
*|3Qcsv5A#l&R0 ) lock_mode,
2x8Q)L'e4|;E d)^!w0 DECODE (l.request,
8]J V)r4F:xb]k i5T0 0, ' ',51Testing软件测试网w*F r p0L
1, 'Null',
;H Zo4j9Q0 2, 'Row Share',51Testing软件测试网u bmq!bf
3, 'Row Excl',
'v {&W NfZN0 4, 'Share',
W%f9[)NA0 5, 'Sha Row Exc',
^\ V6Z `;t sC Cv4f3^0 6, 'Exclusive',
`6Iet[D h&dG0 'Other'
&s x K7\Fz}.o0 ) req_mode51Testing软件测试网Lw+Y.t!e@/z } WR U
FROM v$lock l, v$session s, SYS.obj$ o51Testing软件测试网4a"i ?:_ i }
WHERE l.request = 051Testing软件测试网.y9u f"y Q
AND l.SID = s.SID
8N;C bAu0 AND l.id1 = o.obj#(+)
\.C6C7g-}6E%dV0 AND s.username IS NOT NULL
!oOxN Tp[0 ORDER BY s.username, l.SID, l.BLOCK;51Testing软件测试网(O4{G%s U` L
51Testing软件测试网k1{U5L.xm ~
以上问题对于CBO优化器普遍存在，对于Oracle9i2同样如此。
,_OR-S6M*r0E0 51Testing软件测试网2X!^ _r K
幸运的是在Oracle9i中，optimizer_max_permutations初始值降低到2000，从80000到2000，这是一个重大的进步51Testing软件测试网)f.B3gSs$KM {
51Testing软件测试网EF D!onn;m ~T-M,j
其实或者这不能算是问题，对于Oracle这只是一种知识，一种方法而已
&n.G-s ` I C&H/q0
IW4i,X/i0 这个SQL语句是一个动态查询语句的一部分，该查询根据不同条件生成不同的SQL语句。
!a[SX\ h0
`N&nnLf&a0 本例为查询2003年以来的入库单据，很少的数据。
i R4|A GW0 51Testing软件测试网E.?&R\9y
SELECT "SP_TRANS"."TRANS_NO",51Testing软件测试网%H!o4m4TvJH
"SP_TRANS"."TRANS_TYPE",51Testing软件测试网 i&zy1k ?-tW+B
"SP_TRANS"."STORE_NO",51Testing软件测试网7{'e z K s-m0?2I
"SP_TRANS"."BILL_NO",51Testing软件测试网v7Y.g*Q0{*T6_ Q.E
"SP_TRANS"."TRANSDATE",51Testing软件测试网pJ1z.Hp1z0h8_q_ y
"SP_TRANS"."MANAGER_ID",51Testing软件测试网3c.s0{*|5SUk-E
"SP_TRANS"."REMARK",
} r aH5Z LG?.H0 "SP_TRANS"."STATE",
^ O6D s X\ N0 "SP_TRANS_SUB"."TRANS_NO",51Testing软件测试网 m5co-vL Tq2L w
"SP_TRANS_SUB"."ITEM_CODE",51Testing软件测试网\+m9d'kb:tsqR n
"SP_TRANS_SUB"."COUNTRY",51Testing软件测试网&d6VLJ.x
"SP_TRANS_SUB"."QTY",
-X y.b(D*PI0 "SP_TRANS_SUB"."PRICE",51Testing软件测试网W Q @&a%p PW
"SP_TRANS_SUB"."TOTAL",
;l I)e(cx9|.[9h%i0 "SP_CHK"."CHK_NO",
S3t)y Ug*S'D ~ x T0 "SP_CHK"."RECEIVE_NO",
3`:u)Zr Ot z.P1N0 "SP_CHK"."CHECKER",
7a#L%WY l&^)}0 "SP_CHK_SUB"."CHK_NO",51Testing软件测试网F&i*Ii t
"SP_CHK_SUB"."ITEM_CODE",
?'Z&g*I P:i2W B"a0 "SP_CHK_SUB"."COUNTRY",
H2z~(r T,`V ^ l0 "SP_CHK_SUB"."PLAN_NO",51Testing软件测试网p n6i9Qik$I
"SP_CHK_SUB"."PLAN_LINE",
Z!V7Fr3H0 "SP_CHK_SUB"."QTY_CHECKOUT",
$`}'N y TI9w0 "SP_CHK_SUB"."NOW_QTY",51Testing软件测试网b1j ab#q W@ q.p
"SP_RECEIVE"."RECEIVE_NO",
!WEp'{3t(N1}.X0 "SP_RECEIVE"."VENDOR_NAME",51Testing软件测试网3?;[+Hf*Bv;a}
"SP_RECEIVE"."BUYER",51Testing软件测试网IS)X"t7?H"U
"SP_RECEIVE_SUB"."RECEIVE_NO",
O j9F6I;|3X3l0L7U0 "SP_RECEIVE_SUB"."PLAN_NO",
p6[k(B/K b/w W B W0 "SP_RECEIVE_SUB"."PLAN_LINE",51Testing软件测试网+eUO9X!I
"SP_RECEIVE_SUB"."ITEM_NAME",51Testing软件测试网UG5z` `
"SP_RECEIVE_SUB"."COUNTRY",
F-k rhfv%gc0 "SP_ITEM"."ITEM_CODE",
r RM!H9HN qU0 "SP_ITEM"."CHART_ID",51Testing软件测试网"VA5y8P/`"`;J
"SP_ITEM"."SPECIFICATION"
)Ri g[3S5d {0 FROM "SP_TRANS",51Testing软件测试网 pu2x [0T-j ?y
"SP_TRANS_SUB",
Wo+l"s*FMr!H#zk0 "SP_CHK",51Testing软件测试网Q O it@A3i,U
"SP_CHK_SUB",51Testing软件测试网kz(E V}5]
"SP_RECEIVE",51Testing软件测试网yZc Oe] G
"SP_RECEIVE_SUB",51Testing软件测试网5CRql!Y,?of
"SP_ITEM"
A(v,L2F"@1i {0 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and51Testing软件测试网 r{'BS N j
("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
gy GUE5f Z#DJi0 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and51Testing软件测试网 l(z\.ld7a_t
( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
!J;@5jo],h r0 ( "SP_CHK"."STATE" = 15 ) and
F*N#v.D6wU8zdC0 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and51Testing软件测试网\ cezv#R
( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
t#z3a Ad_Ekt&`0 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and51Testing软件测试网u [;v+wj7IU
( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and51Testing软件测试网9wU(\BGy%f ?
( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and51Testing软件测试网W,N3x ll9Z;C/XG
( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and
,h](Gz#K y\0 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
!z b+Lh|L0 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and
!z7QC,Y4p L0 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')51Testing软件测试网z2D[xQ7s5e e
/51Testing软件测试网%G1C%e*E7m
_|.Lw.J Gzv M N c4~0 2. 执行计划51Testing软件测试网)a1_/~ ?O XeV
;Ky/~9J!M O&v+?;n0 我们的数据库使用dbms_stats.gather_schema_stats分析过，具有足够及时的所有数据，然而在CBO的执行计划下，优化器选择了完全
m G \H5S O'm6@TU0
,a c"C+g]7c#k0 不同的执行计划.
e?*_u.b0
`'U#S%c z+A i0 a. no hints
-fmm+zZ I(C/F0
?+H+t$R A$k a@0 这是未加任何提示时，Oralce选择的执行路径，在实际程序中，用户说死掉了，通过执行计划我们知道，不是死掉了，是慢！！！
d vd OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)51Testing软件测试网 y Q.[ l sqw gW P
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)51Testing软件测试网)Uc0T S0D P
12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)51Testing软件测试网$Ae lm-o` EEZ
13 4 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
!h w)_H'~7L.Y#d0 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)51Testing软件测试网ChB)\#x
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
d,y Z+pN0 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
9?)i2]-N*k@ z \0 17 16 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)51Testing软件测试网ON"o _"q
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
l'P b6YApJ0 19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)51Testing软件测试网 [6qK }t'Q)HV3Qn T
51Testing软件测试网3A8?1h6yFLP
用足够的耐心，我们得到了该计划的执行结果。
4r$WC q:r&]0 51Testing软件测试网7FLg7u Ofe_
SQL> SELECT "SP_TRANS"."TRANS_NO",
@:X}"Zt5e5L n0 2 "SP_TRANS"."TRANS_TYPE",51Testing软件测试网my1Vy/~sb*s#jA
3 "SP_TRANS"."STORE_NO",51Testing软件测试网$~"I8mo {#?
4 "SP_TRANS"."BILL_NO",51Testing软件测试网7` X8A;K@
5 "SP_TRANS"."TRANSDATE",51Testing软件测试网w ~8O,Z V
6 "SP_TRANS"."MANAGER_ID",51Testing软件测试网[X6{8O#Zi:tyZM
7 "SP_TRANS"."REMARK",
B-\? Q _f _0 8 "SP_TRANS"."STATE",
H.Bq nR"b3a0 9 "SP_TRANS_SUB"."TRANS_NO",
&y6U x ]Sj&|M0 10 "SP_TRANS_SUB"."ITEM_CODE",
@&@3_2L tt ]9ye.Q(G0 11 "SP_TRANS_SUB"."COUNTRY",51Testing软件测试网 |` |$_0uCm8U,J
12 "SP_TRANS_SUB"."QTY",51Testing软件测试网hQTrK$gO)Qe'A
13 "SP_TRANS_SUB"."PRICE",
{ rAb H?KZ0 14 "SP_TRANS_SUB"."TOTAL",
uh T!~0f f(P9H0 15 "SP_CHK"."CHK_NO",51Testing软件测试网3k _7Pw4}[3?T'X
16 "SP_CHK"."RECEIVE_NO",51Testing软件测试网%CJ#z*R!r?t r
17 "SP_CHK"."CHECKER",
1QIno C2H+_;u0 18 "SP_CHK_SUB"."CHK_NO",51Testing软件测试网F`]E y
19 "SP_CHK_SUB"."ITEM_CODE",
z}YA'w uv*G hQ:F0 20 "SP_CHK_SUB"."COUNTRY",51Testing软件测试网F'^[2t:Y$mKc|8b&S*m
21 "SP_CHK_SUB"."PLAN_NO",
-q#b6eFE!l9iG#a0 22 "SP_CHK_SUB"."PLAN_LINE",51Testing软件测试网N xk$L,W E
23 "SP_CHK_SUB"."QTY_CHECKOUT",51Testing软件测试网{a,MF/} T2TKx
24 "SP_CHK_SUB"."NOW_QTY",
O*f F@L@-R0 25 "SP_RECEIVE"."RECEIVE_NO",
2N.[ ofN:X9^"z0 26 "SP_RECEIVE"."VENDOR_NAME",51Testing软件测试网(I6\.UaQ E3] iv
27 "SP_RECEIVE"."BUYER",51Testing软件测试网+C7m'Y;Pd$cC9Z;c3M
28 "SP_RECEIVE_SUB"."RECEIVE_NO",51Testing软件测试网6E%k:D|8|
29 "SP_RECEIVE_SUB"."PLAN_NO",
.}3?d#v)[|E5z'U0 30 "SP_RECEIVE_SUB"."PLAN_LINE",51Testing软件测试网8}om:T|\Y
31 "SP_RECEIVE_SUB"."ITEM_NAME",
U v M}JI*Og y%f0 32 "SP_RECEIVE_SUB"."COUNTRY",
LQ)fj)o0 33 "SP_ITEM"."ITEM_CODE",
_6[h c P`0 34 "SP_ITEM"."CHART_ID",51Testing软件测试网zD'v9n6J4g`
35 "SP_ITEM"."SPECIFICATION"
U0K:Dm Xa0 36 FROM "SP_TRANS",51Testing软件测试网0q#{1O3X2G$P b7i
37 "SP_TRANS_SUB",51Testing软件测试网#S ` yG#nZ7P
38 "SP_CHK",51Testing软件测试网V0H&cR;]{0z
39 "SP_CHK_SUB",51Testing软件测试网 O6{*r%qn*| l*O&r1l
40 "SP_RECEIVE",
dxi-S TvC"])\0 41 "SP_RECEIVE_SUB",51Testing软件测试网k5cY"p9P6U
42 "SP_ITEM"51Testing软件测试网a1Q bP z:rZ;d
43 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
*] nL/Q kk4V6I A EE0 44 ( "SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
.|St+r ?0 45 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and51Testing软件测试网l5w0R6f O9v/cE3Sz
46 ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and51Testing软件测试网:V9[i(h4]
47 ( "SP_CHK"."STATE" = 15 ) and
/{ q:D8|9C0IS}'Y0 48 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
!T"|&E6jM3dR h0 49 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
hn f^:q-e y;I0 50 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and
q P-k xAjwK0 51 ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and51Testing软件测试网9B_,dV a u NZ.B
52 ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and
:f["X Od;y X d |0 53 ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and
fc#O"SF$a0 54 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
7X&e o0c-W p\0 55 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and51Testing软件测试网 J)E]3N:{@2F*W
56 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')51Testing软件测试网U1@#}"v:U1r$x i1d
57 /51Testing软件测试网 Z D i,Hx$eN
51Testing软件测试网oQl7~eP|
130 rows selected.
eQ3r(}&~F${F0
\@$`6` Y0 Elapsed: 00: 29: 1785.4751Testing软件测试网&S[+e A6C
51Testing软件测试网$Fo9J1cAv@p1|
Execution Plan51Testing软件测试网 F6[ L x"_+y4NR
----------------------------------------------------------51Testing软件测试网 q4i|B-T3a+e
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2057 Card=1 Bytes=288)
-f a'VH c @"M d x0 1 0 NESTED LOOPS (Cost=2057 Card=1 Bytes=288)
L8n;cCZ;bHc%GQ0 2 1 NESTED LOOPS (Cost=2056 Card=1 Bytes=256)
4J(uM f2a4zkC5~ a0 3 2 NESTED LOOPS (Cost=2054 Card=1 Bytes=219)51Testing软件测试网*c!d^3g eH3wA XK$@
4 3 NESTED LOOPS (Cost=2053 Card=1 Bytes=178)51Testing软件测试网~/{QYav8b(z0?/\
5 4 NESTED LOOPS (Cost=2009 Card=1 Bytes=131)
K,J'PX ~0 6 5 MERGE JOIN (Cost=2008 Card=1 Bytes=100)51Testing软件测试网R5Xy)A9r VWZI
7 6 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)
`2]$u?%{0 8 7 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)51Testing软件测试网b d$t VC Q
9 6 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)51Testing软件测试网%E*g o){1{$@ {(Zu2p%~
10 9 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)51Testing软件测试网9f&| P!g3B4\ x
11 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970)51Testing软件测试网wP U pDli,u i_
12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
#j ]8w [ q T;Sg$H s s0 13 4 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
do I @)ATF,M D0 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)51Testing软件测试网lQn A'D @.F%N8^ y!e@i
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)51Testing软件测试网 j d4kp)o(|!} Ze
16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)51Testing软件测试网?9U c W)[p1z4P6x0n
17 16 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)51Testing软件测试网v f*R1DJ i"cx
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)51Testing软件测试网F{vq1N1l)?
19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
5j3yW3Fg g0_ D0 51Testing软件测试网-xb Yll l,P4I
Statistics
\l.w Z Wa%K uc0 ----------------------------------------------------------
K#x c{@ M"~ u0 16 recursive calls51Testing软件测试网5y \3@*|"q#l
186307 db block gets
B Byx/fw:dn0 10685361 consistent gets
:s0?[~6M0 2329 physical reads51Testing软件测试网7R#] JA c
0 redo size
"]*CV h\ q1Z0 38486 bytes sent via SQL*Net to client
{3VsCP5] q5Xi:w:m{0 1117 bytes received via SQL*Net from client
ifG3A V$x'um0 10 SQL*Net roundtrips to/from client51Testing软件测试网)g}T&} w-H*t
7 sorts (memory)
;xNv(HP J.rV0 2 sorts (disk)
#le n S1H6h)c/q0 130 rows processed
p@O+zk ^0
S` ~/O)yWi%\3[0 可以看到，该执行计划消耗了大量的资源以及时间，这种情况是无法忍受的。51Testing软件测试网f C0f)c8}L
51Testing软件测试网 Jq$L eb
b. rule
Jb*u#D+?0 51Testing软件测试网c/] Ok1G X ~!C
在RBO条件下，该语句是执行很快的51Testing软件测试网0}bg ^-aV
;mDCh0Q%T XVu0 加入rule提示，我们得到以下执行计划：
W({-M ` E IX0
;R]b ]d3p'\ A z0 Execution Plan51Testing软件测试网 z;uD d*l"O/C.Ab
----------------------------------------------------------
pi YJ2J0 0 SELECT STATEMENT ptimizer=HINT: RULE51Testing软件测试网*V-q]*w&]] \ `
1 0 NESTED LOOPS51Testing软件测试网;S| zS \
2 1 NESTED LOOPS
)r'^^3lr d jG8E0 3 2 NESTED LOOPS
9A V bZC4n aV0 4 3 NESTED LOOPS
r)bd*m!w Z0 5 4 NESTED LOOPS51Testing软件测试网r#n aW0e xM-^
6 5 NESTED LOOPS51Testing软件测试网G,G9h.n6W9q&F
7 6 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'51Testing软件测试网*z#x7h rmV
8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'
x[QI(itk-e"l0 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
%H%x ^(\3O T _*b0 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'
E&u,S i~0 11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
o Dj1I/v0 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'
9p-AP1X8mNU v0 13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)51Testing软件测试网.o_%i`F5BT
14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'51Testing软件测试网:C@#yE G8L(D ]
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
0G ^7W'?7Z2V y+O0 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'51Testing软件测试网 db R8J5Q0U(m
17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)51Testing软件测试网g;S%L:I#? k~
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'51Testing软件测试网ezIWF$V-m
19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)51Testing软件测试网KV%r ui7n)V;Z ?
51Testing软件测试网5| o2iI)}S q&l
执行该计划，我们得到以下输出：
4r G4e)l3HpQ2e0 51Testing软件测试网(Q(p ? B5_8G{
SQL>@sql51Testing软件测试网? m K*n(dtS+C
51Testing软件测试网 j9h j&q]y8F XL
130 rows selected.
P:u1zF ^*G k4W0
;xz7X S/F-W F*_;`0 Elapsed: 00: 00: 12.17
W*v/\] p'{t0
){/x^ |7J0[ p0 Execution Plan51Testing软件测试网FV-a A)U\!M,D@Z
----------------------------------------------------------51Testing软件测试网 QD+b5x\Dd.Xj
0 SELECT STATEMENT ptimizer=HINT: RULE
*\r J3o6Y'y P X0 1 0 NESTED LOOPS
.rcZ st#[3c0 2 1 NESTED LOOPS
Y"[? Q-f_3v].]0 3 2 NESTED LOOPS
}L7C#V.J0 4 3 NESTED LOOPS51Testing软件测试网7}-w1z0|P4{8xB+\;i
5 4 NESTED LOOPS
2d?0R ~5_&? A0 6 5 NESTED LOOPS
,n)_ vFket"p vz0 7 6 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB'
K)} nW5R(m2B0?0 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM'51Testing软件测试网d%b$H C a
9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)51Testing软件测试网p uk$R _
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS'51Testing软件测试网7Pbm3cKp
11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE)
"\~/K:o d%A5pT8V0 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK'51Testing软件测试网'T ?%Z+d&Ux }YZJ
13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE)
5JD e p8p @0 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE'51Testing软件测试网x M1K ujrU
15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
LK8wH;? x#l0 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB'51Testing软件测试网(}oo?V4iNQ'E2z
17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE)51Testing软件测试网s L*W \C1n;w1Yj j
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB'51Testing软件测试网M { l_ w,c3Q/h
19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)51Testing软件测试网%W-sZ1S1`'Ii&l
51Testing软件测试网.S V:b$ce9E P4}`6PN d
Statistics
(NPAjz L6o0 ----------------------------------------------------------
1J"fq"DzW-Qw9xV0 0 recursive calls51Testing软件测试网0S I#?"? L_.D'n
6 db block gets51Testing软件测试网C7u(P'_C2bU*{
829182 consistent gets
4}AM%z RO5N0TG4Z0 0 physical reads51Testing软件测试网&i+D%F?D N*b
0 redo size51Testing软件测试网p3e^)] j
37383 bytes sent via SQL*Net to client51Testing软件测试网1V%?O'e+B-Z bN/l
1127 bytes received via SQL*Net from client51Testing软件测试网/MW j*L#w hG }
10 SQL*Net roundtrips to/from client51Testing软件测试网 E;d6yb?i
0 sorts (memory)
T E0IHy"N0 0 sorts (disk)51Testing软件测试网 Clj0nP&Dd
130 rows processed51Testing软件测试网 D5z%u a j{%D
d6h vy~ D&d#ke#m"r0 SQL>51Testing软件测试网"Y}-t/v'x:k]
+f:J!`3`X;G&Z-i0 c. ordered51Testing软件测试网w'`}1m#n T.mVE;p
h r"Qhf\.`0 然后我想起了Ordered提示
a4^6Y8y8d H:BPS0
~J0D6r @ A#Vb7~0 使用该提示的执行计划如下：51Testing软件测试网ute'UK4LQ ]
z3d;E3Q3g0 SQL>@sql
0`&X]%[B7A;g-i0 51Testing软件测试网 j m$UKe0KU
已选择130行。
6z"W#|1o6l Fn0
|aA[!B J%A5Md t0 已用时间: 00: 00: 05.6751Testing软件测试网1DVS*UR
S"R3F U aj%U z3z.k0 Execution Plan
w1c8`4fij;IN0 ----------------------------------------------------------51Testing软件测试网,? gm1{+xc
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3284 Card=1 Bytes=288)
+_z/N V^0 1 0 NESTED LOOPS (Cost=3284 Card=1 Bytes=288)51Testing软件测试网 l.a*g&TI kNX+C
2 1 NESTED LOOPS (Cost=3283 Card=1 Bytes=256)51Testing软件测试网:G'`4pf8_
3 2 MERGE JOIN (Cost=3282 Card=1 Bytes=204)51Testing软件测试网8zi%R~#X5q t7R b0V&?
4 3 SORT (JOIN) (Cost=2333 Card=6823 Bytes=1064388)
!W0l_ J kts0 5 4 HASH JOIN (Cost=1848 Card=6823 Bytes=1064388)
9e m`wH0 6 5 HASH JOIN (Cost=216 Card=1717 Bytes=204323)51Testing软件测试网%wJ |0vv~6d
7 6 HASH JOIN (Cost=96 Card=1717 Bytes=133926)
x%S ]3n#C |_ V ]0 8 7 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
N2ge/A Q L |O0 9 7 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)
iN5s2?yDB(u)}:gJ0 10 6 TABLE ACCESS (FULL) OF 'SP_RECEIVE' (Cost=17 Card=7816 Bytes=320456)
A'Ms$z5T0 11 5 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=155 Card=136371 Bytes=5045727)
'N&c l(bP0 12 3 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776)51Testing软件测试网-D#@0K:k`9|({f6q
13 12 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
}-K/W xI2pR0 14 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' (Cost=1 Card=36730 Bytes=1909960)
`z _#H@&[k,Z!f0 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)
$PW Y_H5D.@ k0 16 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)51Testing软件测试网#rW3M8g1i0uY r F dG
17 16 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)51Testing软件测试网M6Q{&FRrRD V b
l$S:`)k A'}:C0 Statistics51Testing软件测试网 L-tMu)]t x UcY
----------------------------------------------------------
6jS;fn7psn0 8 recursive calls
W!oO%mx B` ~0@0 88 db block gets
%U9b&~)U"E h-w0 2667 consistent gets
g _J&e&m0 1093 physical reads51Testing软件测试网 }/wf/\ s9\
0 redo size51Testing软件测试网t+e M(sva-kq
37285 bytes sent via SQL*Net to client
Ty-o+g}.w)Z0 1109 bytes received via SQL*Net from client
9wK%zd \3Hc%A3E l0 10 SQL*Net roundtrips to/from client
:X-Mt$\l2x|0 8 sorts (memory)
B_ j] vNO!J0 1 sorts (disk)
&sz.P0Z J}1~/N5j0 130 rows processed51Testing软件测试网l U,c7Y*tqu
51Testing软件测试网d zR;R%iZ2Ys
SQL>51Testing软件测试网:R W8u2n:_,Y4y
51Testing软件测试网-ghP4@f ES%lWg
很幸运，Ordered提示使Oracle选择了较好的执行计划。51Testing软件测试网I P@4[[ P4i
J W:v3G6Pp YF.V@0 所以会产生这样的效果，是因为在CBO的执行计划中，对于7张数据表，Oracle需要计算7！(5040)个连接顺序，然后比较各个顺序的
M)S-o1q(^J.lWj^%h5g0
f3m['[@&t^-D V4?0 成本，最后选择成本较低的执行计划51Testing软件测试网:o9^.t+FPoyk/`
6E s8|%@U O$J-N E)A3A.u0 显然，在这一判断上耗费了大量的时间。当我们使用ordered hints的时候，Oracle就不需要这一计算步骤，它只需要使用我们指定的51Testing软件测试网'h,fzT2n d
51Testing软件测试网 ZS P.L Y_;~
顺序，然后快速的给出结果。然后问题迎刃而解。
H,o?6A0w R"B,Ce0
C.{hRZv"F0 初试化参数对于执行计划的影响51Testing软件测试网.a6]\@K
51Testing软件测试网l2ui'jl(C
有几个初试化参数对于多表连接的执行计划有重要的关系。
TI l%\%Gk^)j Y7t5`0 51Testing软件测试网p-MB:Y/H'`Fh0eH
在Oracle 8 release 8.0.5中引入了两个参数OPTIMIZER_MAX_PERMUTATIONS 和 OPTIMIZER_SEARCH_LIMIT51Testing软件测试网6b(q2M|:sPr\
51Testing软件测试网+iv#G1Y X n.W$i#j
optimizer_search_limit参数指定了在决定连接多个数据表的最好方式时，CBO需要衡量的数据表连接组合的最大数目。51Testing软件测试网~8fa(|,{d+S
51Testing软件测试网 x:@3f-y^4J(C
该参数的缺省值是5。51Testing软件测试网yO4A:i9R/ZAY
'~~?sr(k0 如果连接表的数目小于optimizer_search_limit参数，那么Oracle会执行所有可能的连接。可能连接的组合数目是数据表数目的阶乘。51Testing软件测试网c"m_K Jx1{N_
51Testing软件测试网7`z2C ~9c7\%D.n
我们刚才有7张表，那么有7!（5040）种组合。51Testing软件测试网LW cf S? tA?
51Testing软件测试网B1Gmr,^ qe
optimizer_max_permutations参数定义了CBO所考虑的表连接的最大数目的上限。
&p n B*Y*RGC x0
!jP#h'kY;s b8q'T0 当我们给这个参数设置很小的一个值的时候，Oracle的计算比较很快就可以被遏制。然后执行计划，给出结果。51Testing软件测试网 R:H$j0_W \_ E*|#E
V9UushU&M yhw0 optimizer_search_limit参数和optimizer_max_permutations参数和Ordered参数不相容，如果定义了ordered提示，那么
f,W2k8b7bD V0 51Testing软件测试网7li%N+AD-|
optimizer_max_permutations参数将会失效。
2U;X@%H s&M#l yOI:M0 51Testing软件测试网 L|y:Sx8G
实际上，当你定义了ordered提示时，oracle已经无需计算了。
%n x8xt3tlR0 51Testing软件测试网wv&`U m s
optimizer_search_limit参数和optimizer_max_permutations参数要结合使用，优化器将在optimizer_search_limit参数或
o1hc3L/q:}0
FTA u;R0 optimizer_max_permutations参数值超出之前，生成可能的表连接转换。当优化器停止对表连接的评估时，它将选择成本最低的组合。
8hpy9u$E w @0 51Testing软件测试网R A D2e U
例如，需要连接9个表的查询已经超出了optimizer_search_limit参数的限制，但是仍然可能要花费大量的时间去试图评估所有362880个
PG5N^4q0 51Testing软件测试网)wm zM[j C5Df
可能的连接顺序(9!),直到超过了optimizer_max_permutations参数的默认值(80000个表连接顺序)。
7Zq y)p3`0 51Testing软件测试网l+\_2h Y }#{
optimizer_max_permutations参数为CBO需要评估的排列数量的最大值。
!|"P%c;R d0
_ r-O*Q N,l0 optimizer_max_permutations的默认值是80000。
sd^nR0H0 51Testing软件测试网 QE8oDk.A2i
在确定查询排列评估数量的上限时，CBO采用的原则是：51Testing软件测试网 W,Ce3_({6s"?
51Testing软件测试网'}(S@R%A.}:bD0m
如果查询中存在的非单一记录表的数目小于optimizer_search_limit+1,那么排列的最大值等于下面两个表达式中较大的数值：
#h,PWO)T_0 51Testing软件测试网uR3EXxv
optimizer_max_permutations51Testing软件测试网 c^5}'rg/Ts$z&` k
______________________________
.y(W9P `/^,c ` M0 (可能启动表的数目+1)
^ yy9w \0p2R0 51Testing软件测试网y p/R*Cr1u9FA
和51Testing软件测试网 md\Ah\ LDz2e
51Testing软件测试网BS6s{6DX~ F Wh
optimizer_search_limit!51Testing软件测试网-Dv cj_k.H*S;pJ/s
___________________________51Testing软件测试网 kfdxPSd8ki
(可能启动表的数目+1)51Testing软件测试网%B?8z&eB {"cs.S(Bm
EA$S7ye A0]@0 例如5个表连接51Testing软件测试网*}[7d \*W
51Testing软件测试网 pm N'Auc(N&FM
排列的最大值= 80000/6=1333351Testing软件测试网-O} {,`(a s3sE
____________________________
(jm'],^ _0~)f Hn I fu0 搜索限制=5!/6=120/6=2051Testing软件测试网"I%i r#AB7o
51Testing软件测试网2@ d%bd*tGd
较大值是13333，这就是优化器要考虑的排列的最大数值(当然实际的数值要比这个小的多,Oracle会排除掉大部分不可能组合)。
g']+f?q Q6} H0~6] d0
&yd8cqk0F L0 SQL> alter session set optimizer_search_limit = 3;51Testing软件测试网6A/` `!N7gO Z
51Testing软件测试网E'g-n,TI&r;^Q
会话已更改。51Testing软件测试网 Ty dbY [c_;F
51Testing软件测试网 V;W U s e
已用时间: 00: 00: 00.60
2Oa%\[a1z)M0
;B$E0E+@v P w iz U0 SQL> alter session set optimizer_max_permutations = 100;51Testing软件测试网1p0u%Q#L$q~k
7Wi7S };c9x(l._ f0 会话已更改。51Testing软件测试网 ink v h:`+b
ezk~9z9N*U)b0 已用时间: 00: 00: 00.90
NM3V(mW0S p,p1B7h!O1L0 SQL> set autotrace traceonly51Testing软件测试网5n,E2m azOh
SQL> SELECT "SP_TRANS"."TRANS_NO",51Testing软件测试网"@H!p cM-I#l*` v
2 "SP_TRANS"."TRANS_TYPE",51Testing软件测试网*^ o3s y8xm2[
3 "SP_TRANS"."STORE_NO",51Testing软件测试网~%mK:[ h
4 "SP_TRANS"."BILL_NO",51Testing软件测试网-wg[ZCn
5 "SP_TRANS"."TRANSDATE",51Testing软件测试网8^3Imc*Gf
6 "SP_TRANS"."MANAGER_ID",51Testing软件测试网#w G F2f#A*?E
7 "SP_TRANS"."REMARK",51Testing软件测试网CWZ(r+TB
8 "SP_TRANS"."STATE",
4FxS c"k3CN ~0 9 "SP_TRANS_SUB"."TRANS_NO",51Testing软件测试网4r4h1K8G?dEe
10 "SP_TRANS_SUB"."ITEM_CODE",
Q R'q`2gY M%R"P)Z W0 11 "SP_TRANS_SUB"."COUNTRY",
6TyG)w8E$A0 12 "SP_TRANS_SUB"."QTY",
w$d N7~w0V%Y0 13 "SP_TRANS_SUB"."PRICE",51Testing软件测试网0^.^;`m7Q.T
14 "SP_TRANS_SUB"."TOTAL",
2H!TI M:|2c5W5h0 15 "SP_CHK"."CHK_NO",
g"h`"F8a`r`'x0 16 "SP_CHK"."RECEIVE_NO",51Testing软件测试网7L H!W S [s ts s
17 "SP_CHK"."CHECKER",
y)Al{Qn;F'p0 18 "SP_CHK_SUB"."CHK_NO",
usu,b I Ti0 19 "SP_CHK_SUB"."ITEM_CODE",51Testing软件测试网;l1kj NP
20 "SP_CHK_SUB"."COUNTRY",
.T7r V P ^J_0 21 "SP_CHK_SUB"."PLAN_NO",51Testing软件测试网 e Vr zoZh.p
22 "SP_CHK_SUB"."PLAN_LINE",
7g9R \3`j6IM_6Zr"Y+@0 23 "SP_CHK_SUB"."QTY_CHECKOUT",
a0{9K[(nl+A0 24 "SP_CHK_SUB"."NOW_QTY",51Testing软件测试网?/t9l/Ec!s
25 "SP_RECEIVE"."RECEIVE_NO",51Testing软件测试网}6RD FH
26 "SP_RECEIVE"."VENDOR_NAME",
])H$G,B+t!e \W0 27 "SP_RECEIVE"."BUYER",51Testing软件测试网 spt zq(O.F1j(rb
28 "SP_RECEIVE_SUB"."RECEIVE_NO",
2D:~_Z9^[}s0 29 "SP_RECEIVE_SUB"."PLAN_NO",
;U%x"F0j)lI L0 30 "SP_RECEIVE_SUB"."PLAN_LINE",
4j.^ ^7Cs[ U3q0 31 "SP_RECEIVE_SUB"."ITEM_NAME",51Testing软件测试网Z(YJF \2y
32 "SP_RECEIVE_SUB"."COUNTRY",
_I)at:P n0 33 "SP_ITEM"."ITEM_CODE",51Testing软件测试网&xe)r-hBH1E
34 "SP_ITEM"."CHART_ID",51Testing软件测试网/J X-YO/dE R9[[
35 "SP_ITEM"."SPECIFICATION"51Testing软件测试网F n7A)s(~
36 FROM "SP_TRANS" ,51Testing软件测试网z!h ~S V qE
37 "SP_CHK" ,51Testing软件测试网 M5WFX oeDO{ c
38 "SP_RECEIVE" ,51Testing软件测试网1l x'o^R/c|]n i
39 "SP_TRANS_SUB" ,
i Cw B]~5L%h/|0 40 "SP_CHK_SUB" ,
3kP/|Ss+S-s6U x0 41 "SP_RECEIVE_SUB" ,
jwvp-H*? F J0 42 "SP_ITEM"51Testing软件测试网p N)l4A/v"[_
43 WHERE
GjL4] D3[ l1R i-Nx0 44 ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and51Testing软件测试网:?mQ'w J&t1R
45 ("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and51Testing软件测试网9u Dk Q3N
46 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and51Testing软件测试网(b YZyYB+Y Io
47 ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
o] ] B H0 48 ( "SP_CHK"."STATE" = 15 ) and51Testing软件测试网+G p D YE&^!SG9ze8pS
49 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and51Testing软件测试网;A D4P&h9l-h9J B
50 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
0tL?2Nr)v#c0 51 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and
J z(D]/a&L"Lo/z7]0 52 ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and
GZ5G ]8bRS!^0 53 ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and51Testing软件测试网ou D1\8?(s{ v
54 ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and51Testing软件测试网l ~Q3Wz%hp;F
55 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
G cG uh TC },B2X!n0 56 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and51Testing软件测试网2c/X1M2N rpE
57 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01')
4} k,`4n Q x0 58 /51Testing软件测试网W hB&IR'_G!v
#_4tDP(?0 已选择130行。51Testing软件测试网4R"e)hSF {*X D B
M/aXvU[[ o0 已用时间: 00: 00: 05.78
7x| |_#z]Gf0
#^ DeY0q f[0 Execution Plan
y {fP d`x0 ----------------------------------------------------------51Testing软件测试网M.k)xANv
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2177 Card=1 Bytes=288)51Testing软件测试网;_:t0?Y!Bw
1 0 NESTED LOOPS (Cost=2177 Card=1 Bytes=288)51Testing软件测试网~`s'^"e s m.k |z S
2 1 NESTED LOOPS (Cost=2176 Card=1 Bytes=256)
M-Z o,AX1z y rM4T0 3 2 NESTED LOOPS (Cost=2174 Card=1 Bytes=219)51Testing软件测试网,c"Z%?Z+\Yvh
4 3 MERGE JOIN (Cost=2173 Card=1 Bytes=178)
,n+C2a n m"t0 5 4 SORT (JOIN) (Cost=1115 Card=8081 Bytes=1018206)
}z)SY u0bD(I\0 6 5 HASH JOIN (Cost=645 Card=8081 Bytes=1018206)51Testing软件测试网%RX2]'`wD:w
7 6 HASH JOIN (Cost=96 Card=1717 Bytes=133926)
;^.w(sP(beQ0 8 7 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)51Testing软件测试网7d*x*W `#Wb\B
9 7 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)51Testing软件测试网/P3X{hP
10 6 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
2V9oQJMY6]s;R0 11 4 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
S,n2l w%S!F({X0 12 11 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)51Testing软件测试网V'B9\ l1rS
13 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)51Testing软件测试网O Te/Q WU$e
14 13 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
@f0C%^ U+m0 15 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
"l3uj/u'l0 16 15 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)51Testing软件测试网7]GX)Bx3DP
17 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)51Testing软件测试网 aA&S"]"\8N/x
18 17 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)
I |c(Fz HH0
z k*Tj/kJb0 Statistics51Testing软件测试网 Z Ts}8`9M
----------------------------------------------------------
)E"Dt q)a"nNB0 8 recursive calls
FVp2`;g J3j0 131 db block gets
'C J Vy2wZ/x e0 3436 consistent gets51Testing软件测试网;HI|_ WE
1397 physical reads51Testing软件测试网-??MzQy w-l
0 redo size
0{i2{C*~+h8W1[n}0 38555 bytes sent via SQL*Net to client51Testing软件测试网]&vk@&oX s!h
1085 bytes received via SQL*Net from client
z R$cF:l5^E0 10 SQL*Net roundtrips to/from client51Testing软件测试网#h gL6L:m
8 sorts (memory)51Testing软件测试网'[ \ g%lF
1 sorts (disk)51Testing软件测试网"l)p}3_s Yw
130 rows processed
?;dc'p#EB;TC0
Sb|hAU CC0 SQL>51Testing软件测试网 JNi*V+b!r e
51Testing软件测试网6TC+f[;ve+[$i |
3. 其他
9S%^ X"e _Y.vh0
B~3L m)h6M C9s0 在有的系统视图查询中，很多时候会出现问题，比如以下的SQL：
h4a m7C `*j6a MM0R"h0 51Testing软件测试网MjB"sW-t D"^ O
select a.username, a.sid, a.serial#, b.id1
Prr:V |g]7`#W0 from v$session a, v$lock b
DD&W4W EK0 where a.lockwait = b.kaddr51Testing软件测试网+Z5f tY CC
/51Testing软件测试网 A#P Hmb#H/k E
${$d jn2Z%f'C;k L0 这个语句用来查找锁，在Oracle7的年代，这样的SQL语句执行的很快，但是在Oracle8以后的数据库，如果碰巧你用的是CBO，那么51Testing软件测试网ys h6N`)k3a
51Testing软件测试网aprQ PH;d
这样的语句执行结果可能是Hang了（其实不是死了，只是很多人没有耐心等而已），在Oracle7里，这样的语句毫无疑问使用RBO，51Testing软件测试网Im,f3|,[&E
51Testing软件测试网 q'f*]t'oXY$o6S
很快你就可以得到执行结果。可以对于CBO，你所看到的两个视图，对于数据库来说，实际上是6个表，单只6个表的可能顺序组合就有
O7\o c*}-M0 51Testing软件测试网.J1UCZQ2u8J
6!（720）种，数据库时间都消耗在计算这些执行路径上了，所以你得到的就是hang的结果。
q7s U%US|*pgA&}-]B0 51Testing软件测试网 ^h6i.q {Y%t
最简单的解决办法就是使用rule提示，或者使用ordered提示51Testing软件测试网)JG(~4| n(B
J+QEz1H5q0 我们可以看一下这两种方式的执行计划，如果你有兴趣的话，还可以研究一下X$视图：
zl R`7\!L0
gS)@$v7@,b {0 SQL> select /*+ rule */ a.username, a.sid, a.serial#, b.id151Testing软件测试网.y#}2~c S y a:{
2 from v$session a, v$lock b51Testing软件测试网rv%iU;IG
3 where a.lockwait = b.kaddr51Testing软件测试网Ou E!\-nQ;y
4 /51Testing软件测试网 A-R0i&X3A CQ
3}3i3~u({0 未选定行51Testing软件测试网LS` B'Uha
+n/~2n!qQD$}w0 Execution Plan
&gCbT1D x1W-}2a0 ----------------------------------------------------------
V-\ y1RT6P0 0 SELECT STATEMENT ptimizer=HINT: RULE
-r3UZ t;?W \5D5R0 1 0 MERGE JOIN51Testing软件测试网^2nsp%mR%C G R
2 1 SORT (JOIN)
'XF-frTZfY0 3 2 MERGE JOIN
i2r8j2Y5HV `|%V0 4 3 SORT (JOIN)
3bwg _"f C0 5 4 MERGE JOIN51Testing软件测试网-W&i r Y;D
6 5 FIXED TABLE (FULL) OF 'X$KSQRS'51Testing软件测试网8F2G8d(c.O3V)^T*g+Y
7 5 SORT (JOIN)51Testing软件测试网|0q(Zg MJ/h1D
8 7 VIEW OF 'GV$_LOCK'51Testing软件测试网7TQC*I-` A
9 8 UNION-ALL51Testing软件测试网Vp5u$u2P e
10 9 VIEW OF 'GV$_LOCK1'
k7M x$~2bK0 11 10 UNION-ALL
2O }7O[-p0 12 11 FIXED TABLE (FULL) OF 'X$KDNSSF'
2J{H`/J q I }0 13 11 FIXED TABLE (FULL) OF 'X$KSQEQ'51Testing软件测试网5}u)K q.h g;H5\
14 9 FIXED TABLE (FULL) OF 'X$KTADM'
`W Xdp X7J0 15 9 FIXED TABLE (FULL) OF 'X$KTCXB'51Testing软件测试网/aC#],j3m GC0I9\
16 3 SORT (JOIN)51Testing软件测试网~q4Z m ETR-]/B7l1x
17 16 FIXED TABLE (FULL) OF 'X$KSUSE'51Testing软件测试网R9^-hbh P3r+k3e
18 1 SORT (JOIN)
Wo7` gjr!Q$@0 19 18 FIXED TABLE (FULL) OF 'X$KSUSE'51Testing软件测试网/K6e yFn5g
aC `Iq N0 Statistics
(l a { qq4I!qptfp]0 ----------------------------------------------------------51Testing软件测试网.g%H0S6lY
0 recursive calls51Testing软件测试网 M$V P8\B HD
0 db block gets51Testing软件测试网*ju%_(P n!` h ?
0 consistent gets
5v&yW([%C0s0 0 physical reads51Testing软件测试网;zG_)j'I6K
0 redo size
+d V'a$^7k o O-^0 196 bytes sent via SQL*Net to client
W Hg [o0 246 bytes received via SQL*Net from client
t.| N-U? k J%~0 1 SQL*Net roundtrips to/from client
u*X0p f+o0 5 sorts (memory)
ByR!WQ u S/^4f0 0 sorts (disk)51Testing软件测试网$A[.L6^b
0 rows processed51Testing软件测试网W1e'CV TU T8v
51Testing软件测试网;X;_$c5Rr9iVo0W)`;q
对于Ordered提示：51Testing软件测试网'ki,uvv7u z2c&F
+O6Z]/dun*Pt v!A0 SQL> select /*+ ordered */ a.username, a.sid, a.serial#, b.id151Testing软件测试网[J fHpA(Q8{ L
2 from v$session a, v$lock b51Testing软件测试网mO2^/c(H
3 where a.lockwait = b.kaddr51Testing软件测试网 G2]:bVb:U7j{%A2D
4 /51Testing软件测试网%g l3{ d\ z Fp
6~-s%u _$d'Dcc0 未选定行51Testing软件测试网$Yn&XQA X
51Testing软件测试网r(m)O B(unhk
Execution Plan51Testing软件测试网3~&TVD0w[L
----------------------------------------------------------
]$V2xCRHBn0 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=112 Card=1 Bytes=145 )51Testing软件测试网 K@0flW1|n8h
1 0 NESTED LOOPS (Cost=112 Card=1 Bytes=145)51Testing软件测试网 K z$JD$l.q
2 1 NESTED LOOPS (Cost=96 Card=1 Bytes=128)51Testing软件测试网C l"p2n;I
3 2 NESTED LOOPS (Cost=80 Card=1 Bytes=111)51Testing软件测试网g9{"@*s?4H
4 3 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=86)51Testing软件测试网b3_"yf`1P [@ pi
5 3 VIEW OF 'GV$_LOCK'
|B {6Mr3? pT[LW5@#P&Q0 6 5 UNION-ALL
e0pB s)X Y0 7 6 VIEW OF 'GV$_LOCK1' (Cost=32 Card=2 Bytes=162)51Testing软件测试网1{@W3m,X.M
8 7 UNION-ALL
Os_,N l Y-B s0 9 8 FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost=16 Card=1 Bytes=94)
)^1S&S6R6c;U2t0 10 8 FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=16 Card=1 Bytes=94)51Testing软件测试网y"xo5r!G!RT/HN J*Z
11 6 FIXED TABLE (FULL) OF 'X$KTADM' (Cost=16 Card=1 Bytes=94)
L)w _*i.N0 12 6 FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=16 Card=1 Bytes=94)
(X.F:j0hAH[ aNL0 13 2 FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=17)51Testing软件测试网 J$qR[ t3E
14 1 FIXED TABLE (FIXED INDEX #1) OF 'X$KSQRS' (Cost=16 Card=100 Bytes=1700)
.s'g7ujho|`8u4K!k0
9K Z)] t&A&e Z0 Statistics
]/W0^ A)M k'n$t&qB5x0 ----------------------------------------------------------
3kb] CZ)i0 0 recursive calls
kEi3q|!Aj0 67 db block gets51Testing软件测试网Q|%Nz J
0 consistent gets51Testing软件测试网f5rJ$]7@E(oN
0 physical reads
:SN5h @Jj0 0 redo size51Testing软件测试网 H9t;} q2J+Lxf
202 bytes sent via SQL*Net to client
RJ n*qcI1V%V D0 244 bytes received via SQL*Net from client51Testing软件测试网7_NQ H"M7[0U
1 SQL*Net roundtrips to/from client51Testing软件测试网8v.F fFQ~:A0W
17 sorts (memory)51Testing软件测试网rz h \U y
0 sorts (disk)
r Mq rWA g0 0 rows processed
3C|%JeR!yQ.b;n0 51Testing软件测试网 T RiD y
SQL>
`1YAJ i k*W0 51Testing软件测试网-m7`f pza!K'Q;H
类似的
.r} l EX4z%kn H0 51Testing软件测试网!i,z U9RG]4huO
SELECT /*+ RULE */
.O7YM.j0Ms(p0 s.SID, s.serial#, l.TYPE, l.id1, l.id2, l.lmode, l.request, l.addr,51Testing软件测试网6bo(~qO z0d'n J
l.kaddr, l.ctime, l.BLOCK, s.username, s.osuser, s.machine,51Testing软件测试网\M3rSeyQ
DECODE (l.id2,
igLP2xv0 0, TO_CHAR (o.owner#) || '-' || o.NAME,51Testing软件测试网6RQ R? q4i O4q7{M GM5g
'Trans-' || TO_CHAR (l.id1) || '-' || l.id2
(\7a t.Q$t!i-?2S+S$c/lj0 ) object_name,51Testing软件测试网7^%M3Vi v
DECODE (l.lmode,51Testing软件测试网 MV bC#HeU5b!fs*_
0, '--Waiting--',51Testing软件测试网9}^pP8K
1, 'Null',51Testing软件测试网 {e1q ^\ |
2, 'Row Share',
o Up} q AC0 3, 'Row Excl',
?'@"V)@0eB7Rr0 4, 'Share',51Testing软件测试网K3H'u!W `CFl
5, 'Sha Row Exc',
X3^fK i\2Vb \0 6, 'Exclusive',
^K i*y_ }1O1Si0 'Other'
*|3Qcsv5A#l&R0 ) lock_mode,
2x8Q)L'e4|;E d)^!w0 DECODE (l.request,
8]J V)r4F:xb]k i5T0 0, ' ',51Testing软件测试网w*F r p0L
1, 'Null',
;H Zo4j9Q0 2, 'Row Share',51Testing软件测试网u bmq!bf
3, 'Row Excl',
'v {&W NfZN0 4, 'Share',
W%f9[)NA0 5, 'Sha Row Exc',
^\ V6Z `;t sC Cv4f3^0 6, 'Exclusive',
`6Iet[D h&dG0 'Other'
&s x K7\Fz}.o0 ) req_mode51Testing软件测试网Lw+Y.t!e@/z } WR U
FROM v$lock l, v$session s, SYS.obj$ o51Testing软件测试网4a"i ?:_ i }
WHERE l.request = 051Testing软件测试网.y9u f"y Q
AND l.SID = s.SID
8N;C bAu0 AND l.id1 = o.obj#(+)
\.C6C7g-}6E%dV0 AND s.username IS NOT NULL
!oOxN Tp[0 ORDER BY s.username, l.SID, l.BLOCK;51Testing软件测试网(O4{G%s U` L
51Testing软件测试网k1{U5L.xm ~
以上问题对于CBO优化器普遍存在，对于Oracle9i2同样如此。
,_OR-S6M*r0E0 51Testing软件测试网2X!^ _r K
幸运的是在Oracle9i中，optimizer_max_permutations初始值降低到2000，从80000到2000，这是一个重大的进步51Testing软件测试网)f.B3gSs$KM {
51Testing软件测试网EF D!onn;m ~T-M,j
其实或者这不能算是问题，对于Oracle这只是一种知识，一种方法而已
TAG:
我的栏目
标题搜索
日历
|
|||||||||
| 日 | 一 | 二 | 三 | 四 | 五 | 六 | |||
| 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 | ||||||
我的存档
数据统计
- 访问量: 225130
- 日志数: 58
- 文件数: 305
- 书签数: 21
- 建立时间: 2007-01-31
- 更新时间: 2009-07-27
