簡單分析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

如果在另一種場景中使用綁定變量的方式，結果會略有不同

execute immediate 'insert into t values(:a,:b)' using i.object_id,i.object_name;

使用下面的語句就不會得到需要的資訊了。

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;

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

這個時候會發現還是出現了不同之處。version_count變為了2

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的各個層面，通過一些補充的實驗可以驗證我們原本固有的思想，可能會發現綁定變量在特定的場景下使用效果是很明顯的，有時候我們可能都沒有意識到本身就在使用。