SQL語句是一種描述性的語言。Oracle接受一個SQL之後，要進行一系列的解析parse操作，最終生成真正操作的步驟——執行計劃。Oracle對一個SQL進行第一次硬解析（hard parse）之後，就將執行計劃快取在SGA的library cache中。

進行執行計劃快取的目的就是進行執行計劃共享，這樣期望在下次輸入相同SQL的時候能夠共享該計劃，減少由於硬解析帶來的Library Cache Lock和Library Cache Pin。但是實際中，真正實現SQL語句執行計劃共享是一件很複雜的事情，要滿足很多現實條件。本系列將從SQL語句遊標共享的角度，討論幾種SQL遊標不能共享的場景。

1、 環境準備

首先，我們準備一個簡單的實驗環境。

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

我們選擇Oracle 10gR2的環境。同時，在sys的使用者schema下，存在一個資料表t。我們本系列中的大部分實驗都是圍繞這個資料表進行的。

2、父子游標的cursor sharing

為了增加本系列的可理解性。在本部分中稍稍介紹Oracle父子游標機制和共享機制。

ü 當Oracle接受到一個全新的SQL時，要進行硬解析hard parse，形成執行計劃。執行計劃對應的shared cursor本質上就是SGA上的一個地址區域。儲存執行計劃是以父遊標parent cursor和子游標child cursor兩部分進行儲存；

ü 父遊標parent cursor對應的就是SQL語句字面相同的SQL與之對應。對每一個父遊標而言，Oracle都會分配一個系統唯一的sql_id與之對應。新SQL進行shared pool尋找可共享的shared cursor的時候，首先定位到的就是父遊標parent cursor；

ü 子游標child cursor是繫結在父遊標上形成多對一關係的執行計劃。由於最佳化器模式等原因，一個父遊標會對應多個子遊標。每個子游標中攜帶的才是真正的執行計劃；

ü 在Oracle內部檢視上，v$sqlarea檢視表示的是父遊標資訊，v$sql檢視中表示的是子游標資訊；

3、 SQL字面值差異

兩個SQL如果語義相同，是否可以實現遊標共享嗎？首先，我們執行兩個SQL語句。

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

COUNT(*)

----------

18015

SQL> select /*+ demo_1 */count(*) from T;

COUNT(*)

----------

18015

兩個SQL語句的差異只是在大小寫拼寫上。我們觀察父子游標情況。

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

SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS

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

select /*+ demo_1 */count(*) from t 1vp29y1g9zjs2 ALL_ROWS 1587529474 2966233522 6967139C

select /*+ demo_1 */count(*) from T 2kjp0bmmn7jsg ALL_ROWS 3879978767 2966233522 69670434

從結果看，Oracle為兩個略微差異的SQL語句生成了不同的父遊標。在v$sqlarea中，有兩行記錄與之對應。但是，我們注意到兩個父遊標對應的plan_hash_value值相同，說明雖然兩個遊標沒有實現共享，但是遊標生成的執行計劃內容上是相同的。

結論：在Oracle中，要實現遊標共享的前提條件，就是輸入SQL的字面值100%相同，不允許有任何包括大小寫的差異。

4、 SQL指定物件相同（1）

SQL字面相同，那麼SQL語句一定可能共享嗎？我們說，不同使用者schema下對應相同的名稱。這樣就可能出現相同的SQL語句透過不同的使用者發出，對應不同物件的情況。下面我們進行試驗。

SQL> col owner for a10;

SQL> col object_name for a20;

SQL> select owner, object_name, object_id from dba_objects where object_name='T';

OWNER OBJECT_NAME OBJECT_ID

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

SYS T 54216

SCOTT T 54136

sys使用者和scott使用者下，存在兩個相同名稱T的物件。但是本質上肯定是兩個物件。

//當前使用者為sys

SQL> show user;

User is "SYS"

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

COUNT(*)

----------

18015

//切換到scott使用者

SQL> conn scott/tiger@orcl;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

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

COUNT(*)

----------

0

兩個SQL字面完全一樣，此時作為相同的SQL語句，在Oracle library cache中是如何呢？

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_2 */%';

SQL_TEXT SQL_ID OPTIMIZER_MODE HASH_VALUE PLAN_HASH_VALUE ADDRESS EXECUTIONS VERSION_COUNT

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

select /*+ demo_2 */ count(*) from 6d100h31dw99u ALL_ROWS 3269338426 2966233522 69654C70 2 2

t

我們在父遊標中只看到了一個父遊標資訊，同時executions為2表示該SQL被執行過兩次，同實際相同。而version_count為2說明兩次執行，雖然可以共享父遊標，但是在子游標層面上，變為兩個子游標，進而是兩個執行計劃。

SQL> select sql_id, PARSING_SCHEMA_NAME, child_number from v$sql where sql_id='6d100h31dw99u';

SQL_ID PARSING_SCHEMA_NAME CHILD_NUMBER

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

6d100h31dw99u SYS 0

6d100h31dw99u SCOTT 1

結論：兩個schema下發出的SQL，如果環境相同，SQL字面值相同，在shared pool中共享父遊標，對應不同的子游標。執行計劃不能共享。

此時，如果我們進一步想，如果兩個SQL字面值相同，而且對應相同的物件，會怎麼樣呢？

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

相關文章