【深入解析--eygle】 学习笔记
1.2.7 诊断和解决ORA-04031 错误?
Shared Pool的主要问题在根本上只有一个，就是碎片过多带来的性能影响。?
1.2.7.1 什么是ORA-04031错误
当尝试在共享池分配大块的连续内存失败（很多时候是由于碎片过多，而并非真是内存不足）时，Oracle首先清除共享池中当前没使用的所有对象，使空闲内存块合并。如果仍然没有足够大的单块内存可以满足需要，就会产生ORA-04031错误。?
如下一段伪代码来描述04031错误的产生：?
Scan free lists --扫描Free Lists
if (request size of RESERVED Pool size) --如果请求RESERVED POOL空间
scan reserved list --扫描保留列表
if (chunk found) --如果发现满足条件的内存块
check 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或reservedlist找到则成功返回
signal ORA-4031 error --否则报告ORA-04031错误。
[oracle@felix ~]$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes ofshared memory(\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory,either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
[oracle@felix ~]$
1.2.7.2 绑定变量和cursor_sharing
如果SHARED_POOL_SIZE设置得足够大，又可以排除Bug的因素，那么大多数的ORA-04031错误都是由共享池中的大量的SQL代码等导致过多内存碎片引起的。
可能的主要原因有：
（1）SQL没有足够的共享；
（2）大量不必要的解析调用；
（3）没有使用绑定变量。
实际上说，应用的编写和调整始终是最重要的内容，Shared Pool的调整根本上要从应用入手。根本上，使用绑定变量可以充分降低Shared Pool和Library Cache的Latch竞争，从而提高性能。
反复的SQL硬解析不仅会消耗大量的CPU资源，也会占用更多的内存，严重影响数据库的性能，而使用绑定变量则可以使SQL充分共享，实现SQL的软解析，提高系统性能。
（1）创建表病记录解析统计记录：
15:46:52 scott@felixSQL>create table felix (id number);
Table created.
15:47:48 scott@felix SQL>SELECT NAME,VALUE FROMV$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC# AND NAME LIKE 'parse%';
NAME VALUE
----------------------------------------------------
parse time cpu 28
parse time elapsed 82
parse count (total) 294
parse count (hard) 180
parse count (failures) 0
parse count (describe) 0?
6 rows selected.?
15:54:32 scott@felix SQL>
?（2）进行循环插入数据，以下代码并未使用绑定变量：?
felix SQL> begin
for i in 1..10 loop
execute immediate 'insert into felixvalues('||i||')';
end loop;
commit;
end;
/?
PL/SQL procedure successfully completed.?
（3）完成之后检查统计信息，注意硬解析次数增加了10次，也就是说每次INSERT操作都需要进行一次独立的解析：
16:02:22 scott@felix SQL>SELECT NAME,VALUE FROMV$MYSTAT A,V$STATNAME B WHERE A.STATISTIC#=B.STATISTIC# AND NAME LIKE 'parse%';?
NAME VALUE
----------------------------------------------------
parse time cpu 32
parse time elapsed 89
parse count (total) 336
parse count (hard) 190
parse count (failures) 1
parse count (describe) 0?
6 rows selected.?
16:02:29 scott@felix SQL>?
查询V$SQLAREA视图，可以找到这些不能共享的SQL，注 意 每 条SQL都只执行了一次，这些SQL不仅解析要消耗密集的SQL资源，也要占用共享内存存储这些不同的SQL代码：
SELECT sql_text, version_count, parse_calls, executions
FROM v$sqlare