cursor_sharing_exact hints的使用

主要当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.

alter session set sql_trace true

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 56 
********************************************************************************

select count(*)
from
 tt where a=:"SYS_B_0" and b=:"SYS_B_1"

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.03       0.03          0        106          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.03       0.03          0        106          0           2

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 56 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=13558 us)
      1   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=13473 us)

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

select /*+cursor_sharing_exact*/ count(*)
from
 tt where a=2 and b='kyp'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         53          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0         53          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=2726 us)
      1   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=2053 us)

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

select /*+cursor_sharing_exact*/ count(*)
from
 tt where a=1 and b='liu'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0         53          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0         53          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 56 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=53 pr=0 pw=0 time=22016 us)
  27648   TABLE ACCESS FULL TT (cr=53 pr=0 pw=0 time=940507 us)