_shared_pool_reserved_pct or shared_pool_reserved_size with ASMM

共享池是Oracle著名的SGA的一個重要組成部分，當我們嘗試從共享池中分配較大的連續區域時(預設來說是4400bytes)，我們可能會用到共享池中的保留區域(也叫保留池);注意Oracle總是會先嚐試掃描普通共享池的空閒列表，之後才嘗試掃描保留池的空閒列表，無論所需分配的記憶體塊是否超過隱式引數_shared_pool_reserved_min_alloc所指定的值。

什麼？你看到過的4031描述文件是用以下虛擬碼描述分配流程的：

large, scan reserved list
if (chunk found)
check chunk size and perhaps truncate
if (chunk is not found)
scan regular free list
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat

small, scan regular free list
if (chunk found)
check chunk size and perhaps truncate
all done
if (chunk is not found)
do LRU operations and repeat

那麼來看看以下測試:

SQL> alter system set "_shared_pool_reserved_pct"=5 scope=spfile;

System altered.

SQL> startup frce;
SP2-0714: invalid combination of STARTUP options
SQL> startup force;
ORACLE instance started.

Total System Global Area 3154116608 bytes
Fixed Size                  2099616 bytes
Variable Size            2197816928 bytes
Database Buffers          939524096 bytes
Redo Buffers               14675968 bytes
Database mounted.
Database opened.
SQL>  select free_space from v$shared_pool_reserved;

FREE_SPACE
----------
3525368

SQL> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
2   FROM SYS.x$ksppi x, SYS.x$ksppcv y
3   WHERE x.inst_id = USERENV ('Instance')
4   AND y.inst_id = USERENV ('Instance')
5   AND x.indx = y.indx
6  AND x.ksppinm LIKE '%_shared_pool_reserved_min_alloc%';

NAME                            VALU DESCRIB
------------------------------- ---- ---------------------------------------------------------------------
_shared_pool_reserved_min_alloc 4400 minimum allocation size in bytes for reserved area of shared pool

SQL> select count(*) from x$ksmsp where ksmchsiz>4400 and ksmchcom!='free memory';

COUNT(*)
----------
64

SQL>  exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.
SQL> select count(*) from x$ksmsp where ksmchsiz>4400 and ksmchcom!='free memory';

COUNT(*)
----------
67                            /* 方才呼叫的儲存過程成功在共享池中分配到3個大於4400 byte的Chunk,接下來看保留池大小變化)
SQL>  select free_space from v$shared_pool_reserved;

FREE_SPACE
----------
3525368               /* 保留池大小沒有發生變化，很顯然3個大於4400 byte的Chunk是從regular free list上獲取的，而非reserved free list/

以上實驗中我們透過呼叫awr快照儲存過程，模擬了從共享池中分配大於4400位元組Chunk的操作，實驗結果是在保留池有足夠空閒空間的情況下，Oracle仍嘗試在普通共享池區域中分配了這些連續記憶體，故而透過查詢內部檢視x$ksmsp雖然發現了多出了三個大於4400 byte的Chunk，然而保留池的空閒量並未減少。由此可證即便是要分配大於4400位元組的記憶體塊，Oracle也會先嚐試搜尋普通空閒列表，在普通空閒列表上無適應尺寸的連續空間時，才會嘗試掃描保留池的空閒列表。

言歸正題，我們可以透過2個引數控制保留池的大小:shared_pool_reserved_size和_shared_pool_reserved_pct。這2個引數的區別在於普通引數shared_pool_reserved_size以數值形式制定保留池的大小，這個數值採用在10g的ASMM(自動管理的SGA記憶體管理)特性的環境中是不會隨共享池的大小變化而浮動的；不同於此，隱式引數_shared_pool_reserved_pct作為一個比例值，可以協同ASMM中共享池的變化而適應變化。在討論經典4031錯誤的數個文件中，都有介紹到如果在ASMM環境中，設定_shared_pool_reserved_pct往往要好過shared_pool_reserved_size，它使你的共享池更具可收縮性！

紙上得來終覺淺，我們來看看_shared_pool_reserved_pct的實際效果：

SQL> alter system set sga_max_size=3000M scope=spfile;

System altered.

SQL> alter system set sga_target=3000M scope=spfile;

System altered.

SQL> alter system set shared_pool_size=500M;

System altered.

SQL> alter system set "_shared_pool_reserved_pct"=50 scope=spfile;

System altered.

SQL> startup force ;
ORACLE instance started.

Total System Global Area 3154116608 bytes
Fixed Size                  2099616 bytes
Variable Size             570426976 bytes
Database Buffers         2566914048 bytes
Redo Buffers               14675968 bytes
Database mounted.
Database opened.
SQL> select free_space from v$shared_pool_reserved;

FREE_SPACE
----------
21158280
SQL> alter system set shared_pool_size=2000M ;  /*ASMM下手動修改shared_pool_size,模擬共享池自動擴充套件的情況*/

System altered.

SQL> select free_space from v$shared_pool_reserved;

FREE_SPACE
----------
21158280                          /*  ohhh!好像跟我們預期的差別挺大,保留池大小沒變*/

讓我們跑下這段產生反覆硬解析的SQL:

begin
for i in 1..200000 loop
execute immediate 'select 2 from dual where 1='||i;
end loop;
end;
/

SQL> select free_space from v$shared_pool_reserved;

FREE_SPACE
----------
296215920                                          /* 這樣好了，我們如願了，SGA真"動態" /

SQL>  alter system set shared_pool_size=300M;   /*嘗試收縮ASMM下的共享池*/

System altered.

SQL>  alter system flush shared_pool;

System altered.

SQL> select free_space from v$shared_pool_reserved;

FREE_SPACE
----------
296215920                           /* 我們甚至無法flush 掉這些記憶體,這挺要命的 /

SQL> select name ,value/1024/1024 "SIZE MB" from v$system_parameter where name in ('sga_target','sga_max_size','shared_pool_size','db_cache_size','java_pool_size','large_pool_size','db_keep_cache_size');

NAME                    SIZE MB
-------------------- ----------
sga_max_size               3008
shared_pool_size            304
large_pool_size              16
java_pool_size               16
sga_target                 3008
db_cache_size               512
db_keep_cache_size            0

可以看到我們還有很多“沒有分配”的SGA記憶體，我們來加大快取記憶體看看:
SQL> alter system set db_cache_size=1000M;
alter system set db_cache_size=1000M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache      /* ohh 因為無法回收保留池的大量記憶體，導致了SGA其他元件無法擴充套件/

_shared_pool_reserved_pct的預設值5%可以滿足絕大多數情況，透過上述實驗證明設定該percent引數可以使保留池大小隨SGA動態調整而擴大；但透過再次調整shared_pool_size和flush shared_pool手段都無法回收過度分配的保留池空間，這會導致其他元件無法正常擴充套件；因而我們在10gASMM的背景下，透過設定_shared_pool_reserved_pct可以獲得更好的效果，但因為存在回收空間的問題，該引數也不宜設定過大，如果預設值在您的場景中顯得過小，那麼您可以嘗試使用5-20這個區間內的值，超過20%的話往往就會造成負面的影響了。