buffercache和sharedpool详解（之五，问题诊断总结）

【深入解析--eygle】 学习笔记 1.2.7 诊断和解决ORA-04031 错误 S hared Pool的主要问题在根本上只有一个，就是碎片过多带来的性能影响 。 1.2.7.1 什么是ORA-04031错误 当尝试在共享池分配大块的连续内存失败（很多时候是由于碎片过多，而并非真是内存不足

【深入解析--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$sqlarea

WHERE sql_text LIKE 'insert into felix%';

SQL_TEXT VERSION_COUNTPARSE_CALLS EXECUTIONS

-------------------------------- ------------------------ ----------

insert into felix values(9) 1 1 1

insert into felix values(5) 1 1 1

insert into felix values(8) 1 1 1

insert into felix values(1) 1 1 1

insert into felix values(4) 1 1 1

insert into felix values(6) 1 1 1

insert into felix values(3) 1 1 1

insert into felix values(7) 1 1 1

insert into felix values(2) 1 1 1

insert into felix values(10) 1 1 1

10 rows selected.

重构测试表，进行第二次测试：

scott@felix SQL>drop table felix purge;

scott@felix SQL>create table felix (id number);

begin

for i in1..10 loop

executeimmediate 'insert into felix values(:v1)' using i;

end loop;

commit;

end;

/

对于该SQL，在共享池中只存在一份，解析一次，执行10次，这就是绑定变量的优势所在：

SELECT sql_text, version_count, parse_calls,executions

FROMv$sqlarea

WHEREsql_text LIKE 'insert into felix%';

SQL_TEXT VERSION_COUNT PARSE_CALLSEXECUTIONS

-------------------------------- ------------------------ ----------

insert into felix values(:v1) 1 1 1

在应用程序开发的过程中，都应该优先考虑使用绑定变量（在JAVA应用中可以使用PreparedStatement进行变量绑定），但是如果应用没有很好地使用绑定变量，那么Oracle从8.1.6开始提供了一个新的初始化参数用以在Server 端进行强制变量绑定，这个参数就是cursor_sharing。最初这个参数有两个可选设置：exact和force。

缺省值是exact，表示精确匹配；force表示在Server端执行强制绑定。在8i的版本里使用这个参数对某些应用可以带来极大的性能提高，但是同时也存在一些副作用，比如优化器无法生成精确的执行计划，SQL执行计划发生改变等（所以如果启用cursor_sharing参数时，一定确认用户的应用在此模式下经过充分的测试）。

从Oracle 9i开始，Oracle引入了绑定变量Peeking的机制，SQL在第一次执行时，首先在Session的PGA中使用具体值生成精确的执行计划，以期可以提高执行计划的准确性，然而Peeking的方式只在第一次硬解析时生效，所以仍然可能存在问题，导致后续的SQL错误的执行；同时，在Oracle 9i中，cursor_sharing参数有了第3个选项：similar。该参数指定Oracle在存在柱状图信息时，对于不同的变量值，重新解析，从而可以利用柱状图更为精确地制定SQL执行计划。也即当存在柱状图信息时，similar的表现和exact相同；当柱状图信息不存在时，similar的表现和force相同。

除了Bug之外，在正常情况下，由于Similar的判断机制，可能也会导致SQL无法共享。在收集了柱状图（Hisogram）信息之后，如果SQL未使用绑定变量，当SQL使用具备柱状图信息的Column时，数据库会认为SQL传递过来的每个常量都是不可靠的，需要为每个SQL生成一个Cursor，这种情况被称为UNSAFE BINDS。大量的Version_Count可能会导致数据库产生大量的cursor: pin S wait on X等待。解决这类问题，可以设置CURSOR_SHARING为Force或者删除相应字段上的柱状图信息。

1.2.7.3 使用Flush Shared Pool缓解共享池问题

一种应急处理方法，强制刷新共享池。

alter system flushshared_pool;

刷新共享池可以帮助合并碎片（smallchunks）， 强 制 老 化SQL，释放共享池，但是这通常是不推荐的做法，这是因为：

（1）Flush Shared Pool会导致当前未使用的cursor被清除出共享池，如果这些SQL随后需要执行，那么数据库将经历大量的硬解析，系统将会经历严重的CPU争用，数据库将会产生激烈的Latch竞争。

（2）如果应用没有使用绑定变量，大量类似SQL不停执行，那么Flush Shared Pool可能只能带来短暂的改善，数据库很快就会回到原来的状态。

（3）如果Shared Pool很大，并且系统非常繁忙，刷新Shared Pool可能会导致系统挂起，对于类似系统尽量在系统空闲时进行。

1.2.7.4 SHARED_POOL_RESERVED_SIZE参数的设置及作用

shared_pool_reserved_size，该参数指定了保留的共享池空间，用于满足将来的大的连续的共享池空间请求。当共享池出现过多碎片，请求大块空间会导致Oracle 大范围的查找并释放共享池内存来满足请求，由此可能会带来较为严重的性能下降，设置合适的shared_pool_reserved_size参数，结合shared_pool_reserved_min_alloc参数可以用来避免由此导致的性能下降。

这个参数理想值应该大到足以满足任何对RESERVED LIST的内存请求，而无需数据库从共享池中刷新对象。这个参数的缺省值是shared_pool_size 的5%，通常这个参数的建议值为shared_pool_size参数的10%～20%大小，最大不得超过shared_pool_size的50%。

shared_pool_reserved_min_alloc这个参数的值控制保留内存的使用和分配。如果一个足够尺寸的大块内存请求在共享池空闲列表中没能找到，内存就从保留列表（RESERVED LIST）中分配一块比这个值大的空间。

如果你的系统经常出现的ORA-04031错误都是请求大于4400的内存块，那么就可能需要增加shared_pool_reserved_size参数设置。

而如果主要的引发LRU合并、老化并出现04031错误的内存请求在4100～4400byte之间，那么降低_shared_pool_reserved_min_alloc 同时适当增大SHARED_POOL_RESERVED_SIZE参数值通常会有所帮助。设置_shared_pool_reserved_min_alloc=4100可以增加Shared Pool成功满足请求的概率。需要注意的是，这个参数的修改应当结合Shared Pool Size和Shared Pool Reserved Size的修改。设置_shared_pool_reserved_min_alloc=4100是经过证明的可靠方式，不建议设置更低。

查询v$shared_pool_reserved视图可以用于判断共享池问题的引发原因：

16:26:38 sys@felix SQL>S SELECT free_space,

avg_free_size,

used_space,

avg_used_size,

request_failures,

last_failure_size

FROMv$shared_pool_reserved;

FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZEREQUEST_FAILURES LAST_FAILURE_SIZE

---------- ------------- ---------- ----------------------------- -----------------

7255512 196094.919 8155392 220416 0 0

17:04:04 sys@felix SQL>

如果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来加大共享池保留空间的可用内存。

如果request_failures>0 并且last_failure_size

1.2.7.5 其他

此外，某些特定的SQL，较大的指针或者大的Package都可能导致ORA-04031错误。在很多ERP软件中，这样的情况非常常见。在这种情况下，可以考虑把这个大的对象Pin 到共享池中，减少其动态请求、分配所带来的负担。

使用dbms_shared_pool.keep 系统包可以把这些对象pin 到内存中，最常见的SYS.STANDARD、SYS.DBMS_STANDARD等都是常见的候选对象。

注意：要使用DBMS_SHARED_POOL系统包，首先需要运行dbmspool.sql脚本，该脚本会自动调用prvtpool.plb 脚本创建所需对象。

引发ORA-04031 错误的因素还有很多，通过设置相关参数如session_cached_cursors、cursor_space_for_time等也可以解决一些性能问题并带来针对性的性能改善，这里不再过多讨论。

1.2.8 Library Cache Pin 及Library Cache Lock分析

Oracle使用两种数据结构来进行Library Cache的并发访问控制：lock 和 pin。

Lock可以被认为是解析锁，而Pin则可以被认为是以读取或改变对象内容为目的所加的短时锁。之所以将Library Cache Object对象分开，使用多个锁定来保护，其中的一个重要目的就是为了提高并发。

Lock比Pin具有更高的级别。Lock在对象handle上获得，在pin一个对象之前，必须首先获得该handle的锁定。Handle可以理解为Libray Cache对象的Buffer Header，其中包含了库缓存对象的名称、标记、指向具体对象的内存地址指针等信息。

再次引用一下前文曾经提到的图表，通过下图我们可以清晰的看到Object Handles和Heaps的关系：

锁定主要有三种模式: Null，share，Exclusive。在读取访问对象时,通常需要获取Null(空)模式以及share(共享)模式的锁定。在修改对象时,需要获得Exclusive(排他)锁定。Library Cache Lock根本作用就是控制多个Oracle客户端对同一个Library Cache对象的并发访问，通过对Library Cache Object Hadle上加锁来防止非兼容的访问。

常见的使用或保护包括：

1. 一个客户端防止其他客户端访问同一对象

2. 一个客户端可以通过锁定维持相对长时间的依赖性（例如，防止其他客户端修改对象）

3. 当在Library Cache中定位对象时也需要获得这个锁定

在锁定了Library Cache对象以后，一个进程在访问之前必须pin该对象。同样pin有三种模式，Null,shared和exclusive。只读模式时获得共享pin,修改模式获得排他pin。通常我们访问、执行过程、Package时获得的都是共享pin，如果排他pin被持有,那么数据库此时就要产生等待。

为了实现更好的性能，从Oracle10gR2开始，Library Cache Pin已经逐渐被互斥机制（Mutex）所取代，在Oracle Database 11g中，这个变化就更为明显。

1.2.8.1 LIBRARY CACHE PIN等待事件

library cache pin是用来管理library cache的并发访问的，pin一个Object会引起相应的heap被载入内存中（如果此前没有被加载），pins可以在Null、Share、Exclusive这3个模式下获得，可以认为pin是一种特定形式的锁。

当library cache pin等待事件出现时，通常说明该pin被其他用户已非兼容模式持有。library cache pin的等待时间为3秒钟，其中有1秒钟用于PMON后台进程，即在取得pin之前最多等待3秒钟，否则就超时。

ibrary cache pin的参数有P1（KGL Handle Address）、P2（Pin Address）和P3（Encoded Mode & Namespace）， 常用的主要是P1和P2

library cache pin通常是发生在编译或重新编译PL/SQL、VIEW、TYPES等Object时。

当Object变得无效时，Oracle会在第一次访问此Object时试图去重新编译它，如果此时其他session已经把此Object pin到library cache 中，就会出现问题，特别时当有大量的活动session并且存在较复杂的dependence时。在某种情况下，重新编译Object可能会花几个小时时间，从而阻塞其他试图去访问此Object的进程。

recompile过程包含以下步骤：

（1）存储过程的library cache object以排他模式被锁定，这个锁定是在handle上获得的。Exclusive 锁定可以防止其他用户执行同样的操作，同时防止其他用户创建新的引用此过程的对象。

（2）以Shared模式pin该对象，以执行安全和错误检查。

（3）共享pin被释放，重新以排他模式pin该对象，执行重编译。

（4）使所有依赖该过程的对象失效。

（5）释放Exclusive Lock和Exclusive Pin。

从Oracle 10g开始，以上测试将不会看到同样的效果，这是因为Oracle 10g对于对象编译与重建做出了增强。注意当重新replace一个过程时，Oracle会首先执行检查，如果代码前后完全相同，则replace工作并不会真正进行（因为没有变化），对象的LAST_DDL_TIME不会改变，这就意味着Latch的竞争可以减少。

对于version_count过高的问题，可以查询V$SQL_SHARED_CURSOR视图，这个视图会给出SQL不能共享的具体原因，如果是正常因素导致的，相应的字段会被标记为“Y”；对于异常的情况（如本案例），查询结果可能显示的都是“N”，这就表明Oracle认为这种行为是正常的，在当前系统设置下，这些SQL不应该被共享，那么可以判断是某个参数设置引起的。和SQL共享关系最大的一个初始化参数就是cursor_sharing，在这个案例中cursor_sharing参数被设置为similar，正是这个设置导致了大量子指针不能共享。

1.2.9 V$SQL与V$SQLAREA视图

在前面提到过一个经常被问及的问题：V$SQL与V$SQLAREA两个视图有什么不同？所以有这样一个问题是因为这两个视图在结构上非常相似。

V$SQLAREA和V$SQL两个视图的不同之处在于，V$SQL中为每一条SQL保留一个条目，而V$SQLAREA中根据SQL_TEXT进行GROUP BY，通过version_count计算子指针的个数。