簡單分析shared pool(二)

對於shared pool的學習，發現越嘗試去了解，發現自己對它越不瞭解。裡面的東西很雜。
自己想用幾個問題來作為引子來說明更加會有條理一些。
shared pool的大小設定
對於shared pool的大小設定，從早期版本到現在一直都帶有爭論。
從操作上來說，需要設定shared_pool_size就可以了，如果啟用了sga_target或者11g裡的memory_target，那shared pool的大小設定都是自動管理的了。
還有shared_pool_reserved_size會在shared pool中保留一塊固定的記憶體區域留給一些大物件使用，它的空間是獨立的。
從理論上來說,shared pool中含有的free list,或者是bucket，上面有很多的chunk，如果一條sql語句進入library cache，需要申請一塊新的記憶體空間的時候，就需要遍歷free list，如果free list很長，在這個過程中也會持有latch，直到解析完成。所以從這個角度來說，設定小的shared pool可能能減少latch的持有時間，但是反過來說，如果shared pool太小，可能空間老是緊張，會有頻繁的換入換出的chunk操作，無論掃描還是對chunk的管理都是需要持有latch的，都在一定程度上影響效能，如果設定shared Pool大一些，可能能夠延緩一下latch的爭用，但是如果隨著free list中的碎片增多。導致free型別的chunk越來越多，最後也還是會造成爭用。

shared pool的sub pool
這個問題可以從第一個問題得到延伸，如果單純設定shared pool過大存在問題，設定太小也有問題，從oracle的設計角度來說，就根據系統的情況，可以指定sub pool，比如我的機器配置足夠好。可以設定多個sub pool來，每個sub pool都是都有單獨的free list,和保留區域，但是彼此之間還是透過latch來併發共享。這樣也可以在一定程度上提高shared pool的效能。
11g開始，每個sub pool都為512M
可以透過隱含引數來檢視當前的庫中sub pool的設定。

  1* select a.ksppinm,b.ksppstvl from x$ksppi a,x$ksppsv b where a.indx=b.indx and a.ksppinm='_kghdsidx_count'
SQL> /

KSPPINM              KSPPSTVL
-------------------- ----------------------------------------
_kghdsidx_count      1

我本地的環境配置比較差，目前只有一個sub pool,因為shared_pool的大小是200M，沒有足夠的資源。
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 10M
shared_pool_size                     big integer 200M

關於繫結變數和硬解析
提到shared pool，不得不提繫結變數，線上業務系統中是很關鍵的一個指標。
可以舉一個例子來簡單說明一下。
首先建立一個表test_var，然後傳入兩個變數值,型別不同，看看執行的情況。
create table test_var as select object_id id,object_name name from user_objects where rownum<3;  --只以兩條資料為例子
update test_var set name='aaa' where rownum<2;    --修改一條資料
update test_var set name='bbb' where name!='aaa';  --修改另外一條資料

SQL> alter system flush shared_pool;

System altered.

SQL> variable name varchar2(100);
SQL> exec :name:='aaa';

PL/SQL procedure successfully completed.

SQL> select *from test_var where name=:name;

        ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
     21014 aaa

執行語句之後，檢視sql_id,和hash值，從v$sqlarea中可以檢視version_count，如果發生了硬解析，version_count就會遞增。
SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';

SQL_ID        HASH_VALUE ADDRESS          CHILD_ADDRESS
------------- ---------- ---------------- ----------------
3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850

SQL> col sql_text format a50
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select *from test_var%';

SQL_TEXT                                           VERSION_COUNT
-------------------------------------------------- -------------
select *from test_var where name=:name                         1

然後再來賦另外一個值，看看version_count會不會遞增。

SQL> exec :name:='bbb';

PL/SQL procedure successfully completed.

SQL> select *from test_var where name=:name;

        ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
     15287 bbb

SQL> col sql_text format a50
SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';

SQL_ID        HASH_VALUE ADDRESS          CHILD_ADDRESS
------------- ---------- ---------------- ----------------
3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850

SQL>
SQL> select sql_text,version_count from v$sqlarea where sql_text like 'select *from test_var%';

SQL_TEXT                                           VERSION_COUNT
-------------------------------------------------- -------------
select *from test_var where name=:name                         1

可以看到，沒有任何的變化，說明繫結變數起作用了，沒用再次硬解析。
來改一下資料型別，看看效果。我把變數型別從varchar2改為了char

SQL> variable name char(3);
SQL> exec :name:='aaa';

PL/SQL procedure successfully completed.

SQL> select *from test_var where name=:name;

        ID NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
     21014 aaa

SQL> select sql_text,version_count from v$sqlarea where sql_id='3vm96qwzm0mg2'
  2  /

SQL_TEXT                                           VERSION_COUNT
-------------------------------------------------- -------------
select *from test_var where name=:name                         2

SQL> select sql_id,hash_value,address,child_address from v$sql where sql_text like 'select *from test_var%';

SQL_ID        HASH_VALUE ADDRESS          CHILD_ADDRESS
------------- ---------- ---------------- ----------------
3vm96qwzm0mg2 1060130274 000000006B751178 0000000069764850
3vm96qwzm0mg2 1060130274 000000006B751178 000000006A039F40

不同之處就是child_address，說明走了兩次硬解析。
可以想象如果在繁忙的業務系統中如果大量的sql語句走反覆解析的話，會耗費大量的cpu資源和時間。導致系統效能的下降。