cursor_sharing_exact hints的使用

cursor_sharing_exact hints的使用

ITPUB个人空间CM"GcZM6U
主要当cursor_sharing=similar或者force的时候使用比较方便,

以下就是为cursor_sharing=similar的时候的一个sqltrace.

可以看到当使用hints cursor_sharing_exact后,

Misses in library cache during parse重新变为1,

也就是说oracle并不强制转换为bind var

与这个参数对应的也有cursor_sharing_similar.

ITPUB个人空间%i [6c2I/A6im|Xx
alter session set sql_trace true

ITPUB个人空间2\p4D&ce\F
call     count       cpu    elapsed       disk      query    current        rowsITPUB个人空间l_ |A"Dc;|{%P%x
------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间@"bgW7A-j
Parse        0      0.00       0.00          0          0          0           0
V%\ n_mqcN8F0d3nK104152Execute      1      0.00       0.00          0          0          0           0ITPUB个人空间(u3~9D'GbF'U5SY
Fetch        0      0.00       0.00          0          0          0           0ITPUB个人空间C.s(b#r_Z"W
------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间5e(Rr.L/r\v
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Gn;FE;s-v?L p*R1z-_104152Optimizer mode: ALL_ROWS
:kZp:Q_A%}1p104152Parsing user id: 56 
g;PhG n_,B104152********************************************************************************

select count(*)
ni#hp%o-aSg/_104152fromITPUB个人空间.vnd I W {~
 tt where a=:"SYS_B_0" and b=:"SYS_B_1"

ITPUB个人空间V"ZY/W&|4z%tP
call     count       cpu    elapsed       disk      query    current        rows
qD M+L2}B104152------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间z'e+m{_3@
Parse        2      0.00       0.00          0          0          0           0ITPUB个人空间jH#kPh4bn.j.^
Execute      2      0.00       0.00          0          0          0           0ITPUB个人空间I7SK8y9n
Fetch        4      0.03       0.03          0        106          0           2
1Ng.vh.x104152------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间)Z7cRg{#e0h
total        8      0.03       0.03          0        106          0           2

Misses in library cache during parse: 0
4@,Djx(b9@!Y104152Optimizer mode: ALL_ROWS
6R"qH+NEh)]`"G's*D104152Parsing user id: 56 

Rows     Row Source Operation
7N1X,r7wM7zV
IX]104152-------  ---------------------------------------------------
n9^#r,ZxC`$r104152      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=13558 us)ITPUB个人空间-`\&U
L)Tu
      1   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=13473 us)

********************************************************************************

select /*+cursor_sharing_exact*/ count(*)
"DI:iVltx I+qf104152fromITPUB个人空间1Lb(m*Pb2@
 tt where a=2 and b='kyp'

cD&r2Tr~K.F7Z104152call     count       cpu    elapsed       disk      query    current        rows
#vN/b&_|9C z@#r104152------- ------  -------- ---------- ---------- ---------- ----------  ----------
@aZ F f uni
m1~y104152Parse        1      0.02       0.02          0          0          0           0
N;QP2V%[9k104152Execute      1      0.00       0.00          0          0          0           0ITPUB个人空间 L8R@n8i#lC6d)C
Fetch        2      0.00       0.00          0         53          0           1ITPUB个人空间S_'uQ8A4[dO@/L3t
------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间Tnz
S3V
total        4      0.02       0.02          0         53          0           1

Misses in library cache during parse: 1ITPUB个人空间(ui-g^L$c:a2r
Optimizer mode: ALL_ROWSITPUB个人空间I'pRD;Z@CTS
Parsing user id: 56 

Rows     Row Source Operation
$A\.{J&PD+y&\"Rv104152-------  ---------------------------------------------------
fI(s[m,B104152      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=2726 us)ITPUB个人空间/z'Lbh;Z'Q1}!Gxy
      1   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=2053 us)

********************************************************************************

select /*+cursor_sharing_exact*/ count(*)ITPUB个人空间1_rH|uw:p4H]^{
from

y+w"H4b/Q7s%jY104152 tt where a=1 and b='liu'

ITPUB个人空间C6@-NQp,T#J"I/x^
call     count       cpu    elapsed       disk      query    current        rowsITPUB个人空间[UG6r3Cb/{A
------- ------  -------- ---------- ---------- ---------- ----------  ----------
eZ5U[+trY;Nw7i.`104152Parse        1      0.00       0.00          0          0          0           0ITPUB个人空间F1VFN*Tan6z)|*u{aE
Execute      1      0.00       0.00          0          0          0           0
!Js] |x)g6A104152Fetch        2      0.02       0.02          0         53          0           1
aC5a+`*y+F104152------- ------  -------- ---------- ---------- ---------- ----------  ----------ITPUB个人空间%upUTZi$F
total        4      0.02       0.02          0         53          0           1

Misses in library cache during parse: 1
XG8O8]s8]5g104152Optimizer mode: ALL_ROWS
|6e3xCNq-f7x9ger104152Parsing user id: 56 

Rows     Row Source Operation
S9d$JFE!Ek
EsK104152-------  ---------------------------------------------------
U$n{m9q3|6k5e104152      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=22016 us)
#S)\)M3h`W104152  27648   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=940507 us)ITPUB个人空间/a2r1G'@D5{