測試在cursor_sharing為force情況下執行計劃的共享情況

lirenquan發表於2011-03-23
1、首先，我們確定cursor_sharing引數
SQL> show parameter cursor_sharing

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
FORCE
SQL> select spid from v$process where addr in(select paddr from v$session where
sid in(select sid from v$mystat where rownum=1));

SPID
------------
8392
2、測試常量發生變化時，執行計劃的共享情況
SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from tb_test_connect_by where id=4;

no rows selected

SQL> select * from tb_test_connect_by where id=5;

        ID
----------
         5

SQL> select * from tb_test_connect_by where id=6;

        ID
----------
         6

SQL> alter session set sql_trace off
  2  ;
alter session set sql_trace off
                            *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter session set sql_trace=off;
alter session set sql_trace=off
                            *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> alter session set sql_trace=false;

Session altered.

trace結果：
.....

select * 
from
 tb_test_connect_by where id=:"SYS_B_0"


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.01          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        5      0.00       0.00          0          3          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.00       0.01          0          3          0           2

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  INDEX UNIQUE SCAN SYS_C0025401 (cr=1 pr=0 pw=0 time=24 us)(object id 57978)
.....
      從上面的分析結果來看，在cursor_sharing為force時，確實會把常量替換成一個系統自帶的繫結變數，這樣，就可以減少硬解析次數，只是當列的值有傾斜時，可能會導致執行計劃出現問題。

3、測試在繫結變數名稱不一樣的情況下，執行計劃的共享情況

SQL> alter session set sql_trace=true;

Session altered.

SQL> variable id number;
SQL> variable id10 number;
SQL> variable id11 number;
SQL> exec :id=1;
BEGIN :id=1; END;

         *
ERROR at line 1:
ORA-06550: line 1, column 10:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ; indicator
The symbol ":= was inserted before "=" to continue.


SQL> exec :id:=1

PL/SQL procedure successfully completed.

SQL> exec :id10:=2

PL/SQL procedure successfully completed.

SQL> exec :id11:=3

PL/SQL procedure successfully completed.

SQL> select * from tb_test_connect_by where id=:id;

        ID
----------
         1

SQL> select * from tb_test_connect_by where id=:id10;

        ID
----------
         2

SQL> select * from tb_test_connect_by where id=:id11;

        ID
----------
         3

SQL> alter session set sql_trace=false;

Session altered.

trace檔案：
......
select * 
from
 tb_test_connect_by where id=:id


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

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX UNIQUE SCAN SYS_C0025401 (cr=1 pr=0 pw=0 time=79 us)(object id 57978)

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

select * 
from
 tb_test_connect_by where id=:id10


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

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX UNIQUE SCAN SYS_C0025401 (cr=1 pr=0 pw=0 time=16 us)(object id 57978)

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

select * 
from
 tb_test_connect_by where id=:id11


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

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  INDEX UNIQUE SCAN SYS_C0025401 (cr=1 pr=0 pw=0 time=28 us)(object id 57978)
.....
      從測試結果分析來看，當繫結變數的名稱不一樣時，即使在cursor_sharing為force的情況一下，執行計劃也是無法共享的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12350275/viewspace-690250/，如需轉載，請註明出處，否則將追究法律責任。

相關文章