_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%的话往往就会造成负面的影响了。