初始化引數遊標之cursor_sharing

初始化引數之cursor_sharing

一、Cursor_sharing 簡介：

    這個引數是用來告訴Oracle在什麼情況下可以共享遊標，即SQL重用。

    Cursor_sharing引數有3個值可以設定：    

    1)、EXACT：通常來說，exact值是Oracle推薦的，也是預設的，它要求SQL語句在完全相同時才會重用，否則會被重新執行硬解析操作。     

    2)、SIMILAR：similar是在Oracle認為某條SQL語句的謂詞條件可能會影響到它的執行計劃時，才會被重新分析，否則將重用SQL。

    3)、FORCE：force是在任何情況下，無條件重用SQL。

   備註：上面所說的SQL重用，僅僅是指謂詞條件不同的SQL語句，實際上這樣的SQL基本上都在執行同樣的業務操作。

   

二、在Cursor_sharing引數值不同的時對SQL的影響：

   2.1 建立實驗環境：

----首先建立一張woo表---- 

WOO@woo> create table woo (id int,name varchar2(10));
Table created.
Elapsed: 00:00:00.06

 

---產生一些資料---- 

WOO@woo> insert into woo values(1,'aa');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> insert into woo values(2,'bb');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> insert into woo values(3,'cc');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> insert into woo values (4,'dd');
1 row created.
Elapsed: 00:00:00.00
WOO@woo> commit;
Commit complete.
Elapsed: 00:00:00.00
WOO@woo> select * from woo;
        ID NAME
---------- ----------
         1 aa
         2 bb
         3 cc
         4 dd
Elapsed: 00:00:00.01

 

---建立下面實驗將要用到的三張表----

WOO@woo> create table woo_exact as select * from woo;
Table created.
Elapsed: 00:00:00.01
WOO@woo> create table woo_similar as select * from woo;
Table created.
Elapsed: 00:00:00.01
WOO@woo> create table woo_force as select * from woo;
Table created.
Elapsed: 00:00:00.00

 

---檢視當前session的trace檔案的路徑----

WOO@woo> SELECT d.Value || '/' || Lower(Rtrim(i.Instance, Chr(0))) || '_ora_' ||
  2         p.Spid || '.trc' AS "trace_file_name"
  3    FROM (SELECT p.Spid
  4            FROM V$mystat m, V$session s, V$process p
  5           WHERE m.Statistic# = 1
  6             AND s.Sid = m.Sid
  7             AND p.Addr = s.Paddr) p,
  8         (SELECT t.Instance
  9            FROM V$thread t, V$parameter v
 10           WHERE v.Name = 'thread'
 11             AND (v.Value = 0 OR t.Thread# = To_Number(v.Value))) i,
 12         (SELECT VALUE
 13            FROM V$parameter
 14           WHERE NAME = 'user_dump_dest') d;
trace_file_name
-------------------------------------------------------
/DBSoft/diag/rdbms/woo/woo/trace/woo_ora_37746.trc
Elapsed: 00:00:00.01

2.2 cursor_sharing=exact的情況：

WOO@woo>  alter session set cursor_sharing=exact;
Session altered.
Elapsed: 00:00:00.00
WOO@woo>  alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> select * from woo_exact where id=1;
        ID NAME
---------- ----------
         1 aa
Elapsed: 00:00:00.00
WOO@woo> select * from woo_exact where id=2;
        ID NAME
---------- ----------
         2 bb
Elapsed: 00:00:00.01
WOO@woo> select * from woo_exact where id=3;
        ID NAME
---------- ----------
         3 cc
Elapsed: 00:00:00.00
WOO@woo> select * from woo_exact where id=1;
        ID NAME
---------- ----------
         1 aa
Elapsed: 00:00:00.00

----從下面的查詢可以看出執行了兩次硬解析----

WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_exact where%';
SQL_TEXT
---------------------------------------------------------------------------------------
select * from woo_exact where id=1
select * from woo_exact where id=3
select * from woo_exact where id=2
Elapsed: 00:00:00.05
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts                                              0
ADG parselock X get successes                                             0
parse time cpu                                                          326
parse time elapsed                                                      307
parse count (total)                                                   56211
parse count (hard)                                                     1681
parse count (failures)                                                   10
parse count (describe)                                                    0
8 rows selected.

cursor_sharing=similar的情況：

WOO@woo> alter session set cursor_sharing=similar;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.13
WOO@woo> select * from woo_similar where id=1;
        ID NAME
---------- ----------
         1 aa
Elapsed: 00:00:00.01
WOO@woo> select * from woo_similar where id=4;
        ID NAME
---------- ----------
         4 dd
Elapsed: 00:00:00.00
WOO@woo> select * from woo_similar where id=8;
no rows selected
Elapsed: 00:00:00.00

  ----在這裡可以看到執行兩次SQL查詢，只進行了一個硬解析----

WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_similar where %';
SQL_TEXT
--------------------------------------------------------------------------------------------------------
select * from woo_similar where id=:"SYS_B_0"
Elapsed: 00:00:00.02
WOO@woo> select name,value from v$sysstat where name like '%parse%'; 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts                                              0
ADG parselock X get successes                                             0
parse time cpu                                                          374
parse time elapsed                                                      352
parse count (total)                                                   57024
parse count (hard)                                                     2006
parse count (failures)                                                   10
parse count (describe)                                                    0
8 rows selected.
Elapsed: 00:00:00.00
WOO@woo>

     對於SIMILAR的情況，如果CBO發現被繫結變數的謂詞還有其他的執行計劃可以選擇時，如果謂詞條件的值有變化，就將會產生一個新的子游標，而不是重用之前的SQL；如果謂詞沒有其他的執行計劃可選擇，則忽略謂詞的值，重用之前的SQL。

     上面的例子還不能足以說明該情況，接著下面的模擬：

 

 cursor_sharing=force的情況

WOO@woo> alter session set cursor_sharing=force;
Session altered.
Elapsed: 00:00:00.00
WOO@woo> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.07
 
WOO@woo> alter session set sql_trace=true;
Session altered.
Elapsed: 00:00:00.02
WOO@woo> select * from woo_force where id=1;
        ID NAME
---------- ----------
         1 aa
Elapsed: 00:00:00.00
WOO@woo> select * from woo_force where id=4;
        ID NAME
---------- ----------
         4 dd
Elapsed: 00:00:00.00
WOO@woo> select * from woo_force where id=1;
        ID NAME
---------- ----------
         1 aa
Elapsed: 00:00:00.00

  ----從下面的查詢中可以看出只進行了一次硬解析，而且使用了繫結變數----

WOO@woo>  select sql_text from v$sql where sql_text like 'select * from woo_force where %';
SQL_TEXT
-------------------------------------------------------------------------------------------
select * from woo_force where id=:"SYS_B_0"
Elapsed: 00:00:00.02
WOO@woo> select name,value from v$sysstat where name like '%parse%'; 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
ADG parselock X get attempts                                              0
ADG parselock X get successes                                             0
parse time cpu                                                          379
parse time elapsed                                                      355
parse count (total)                                                   57385
parse count (hard)                                                     2145
parse count (failures)                                                   10
parse count (describe)                                                    0
8 rows selected.
Elapsed: 00:00:00.01

總結：force是在任何情況下，無條件重用SQL。  

三、總結：

FORCE和SIMIALR最大的區別在於，FORCE會把所有的謂詞用變數代替，並且不管變數的值如何，一律重用第一條SQL語句，而SIMILAR會根據謂詞的不同，來重新選擇SQL的執行計劃。

如果一個系統，它存在變數繫結的問題，並且這種問題已經影響到了系統的效能，這時候可以考慮將引數cursor_sharing的值設定為SIMILAR或FORCE來改善這種局面，不過在改成SIMILAR或FORCE都可能帶來一些Bug以及很多未知的東西，所以需要慎用。

最後需要說明一點，對於OLTP系統，如果繫結變數情況不好的話，也許可以考慮透過設定這個引數來緩解一下問題；對於是在OLAP系統上，這個引數應該設定成EXACT，並且不應該使用繫結變數，因為在OLAP系統中，SQL的解析對於SQL的執行來看，話費的代價幾乎可以忽略，而正確的SQL執行計劃才是OLAP資料庫最需要關注的。