测试在cursor_sharing为force情况下执行计划的共享情况

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12350275/viewspace-690250/