SQL語句為什麼不會共享（中）

 

前篇（http://space.itpub.net/17203031/viewspace-703624）中我們介紹了SQL父子游標機制和兩種SQL不共享的情形。本篇中我們繼續介紹由於物件不一致和cursor_sharing等因素帶來的SQL不共享的問題。

 

5、SQL指定物件相同（2）

 

分別從兩個schema下發出SQL，對應相同的物件。我們這裡使用公有同義詞技術。

 

 

SQL> show user

User is "SYS"

 

SQL> create public synonym m for t;

Synonym created

 

SQL>  select /*+ demo_3 */ count(*) from m;

  COUNT(*)

----------

     18015

 

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

 

SQL>  select /*+ demo_3 */ count(*) from m;

 

  COUNT(*)

----------

18015

 

 

兩句SQL相同，而且指定物件相同。那麼遊標共享情況如何呢？

 

 

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_3 */%';

 

SQL_TEXT                                                                         SQL_ID        OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS  EXECUTIONS VERSION_COUNT

-------------------------------------------------------------------------------- ------------- -------------- ---------- --------------- -------- ---------- -------------

select /*+ demo_3 */ count(*) from m                                             9pdna8fx02604 ALL_ROWS       3120633860      2966233522 6CE7C19C          1             1

select /*+ demo_3 */ count(*) from m                                             8pvzq44h3rnnv ALL_ROWS        540791451      2966233522 695B4930          1             1

 

SQL> select sql_id, PARSING_SCHEMA_NAME, child_number,executions from v$sql where sql_id='9pdna8fx02604';

 

SQL_ID        PARSING_SCHEMA_NAME            CHILD_NUMBER EXECUTIONS

------------- ------------------------------ ------------ ----------

9pdna8fx02604 SYS                                       0          1

 

SQL> select sql_id, PARSING_SCHEMA_NAME, child_number,executions from v$sql where sql_id='8pvzq44h3rnnv';

 

SQL_ID        PARSING_SCHEMA_NAME            CHILD_NUMBER EXECUTIONS

------------- ------------------------------ ------------ ----------

8pvzq44h3rnnv SCOTT                                     0          1

 

 

結果顯而易見，雖然執行SQL相同，對應物件也是相同。但是Oracle在這種情況下，也是會處理為兩個父遊標。這樣的語句不進行共享。

 

 

6、cursor_sharing模式

 

在CBO時代，cursor_sharing可能是我們最常討論的一個引數。cursor_sharing的設定，主要在於對繫結變數SQL的使用和是否將字面SQL整理為繫結變數SQL上。那麼，cursor_sharing不同的設定值，是否會影響到SQL shared_cursor的使用呢？

 

//當前spfile中定義的引數值為EXACT；

SQL> show parameter cursor_sharing;

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

cursor_sharing                       string      EXACT

 

//當前session所使用的引數值；

SQL> select name, value from v$parameter where name='cursor_sharing';

 

NAME                 VALUE

-------------------- --------------------

cursor_sharing       EXACT

 

 

此時我們呼叫Demo的SQL語句。

 

 

SQL> select /*+ demo_4 */ count(*) from t;

 

  COUNT(*)

----------

18015

 

 

我們修改引數值，之後檢視結果。

 

 

SQL> alter session set cursor_sharing=FORCE;

Session altered

 

SQL> select name, value from v$parameter where name='cursor_sharing';

 

NAME                 VALUE

-------------------- --------------------

cursor_sharing       FORCE

 

SQL> select /*+ demo_4 */ count(*) from t;

  COUNT(*)

----------

18015

 

 

此時，我們觀察父子游標檢視。

 

 

SQL> select sql_text, sql_id, OPTIMIZER_MODE, hash_value, plan_hash_value, address, executions,version_count from v$sqlarea where sql_text like 'select /*+ demo_4 */%';

 

SQL_TEXT                                SQL_ID        OPTIMIZER_MODE  EXECUTIONS VERSION_COUNT

--------------------------------------- ------------- --------------  ---------- -------------

select /*+ demo_4 */ count(*) from t    5v4v3gmkh8aaf ALL_ROWS                 2             1

 

 

SQL> select sql_id, PARSING_SCHEMA_NAME, child_number,executions from v$sql where sql_id='5v4v3gmkh8aaf';

 

SQL_ID        PARSING_SCHEMA_NAME            CHILD_NUMBER EXECUTIONS

------------- ------------------------------ ------------ ----------

5v4v3gmkh8aaf SYS                                       0          2

 

 

注意，一個child cursor對應兩次執行。

 

結論：在相同的環境下，cursor_sharing引數不會影響到一般SQL的執行情況。