簡單分析shared pool(三)

提到shared pool，都會不由得和sql語句的解析過程聯絡起來，因為shared pool所做的主要工作就是解析sql語句，生成執行計劃，在之前的兩篇中對於shared pool的儲存進行了簡單的分析，在10g，11g都是保留了255個bucket，可見這個值還是一個最優的預設值了。
在第二篇中分析了一些關於繫結變數的內容，一般一提到sql語句的解析，都會多多少少提到繫結變數，其實有時候也給我們帶來一些困擾，其實直接使用繫結是一種情況，還可以透過其它的方式間接使用。
比如下面的例子。
declare
cursor test_cur is select object_id,object_name from t ;
begin
for i in test_cur loop
insert into t values(i.object_id,i.object_name);
end loop;
commit;
end;
/

我們在pl/sql中使用遊標的方式，可能我們都沒有意識到我們已經在使用了。不過還有一個細節之處就是在pl/sql裡面直接呼叫sql語句的時候，
shared pool裡都是轉換成大寫來處理的。
select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'INSERT%';

HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT  PARSE_CALLS
---------- ------------- ----------- ------------- --------------------
1681598159 c0rddkpk3q9qg           3             1 INSERT INTO T VALUES(:B2 ,:B1 )   1
                                                   
如果在另一種場景中使用繫結變數的方式，結果會略有不同

declare
cursor test_cur is select object_id,object_name from t ;
begin
for i in test_cur loop
execute immediate 'insert into t values(:a,:b)' using i.object_id,i.object_name;
end loop;
commit;
end;
/

使用下面的語句就不會得到需要的資訊了。
select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'INSERT%'
而是需要使用
select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like 'insert%'
HASH_VALUE SQL_ID        CHILD_LATCH VERSION_COUNT SQL_TEXT PARSE_CALLS
---------- ------------- ----------- ------------- --------------------
4026877341 54wqkdbs0ajcx           1             1 insert into t values (:a,:b)    1

得到的sql_id也會大大不同。

如果更近一步我們再來考慮一些額外的影響，我們會發現同樣的sql語句在不同的上下文環境中還會有一些不同，
因為在cursor_sharing=EXTRACT的預設模式下反覆嘗試，Oracle都處理的很好，不會產生大量的child cursor。
為了驗證，我們只好動用一些其他的方法，比如修改最佳化器的一些預設配置，使得兩條sql語句執行中的上下文環境會一些明顯的不同。
SQL> alter system flush shared_pool;  --先來重新整理shared pool

System altered.

SQL> alter session set optimizer_index_caching=100  ; --然後修改這個最佳化器引數

Session altered.

SQL> select hash_value,sql_id,version_count,parse_calls,sql_text from v$sqlarea where sql_text like 'select sum(object_id) from test.test%'; --sql語句還麼有執行，所以沒有任何記錄

no rows selected

SQL> select sum(object_id) from test.test;  --引用了sum函式，使得最佳化器引數生效

SUM(OBJECT_ID)
--------------
      20545604
這個時候去檢視父遊標的資訊，發現version_count只有一個

SQL>  select hash_value,sql_id,version_count,parse_calls,sql_text from v$sqlarea where sql_text like 'select sum(object_id) from test.test%';

HASH_VALUE SQL_ID        VERSION_COUNT PARSE_CALLS   SQL_TEXT
--------------------------------------------------------------------------------
4120698675 afrus1buttrtm             1           1       select sum(object_id) from test.test
然後我們修改最佳化器引數，執行同樣的sql語句
SQL>  alter session set optimizer_index_caching=60;

Session altered.

SQL> select sum(object_id) from test.test;

SUM(OBJECT_ID)
--------------
      20545604
這個時候會發現還是出現了不同之處。version_count變為了2

SQL>  select hash_value,sql_id,version_count,parse_calls,sql_text from v$sqlarea where sql_text like 'select sum(object_id) from test.test%';

HASH_VALUE SQL_ID        VERSION_COUNT PARSE_CALLS  SQL_TEXT
--------------------------------------------------------------------------------
4120698675 afrus1buttrtm             2           2    select sum(object_id) from test.test
如果這個時候檢視子游標的資訊，就會發現child_number會有2個。這也是我們希望看到的不同之處。
SQL> select child_address,hash_value,sql_id,child_number from v$sql where hash_value='4120698675';

CHILD_AD HASH_VALUE SQL_ID        CHILD_NUMBER
-------- ---------- ------------- ------------
2F155C88 4120698675 afrus1buttrtm            0
2F3A26D8 4120698675 afrus1buttrtm            1

總結一下，在sql語句的執行中，cursor是一個很重要的概念，可能會貫穿整個shared pool的各個層面，透過一些補充的實驗可以驗證我們原本固有的思想，可能會發現繫結變數在特定的場景下使用效果是很明顯的，有時候我們可能都沒有意識到本身就在使用。