[AD]共享池的ORA-04031错误

一，什么是ORA-04031错误
1,SharedPool的根本问题只有一个，就是碎片过多带来的性能影响。当尝试在共享池分配大块的连续内存失败（很多时候是由于碎片过多，而且非真是内存不足）时，Oracle首先清除共享池中当 前没有使用的所有对象，是空闲内存块合并。如果仍然没有足够大的单块内存可以满足需要，就会产生ORA-04031错误。
2,SharedPool的内存算法相当复杂，ORA-04031错误出现的原因也众多，经过简化，可以通过以下伪代码来描述ORA-04031错误产生。
Scan free lists                                     --扫描Free　lists
if((request size of RESERVED Pool size))            --如果请求RESERVED POOL空间
scan reserved list                                  --扫描保留列表
if(chunk found)                                     --如果发现满足条件的内存块
chunk chunk size and perhaps truncate               --检查大小，可能需要分割
return                                              --返回
do LRU operation for n objects                      --如果并非请求RESERVED POOL或不能发现足够内存
scan free lists                                     --则转而执行LRU操作，释放内存，重新扫描
if(request sizes exceeds reserved pool min alloc)   --如果请求大于_shared_pool_reserved_min_alloc
scan reserved list                                  --扫描保留列表
if(chunk found)                                     --如果发现满足条件的内存块
check chunk size and perhaps truncate               --检查大小，可能需要分割
return                                              --在Freelist或reserved list找到则成功返回
signal ORA-04031 error                              --否则报告ORA-04031错误
二，绑定变量和Cursor_sharing
1，如果shared_pool_size设置足够大，可以排除Bug的因素，那么大多数的ORA-04031错误都是由共享池中的大量SQL代码导致了过多的碎片而引起的，可能的主要原因有：
a，SQL没有足够的共享
b，大量不必要的解析调用
c，没有使用绑定变量
2，实际上应用的编写和调整始终是最重要的内容，SharedPool的调整根本上要从应用入手。使用绑定变量可以充分降低SharedPool和Library Cache的Latch竞争，从而提高性能。
3，如果用户的应用没有很好地使用绑定变量，那么Oracle从8.1.6开始提供了一个新的初始化参数用以在Server端进行强制变量绑定，这个参数是：cursor_sharing。
4，最初这个参数有两个可选项：exact和force。缺省的是exact，表示精确匹配；force表示在Server端强制绑定。在8i的版本里使用这个参数对某些应用带来了极大的性能提高，但是同时也存在一些副作用，比如优化器无法生成精确的执行计划，SQL执行计划发生改变等（所以如果启用cursor_sharing参数时，一定要确定应用在此模式下经过充分的测试）。
5，从Oracle 9i开始，Oracle引入了绑定变量Peeking的机制，SQL在第一次执行时，首先在session的PGA中使用具体指生成精确的执行计划，以期提高执行计划的准确性。然而Peeking的方式只在第一次硬解析时生效，所以仍然可能存在问题，导致后续的SQL错误的执行。
6，同时在Oracle 9i中，cursor_sharing参数有了第三个选项：similar。该参数指定Oracle在存在柱状图信息时，对于不同的变量值重新解析，从而可以利用柱状图更为精确地制定SQL执行计划。即当存在柱状图信息时，similar的表现和exact相同；当柱状图不存在时，similar的表现和force相同。
三，使用Flush Shared Pool缓存共享池问题
1，前面提到，本质上ORA-04031错误多数是由于SQL编写不当引起，所以如果能够修改应用绑定变量是最好的解决之道。当然如果不能修改应用，或者不能强制变量绑定，那么Oracle还可以提供一种应急处理办法，强制刷新共享池。
SQL> alter system flush shared_pool;
System altered.
SQL>
2，刷新共享池可以帮助合并碎片（small chunks），强制老化SQL，释放共享池，但是这通常是不推荐的做法，因为：
a，Flush Shared Pool会导致当前未使用的cursor被清除出共享池，如果这些SQL随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的CPU使用，数据库将会产生激烈的Latch竞争。
b，如果没有使用绑定变量，大量类似的SQL不停执行，那么Flush Shared Pool可能只能带来短暂的改善，数据库很快就会回到原来的状态。
c，如果SharedPool很大，并且系统非常繁忙，刷新SharedPool可能会导致系统挂起，对于类似系统尽量在系统空闲时进行。
3，从Oracle 9i开始，Oracle共享池算法发生了很大的改变，Flush Shared Pool的方法不再推荐使用。
四，SHARED_POOL_RESERVED_SIZE参数的设置及作用
1，shared_pool_reserved_size该参数指定了保留的共享池空间，用于满足大的连续的共享池空间请求。当共享池出现碎片，请求大块空间会导致Oracle大范围地查找并释放共享池内存来满足请求，由此可能会带来较为严重的性能下降，设置合适的shared_pool_reserved_size参数，结合shared_pool_reserved_min_alloc参数可以避免由此导致的性能下降。
2，这个参数理想值应该大到足以满足任何对RESERVED LIST的内存请求，而无需数据库从共享池中刷新对象。这个对象的缺省值是shared_pool_size 的5%，通常这个参数的建议值为shared_pool_size 参数的10%~20%大小，最大不得超过shared_poo_size 的50%。
3，同样在trace文件中，可以找到保留类表（RESERVED LIST）的内存信息。
RESERVED FREE LISTS:
 Reserved bucket 0 size=16
 Reserved bucket 1 size=4400
 Reserved bucket 2 size=8204
 Reserved bucket 3 size=8460
 Reserved bucket 4 size=8464
 Reserved bucket 5 size=8468
 Reserved bucket 6 size=8472
 Reserved bucket 7 size=9296
 Reserved bucket 8 size=9300
 Reserved bucket 9 size=12320
 Reserved bucket 10 size=12324
 Reserved bucket 11 size=16396
 Reserved bucket 12 size=32780
 Reserved bucket 13 size=65548
  Chunk 30400050 sz=   212888  R-free      "               "
  Chunk 31400050 sz=   212888  R-free      "               "
  Chunk 32800050 sz=   212888  R-free      "               "
Total reserved free space   =   638664
UNPINNED RECREATABLE CHUNKS (lru first):
SEPARATOR
Unpinned space     =        0  rcr=0 trn=0
PERMANENT CHUNKS:
  Chunk 30434000 sz=  3981312    perm      "perm           "  alo=3239108
  Chunk 32bb84e4 sz=   292880    perm      "perm           "  alo=292880
  Chunk 31434000 sz=  3980376    perm      "perm           "  alo=3980376
  Chunk 32834000 sz=  3687652    perm      "perm           "  alo=3687652
Permanent space    = 11942220
4，shared_pool_reserved_min_alloc这个参数的值控制保留内存的使用和分配。如果在共享池空闲列表中请求一个足够尺寸的大块内存，但是没有找到合适的空间，内存就从保留列表（RESERVED LIST）中分配一块比这个参数值大的空间。
5，SQL> select
  2  x.ksppinm name,
  3  y.ksppstvl value,
  4  y.ksppstdf isdefault,
  5  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MODE','FALSE') ismod,
  6  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
  7  from
  8  sys.x$ksppi x,
  9  sys.x$ksppcv y
 10  where
 11  x.inst_id = userenv('Instance') and
 12  y.inst_id = userenv('Instance') and
 13  x.indx = y.indx and
 14  x.ksppinm like '%_&par%'
 15  order by
 16  translate(x.ksppinm,'_','')
 17  /
Enter value for par: shared_pool_reserved_min_alloc
old  14: x.ksppinm like '%_&par%'
new  14: x.ksppinm like '%_shared_pool_reserved_min_alloc%'
NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
ISDEFAULT ISMOD       ISADJ
--------- ----------- -----
_shared_pool_reserved_min_alloc
4400
TRUE      FALSE       FALSE
SQL>

6，这段代码可以查询所有的隐含参数这个参数默认的值对于大多数系统来说都足够了。如果系统经常出现的ORA-04031错误都是请求大于4400的内存块，那么久可能需要增加shared_pool_reserved_size参数设置。
7，如果主要的引发LRU合并、老化出现ORA-04031错误的内存请求在4100~4400byte之间，那么降低_shared_pool_min_alloc同时适当增大shared_pool_reserved_size参数值通常会有所帮助。设置
_shared_pool_min_alloc=4100可以增加SharedPool成功满足请求的概率。
8，需要注意的是，这个参数的修改应当结合SharedPool和SharedPoolReservedSize的大小。设置_shared_pool_reserved_min_alloc=4100是经过证明的可靠方式，不建议设置更低。
9，查询v$shared_pool_reserved视图可以判断共享池问题的引发原因：
SQL> SELECT free_space,avg_free_size,used_space,
  2  avg_used_size,request_failures,last_failure_size
  3  FROM v$shared_pool_reserved;
FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
   3619096        212888          0             0                0                 0
SQL>
10，如果request_failures>0且last_failure_size>shared_pool_reserved_min_alloc,那么ORA-04031错误就可能是因为共享池保留空间缺少连续空间所致。要解决这个问题，可以考虑加大

shared_pool_reserved_min_alloc来降低缓冲进共享池保留空间的对象数目，并增大shared_pool_reserved_size和shared_pool_size来加大共享池保留空间的可用内存。
11，如果request_failures>0且last_failure_size少连续空间而导致了ORA-04031错误。对于这一类情况应该考虑降低shared_pool_reserved_min_alloc，以放入更多的对象到共享池保留空间中并增大shared_pool_size。
四，导致ORA-04031的其他原因
1，此外，某些特定的SQL，较大的指针或者的package都可能导致ORA-04031错误。在很多的ERP软件中，这样的情况非常常见。在这种情况下，可以考虑把这个大的对象pin到共享池中，减少其动态请求、分配所带来的负担。
2，使用DBMS_SHARED_POOL.KEEP系统包可以把这些对象pin到内存中，SYS.STANDARD、SYS.DBMS_STANDARD等都是常见的候选对象。
3，要使用DBMS_SHARED_POOL系统包，首先需要运行dbmspool.sql脚本，该脚本会自动调用prvpool.plb脚本创建所需对象。
4，引发ORA-4031错误的因素还有很多，通过设置相关参数如session_cached_cursors、cursor_space_for_time等也可以解决一些性能问题并带来针对性的性能改善。
5，SQL> select * from v$sgastat where name in ('miscellaneous','free memory') and pool='shared pool';
POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  free memory                  16659984

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29611940/viewspace-1147569/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29611940/viewspace-1147569/