oracle_Shared Pool优化和Library Cache Latch冲突优化

简介

本文档旨在介绍从Oracle 7到Oracle 11g shared pool调优的关键问题。特别对于存在下列问题的系统非常重要：

• library cache latch/es或者latch:library cache之类的 Latch争用
• shared pool latch 或者 latch:shared pool 之类的Latch争用
• 高CPU解析时间
• V$LIBRARYCACHE 中的高reloads
• 多版本的cursors
• 大量的parse call
• 经常发生ORA-04031 错误

Troubleshooting Steps

什么是shared pool？

Oracle 在SGA的一个特定区域中保留SQL语句, packages, 对象信息以及其它一些内容，这就是大家熟悉的shared pool。这个共享内存区域是由一个复杂的cache和heap manager 构成的。它需要解决三个基本问题：

1. 每次分配的内存大小是不一致的，从几个字节到上千个字节；
2. 因为shared pool的目的是为了最大化共享信息，所以不是每次一个用户用完之后就可以释放这段内存（在传统的heap manager方式会遇到这个问题）。内存中的信息可能对于其他session来说是有用的——Oracle并不能事先知道这些内容是否会被再次用到；
3. Shared pool中的内容不能被写入到硬盘区域中，这一点和传统cache是不一样的。只有“可重建”的信息可以被覆盖，因为他们可以在下次需要时重建。

基于这些背景，我们就可以理解shared pool的管理是一件非常复杂的事情。下面的章节列出了一些影响shared pool性能和它相关的latch的关键问题，包括：

专用术语

Literal SQL

一个Literal SQL语句是指在predicate中使用具体值，而不是使用绑定变量,即不同的执行语句使用的具体值可能是不一样的。
例1：应用程序使用了：

  SELECT * FROM emp WHERE ename='CLARK';

而不是:

  SELECT * FROM emp WHERE ename=:bind1;

例2: 以下语句不用绑定变量但是也不会被认为是literal SQL，因为这个语句可以被多次执行共享。

  SELECT sysdate FROM dual;

例 3: 如果整个应用都是用相同的值'2.0'来检查'version'的话，那么这个语句可以被认为是可以共享的。

  SELECT version  FROM app_version WHERE version>2.0;

Hard Parse（硬解析）

如果一个新的SQL被发起，但是又不在shared pool里面的话，它将被完整的解析一次。例如：Oracle必须在shared pool中分配内存，检查句法和语义等等……这被称为hard parse，它在CPU使用和latch获取上的都是非常消耗资源的。

Soft Parse（软解析）

如果一个session发起一个已经在shared pool中的SQL语句并且它可以使用一个当前存在的版本，那么这个过程被称为一个'soft parse'。对于应用来说，它只需请求解析这个语句。

完全相同的语句?

如果两个SQL语句的含义相同但是没有使用相同的字符，那么Oracle认为它们是不同的语句。比如SCOTT在一个Session中提交的这两个语句：

        SELECT ENAME from EMP;

        SELECT ename from emp;

尽管它们实际上是相同的，但是因为大写字母‘E’和小写字母'e'的区别，他们不会被认为是完全相同的语句。 

Sharable SQL

如果是两个不同的session发起了完全相同的SQL语句，这也不意味着这个语句是可以共享的。比如说：用户SCOTT下有一个表EMP，发起了下面的语句：

SELECT ENAME from EMP;

用户FRED 有一个自己的表也叫EMP并且发起相同的语句:

SELECT ENAME from EMP;

尽管语句完全一样但是由于需要访问的EMP表是不同的对象，所以需要对这条语句产生不同的版本。有很多条件来判断两个完全一致的SQL文本是不是真的是完全相同（以至于他们可以被共享），包括：

• 语句中引用的所有的对象名必须都被解析成实际相同的对象
• 发起语句的session中的optimizer相关的参数应该一致
• 绑定变量的类型和长度应该是"相似的"
  (这里不做详细讨论，但是类型和长度的不同确实会导致语句被分为不同的版本)
• 发起语句的NLS (National Language Support)设置必须相同

语句的版本

正如之前在'Sharable SQL'中描述的，如果两个语句字面上完全相同但是又不能被共享，则会对相同的语句产生不同的'version'，即版本。如果Oracle要匹配一个包含多个版本的语句，它将不得不检查每一个版本来看它们是不是和当前被解析的语句完全相同。所以最好用以下方法来避免高版本数（high version count）：

• 客户端使用的绑定变量最大长度需标准化
• 如果有大量的schema会包含相同名字的对象，那么避免使用一个相同的SQL语句。比如： SELECT xx FROM MYTABLE; 并且每个用户都有一个自己的 MYTABLE 的情况
• 在Oracle 8.1可以将 _SQLEXEC_PROGRESSION_COST 设置成'0'

Library Cache和Shared Pool latches

shared pool latch是用来保护从shared pool中分配和释放内存的关键性操作。

Library cache latche(以及Oracle 7.1中的library cache pin latch)是用来保护library cache 中的操作。

所有的这些Latch都是潜在的资源争用的对象，latch gets发生的次数直接受到shared pool中活动（activity）个数的影响，特别是parse操作。任何减少latch gets或者shared pool中活动（activity）个数的尝试都有助于提高性能和可扩展性。 

Literal SQL和Shared SQL的比较

这一个小章节中描述了literal SQL和sharable SQL各自的优点：

Literal SQL

在有完整的统计信息并且SQL语句在predicate（限定条件）中使用具体值时，基于成本的优化器 (CBO)能工作的最好。比较下面的语句：

SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0;

和

SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA;

对于第一个语句，CBO可以使用已经收集的histogram来判断是否使用全表扫描比使用TOTAL_COST列上索引扫描快（假设有索引的话）。第二个语句CBO并不知道绑定变量":bindA"对应行数的比例，因为该绑定变量没有一个具体的值以确定执行计划。例：":bindA" 可以是 0.0或者99999999999999999.9。

Orders表上两个不同的执行路径的响应时间可能会不同，所以当你需要CBO为你选出最好的执行计划的时候，选用使用literal语句会更好。在一个典型的Decision Support Systems（决策支持系统）中，重复执行'标准'语句的时候非常少，所以共享一个语句的几率很小，而且花在Parse上的CPU时间只占每个语句执行时间的非常小一部分，所以更重要的是给optimizer尽可能详细的信息，而不是缩短解析时间。 

Sharable SQL

如果应用使用了literal (无共享) SQL，则会严重限制可扩展性和生产能力。在对CPU的需求、library cache 和 shared pool latch的获取和释放次数方面，新SQL语句的parse成本很高。

比如：仅仅parse一个简单的语句就可能需要获取和释放library cache latch 20或者30次。

除非它是一个临时的或者不常用的SQL，并且需要让CBO得到尽可能多的信息来生成一个好的执行计划，否则最好让所有的SQL是共享的。 

减轻Shared Pool负载

Parse一次并执行多次

在OLTP类型的应用中，最好的方法是只让一个语句被解析一次，然后保持这个cursor的打开状态，在需要的时候重复执行它。这样做的结果是每个语句只被Parse了一次（不管是soft parse还是hard parse）。显然，总会有些语句很少被执行，所以作为一个打开的cursor维护它们是一种浪费。

请注意一个session最多只能使用参数:open_cursors定义的cursor数，保持cursor打开会增加总体open cursors的数量。

OCI中开发者能直接控制cursor,在预编译器中，HOLD_CURSOR参数控制cursor是否被保持打开。 

消除 Literal SQL

如果你有一个现有的应用程序，你可能没法消除所有的literal SQL，但是你还是得设法消除其中一部分会产生问题的语句。从V$SQLAREA视图可能找到适合转为使用绑定变量的语句。下面的查询列出SGA中有大量相似语句的SQL：

SELECT substr(sql_text,1,40) "SQL",
         count(*) ,
         sum(executions) "TotExecs"
    FROM v$sqlarea
   WHERE executions < 5
   GROUP BY substr(sql_text,1,40)
  HAVING count(*) > 30
   ORDER BY 2
  ;

在10g以上的版本可以用下面的语句:

SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC

 

对SQL语句，去掉重复的空格（不包括字符常量），将大小 写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相同的。对 SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么 SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果SQL中有绑定变 量，force_matching_signature就会与exact_matching_signature一样的生成标准。

可以看到，现在exact_matching_signature与force_matching_signature完全一样了。
从force_matching_signature的特性，我们可以想到一个用途，用于查找没有使用绑定变量的SQL语句，类似于使用plan_hash_value来查找。

注意：如果系统中有library cache latch争用的问题，上面的语句会导致争用加剧。

值40，5和30只是示例，这个查询查找前40个字符相同的，只被执行过很少次数，而又至少在shared pool里出现30次的语句。通常来说，literal语句以下面的形式开始，并且每个语句的前面部分字符是相同的：

"SELECT col1,col2,col3 FROM table WHERE ..."

 

注意：在转化literal SQL使用绑定变量时有一定程度的限制。请放心我们已经反复证明转化那些经常执行的语句会消除shared pool的问题并且能显著提高可扩展性。

请查看你的应用中使用的工具的文档来决定如何在语句中使用绑定变量。 

避免 Invalidations

有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上下文环境。它包括TRUNCATE, 表或索引上的ANALYZE或 DBMS_STATS.GATHER_XXX，关联对象的权限变更。相对应的cursor会留在SQLAREA中，但是下次被引用时会被完全reload并重新parse，所以会对数据库的整体性能造成影响。

下面的查询可以帮我们找到Invalidation较多的cursor:

SELECT SUBSTR(sql_text, 1, 40) "SQL",
invalidations
FROM v$sqlarea
ORDER BY invalidations DESC;

更多的详细信息，请参考 Note:115656.1 和 Note:123214.1

CURSOR_SHARING 参数 (8.1.6 以上)

(在Oracle8.1.6引入).
这个参数需要小心使用。如果它被设为FORCE，那么Oracle会尽可能用系统产生的绑定变量来替换原来SQL中的literals部分。对于很多仅仅是literal不一样的相似的语句，这会让它们共享cursor。这个参数可以在系统级别或者session级别动态设置：

        ALTER SESSION SET cursor_sharing = FORCE;

或者

        ALTER SYSTEM SET cursor_sharing = FORCE;

或者在init.ora中设置

注意：因为FORCE会导致系统产生的绑定变量替换literal，优化器(CBO)可能会选择一个不同的执行计划，因为能够产生最好执行计划的literal值已经不存在了。

在Oracle9i（以上），可以设置CURSOR_SHARING=SIMILAR。如果这些语句只是literal部分不同，并且这些literal不会对SQL的含义有影响，或者可能会导致使用不同的执行计划，那么SIMILAR会共享这些语句。此增强功能适用于当FORCE会产生一个不同并且不是想要的执行计划时，从而提高了参数CURSOR_SHARING的可用性。设置CURSOR_SHARING=SIMILAR， Oracle会决定哪些literals可以被"安全"的替换成绑定变量，这样做的结果是有些SQL在可能产生更好执行计划的时候也不会被共享。

关于这个参数的更多详细信息，请参考 Note:94036.1。

注意: Similar在Oracle 12中不推荐使用。（译者注：根据Note:1169017.1，Oracle12将会移除cursor_sharing = SIMILAR的设置，而且在11g中就已经不推荐使用了，因为有
Adaptive Cursor Sharing的新特性） 请参考: Document:1169017.1 ANNOUNCEMENT: Deprecating the cursor_sharing = SIMILAR setting

SESSION_CACHED_CURSORS 参数

是一个可以在instance级别或者session级别设置的数值参数:

        ALTER SESSION SET session_cached_cursors = NNN;

数值NNN 决定在一个session中可以被'cached'的cursor的个数。

当一个语句被parse的时候，Oracle会首先检查session的私有缓存中指向的语句，如果有可被共享的语句版本的话，它就可以被使用。这为经常被parse的语句提供了一个捷径，可以比soft或者hard parse使用更少的CPU和非常少的Latch get。

为了被缓冲在session缓存中，同样的语句必须在相同的cursor中被parse 3次，之后一个指向shared cursor的指针会被添加到你的session缓存中。如果session缓存cursor已达上限，则最近最少使用的那一个会被替换掉。

如果你还没有设置这个参数，建议先设置为50作为初始值。之后查看bstat/estat报告的统计信息章节的'session cursor cache hits'的值，从这个值可以判断cursor缓存是否有作用。如果有必要的话，可以增加或者减少cursor缓存的值。SESSION_CACHED_CURSORS对于forms经常被打开和关闭的Oracle Forms应用非常有用。 

CURSOR_SPACE_FOR_TIME 参数

控制同一个语句不同执行之间一个cursor是否部分被保持（pin）住。如果设置其他参数都没效果的话，就值得尝试这个参数。这个参数在有不经常被使用的共享语句，或者有非常多的cursor被pinning / unpinning的时候是有帮助的。(查看视图：v$latch_misses – 如果大多数latch等待是因为cursor的pinning和 unpinning导致的"kglpnc: child"和"kglupc: child") .

你必须保证shared pool对于工作负载来说是足够大的，否则性能会受到严重影响而且最终会产生ORA-4031错误。

如果你把这个参数设为TRUE，请留意:

• • 如果SHARED_POOL对于工作负载来说太小的话更容易产生ORA-4031错误。
  • 如果你的应用有cursor泄漏，那么泄漏的cursor会浪费大量内存并在一段时间的运行之后对性能产生负面影响。
  • 目前已知的设置为true可能会导致的问题:
  • • Bug:770924 (Fixed 8061 and 8160) ORA-600 [17302] may occur
    • Bug:897615 (Fixed 8061 and 8160) Garbage Explain Plan over DBLINK
    • Bug:1279398 (Fixed 8162 and 8170) ORA-600 [17182] from ALTER SESSION SET NLS...

CLOSE_CACHED_OPEN_CURSORS 参数

这个参数已经在Oracle8i被废弃。

 控制当一个事务提交时是否PL/SQL cursor被关闭。默认值是FALSE，该设置在不同commits之后保持PL/SQL cursor打开以减少hard parse的次数。如果设成TRUE 的话可能会增加SQL在不用的时候被从shared pool 中清除出去的可能性。

SHARED_POOL_RESERVED_SIZE 参数

已经有相当多的文档解释过参数  。这个参数在Oracle 7.1.5被引进，它把shared pool 的一部分预留出来用于较大内存的分配。这个预留区域是从shared pool自身划分出来的。

从实践角度来说我们应该把SHARED_POOL_RESERVED_SIZE 设成SHARED_POOL_SIZE 的10%，除非shared pool 非常大或者 SHARED_POOL_RESERVED_MIN_ALLOC 被设得小于默认值：

• 如果shared pool 非常大的话，设成10%会浪费很多内存因为可能设成几MB就够用了。
• 如果SHARED_POOL_RESERVED_MIN_ALLOC被设的较小，则很多的空间请求都会符合从保留空间中分配的条件，那么10%也许就不够了。

查看视图  的FREE_SPACE列可以很容易监控保留区域的使用情况。

SHARED_POOL_RESERVED_MIN_ALLOC 参数

在 Oracle8i 这个参数是隐藏的.

尽管有些情况下SHARED_POOL_RESERVED_MIN_ALLOC设成4100或者4200可能对缓解较大压力下的shared pool的冲突有帮助，但是在大多数情况下应保持默认值。

SHARED_POOL_SIZE 参数

控制shared pool自己的大小，它能对性能造成影响。如果太小，则共享的信息会被从共享池中交换出去，过一阵子有需要被重新装载（重建）。如果literal SQL使用较多而且shared pool又很大，长时间使用后内部内存freelist上会产生大量小的内存碎片，使得shared pool latch被持有的时间变长，进而导致性能问题。在这种情况下，较小的shared pool也许比较大的shared pool好。因为 Bug:986149 的改进，这个问题在8.0.6和8.1.6以上版本被大大减少了。.

注意: 一定要避免由于shared pool设置过大进而导致的swap的发生的情况，因为当swap发生的时候性能会急剧下降。

参考 Note:1012046.6 来根据工作量计算SHARED_POOL_SIZE 需要的大小。

_SQLEXEC_PROGRESSION_COST parameter 参数 (8.1.5 以上)

这是一个Oracle 8.1.5引入的隐含参数。这里提到它是因为默认设置可能导致SQL共享方面的一些问题。设置成0会避免在shared pool 中产生语句高版本的问题。

例: 在init.ora 文件中增加这个参数

        # _SQLEXEC_PROGRESSION_COST 并且设成0来避免SQL 共享问题
        # 参考 Note:62143.1 获取更多信息
        _sqlexec_progression_cost=0

 

注意设成'0'的一个副作用会导致V$SESSION_LONGOPS视图中不记录长时间运行的查询。

参考 Note:68955.1 获取关于这个参数的更多信息。

预编译器的 HOLD_CURSOR 和 RELEASE_CURSOR 选项

当使用Oracle 预编译器预编译程序的时候，shared pool的行为可以通过参数RELEASE_CURSOR 和 HOLD_CURSOR 来控制。这些参数可以决定当cursor执行完毕之后library cache 和session cache 中cursor的状态。

关于这个参数的更多信息，请参考 Note:73922.1

将cursor固定（pinning）在shared pool中

另外一种减少library cache latch使用的方法是将cursor固定在shared pool中，详见以下文档：

Note:130699.1  How to Reduce 'LIBRARY CACHE LATCH' Contention Using a Procedure to KEEP Cursors Executed> 10 times

DBMS_SHARED_POOL.KEEP

这个存储过程 (RDBMS/ADMIN 目录下的DBMSPOOL.SQL脚本中有定义) 可以用来将对象KEEP到shared pool中， DBMS_SHARED_POOL.KEEP可以 'KEEP' packages, procedures, functions, triggers (7.3+) 和 sequences (7.3.3.1+) ，在 Note:61760.1 中有完整的描述。

通常情况下，建议将那些需要经常使用的package一直keep在shared pool中。KEEP操作在数据库启动后需要尽快实施，因为在shutdown之后Oracle不会自动重新keep这些对象。

注意:在Oracle 7.2之前DBMS_SHARED_POOL.KEEP实际上不会把需要KEEP的对象完整的放到shared pool中。所以建议在每一个要被KEEP的package中放一个空的存储过程，在执行完DBMS_SHARED_POOL.KEEP之后再调用一下这个空存储过程来保证对象被完全装载。这在7.2之后已经修复了。 

 

Flushing（清空） SHARED POOL

在使用大量literal SQL的系统中，shared pool随时间推移会产生大量碎片进而导致并发能力的下降。Flushing shared pool能够使得很多小块碎片合并，所以经常能够在一段时间内恢复系统的性能。清空之后可能也会产生短暂的性能下降，因为这个操作同时也会把没造成shared pool碎片的共享SQL也清除了。清空shared pool的命令是：

        ALTER SYSTEM FLUSH SHARED_POOL;

注意：如果显式的使用以上命令，即使是用 DBMS_SHARED_POOL.KEEP 而被保留的那些对象可能也会被释放掉，包括它们占用的内存。如果是隐式的 flush （由于 shared pool上的内存压力） 这个时候“kept"的对象不会被释放。

注意:如果sequence使用了cache选项，冲刷shared pool有可能会使sequence在其范围内产生不连续的记录。使用DBMS_SHARED_POOL.KEEP('sequence_name','Q')来保持sequence会防止这种不连续的情况发生。

DBMS_SHARED_POOL.PURGE 

也可以不刷新整个shared pool，而只清空其中的单个对象。下面的文档说明了10g和11g中如何清空library cache heap。

Document:751876.1 DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4

使用 V$ 视图 (V$SQL 和 V$SQLAREA)

注意有一些V$视图需要获取相关的latch来返回查询的数据。用来展示library cache和 SQL area的视图就是值得注意的。所以我们建议有选择性的运行那些需要访问这种类型视图的语句。特别需要指出的是，查询V$SQLAREA会在library cache latch上产生大量的负载，所以一般可以使用对latch访问比较少的v$sql做替代——这是因为V$SQLAREA的输出是基于shared pool中所有语句的GROUP BY操作，而V$SQL没有用GROUP BY操作。

MTS, Shared Server 和 XA

由于多线程服务器(MTS)的User Global Area (UGA)是存放在shared pool中的，所以会增加shared pool的负载。在Oracle7上的XA session也会产生同样的问题，因为他们的UGA也是在shared pool里面（在Oracle8/8i开始XA session不再把UGA放到shared pool中）。在Oracle8中Large Pool可以被用来减少MTS对shared pool活动的影响——但是，Large Pool中的内存分配仍然会使用"shared pool latch"。对Large Pool的描述请参考Note:62140.1.

使用dedicate connections(专有连接)替代MTS可以使UGA在进程私有内存中分配而不是shared pool。私有内存分配不会使用"shared pool latch"，所以在有些情况下从MTS切换到专有连接可以帮助减少竞争。

在Oracle9i中，MTS被改名为"Shared Server"。但是对于shared pool产生影响的行为从根本上说还是一样的。 

使用SQL 查看Shared Pool问题

这一章节展示了一些可以用来帮助找到shared pool中的潜在问题的SQL语句。这些语句的输出最好spool到一个文件中。

注意:这些语句可能会使latch竞争加剧，我们在上面的 "使用 V$ 视图 (V$SQL 和 V$SQLAREA)" above.

• 查找literal SQL

          SELECT substr(sql_text,1,40) "SQL",
                 count(*) ,
                 sum(executions) "TotExecs"
            FROM v$sqlarea
           WHERE executions < 5
           GROUP BY substr(sql_text,1,40)
          HAVING count(*) > 30
           ORDER BY 2
          ;

这个语句有助于找到那些经常被使用的literal SQL – 请查看上面的"消除 Literal SQL"

• 检索Library Cache hit ratio

        SELECT SUM(PINS) "EXECUTIONS",
        SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
        FROM V$LIBRARYCACHE;

如果misses/executions高于1%的话，则需要尝试减少library cache miss的发生。

• 检查 hash chain 的长度:

        SELECT hash_value, count(*)
          FROM v$sqlarea
         GROUP BY hash_value
        HAVING count(*) > 5
        ;

这个语句正常应该返回0行。如果有任何HASH_VALUES存在高的count（两位数的）的话，你需要查看是否是bug的影响或者是literal SQL使用了不正常的形式。建议进一步列出所有有相同HASH_VALUE的语句。例如：

     SELECT sql_text FROM v$sqlarea WHERE hash_value= ;

如果这些语句看起来一样，则查询V$SQLTEXT去找完整的语句。有可能不同的SQL文本会映射到相同的hash值，比如：在7.3中，如果一个值在语句中出现2次而且中间正好间隔32个字节的话，这两个语句会映射出相同的hash值。

• 检查高版本:

        SELECT address, hash_value,
                version_count ,
                users_opening ,
                users_executing,
                substr(sql_text,1,40) "SQL"
          FROM v$sqlarea
         WHERE version_count > 10
        ;

在上面的"Sharable SQL"章节中，我们已经描述了，一个语句的不同"版本"是当语句的字符完全一致但是需要访问的对象或者绑定变量不一致等等造成的。在Oracle8i的不同版本中因为进度监控的问题也会产生高版本。在这篇文档的前面描述过了，我们可以把_SQLEXEC_PROGRESSION_COST 设成'0'来禁止进度监控产生高版本。

• 找到占用shared pool 内存多的语句:

        SELECT substr(sql_text,1,40) "Stmt", count(*),
                sum(sharable_mem)    "Mem",
                sum(users_opening)   "Open",
                sum(executions)      "Exec"
          FROM v$sql
         GROUP BY substr(sql_text,1,40)
        HAVING sum(sharable_mem) > &MEMSIZE
        ;

 这里MEMSIZE取值为shared pool大小的10%，单位是byte。这个语句可以查出占用shared pool很大内存的那些SQL，这些SQL可以是相似的literal语句或者是一个语句的不同版本。

• 导致shared pool 内存'aged' out的内存分配

        SELECT *
          FROM x$ksmlru
        WHERE ksmlrnum>0
        ;

注意: 因为这个查询在返回不超过10行记录后就会消除X$KSMLRU的内容，所以请用SPOOL保存输出的内容。X$KSMLRU表显示从上一次查询该表开始，哪些内存分配操作导致了最多的内存块被清除出shared pool 。有些时候，这会有助
于找到那些持续的请求分配空间的session或者语句。如果一个系统表现很好而且共享SQL 使用得也不错，但是偶尔会变慢，这个语句可以帮助找到原因。关于X$KSMLRU 的更多信息请查看 Note:43600.1。

在不同Oracle Releases中的都会遇到的问题

在不同的release中有一些通用的会影响shared pool性能的问题：

• 增加每个CPU的处理能力可以减少latch 被持有的时间从而有助于在Oracle 的各个release上减少shared pool竞争。换一个更快的CPU一般来说会比增加一个慢的CPU效果要好。
• 如果你设置了一个EVENT，不管基于什么原因，请让Oracle support 检查这个event是否会对shared pool 的性能造成影响。

• 确保Oracle 实例有足够的内存，避免SGA内存被操作系统swap交换出去的风险。
  例如: 在AIX上操作系统的不正确设置可能会导致 shared pool问题- 参考 Note:316533.1 .

Bug 修复和增强功能

这里列出了主要的影响shared pool的bug和功能增强。'Fixed'列是4位数字的服务器版本号，表示在哪个版本上这个问题被修复或者功能被增强——例如：8062表示在8.0.6.2上修复，9000表示这个问题在Oracle9i上修复。

Bug	Versions	Fixed	Description
Bug.1623256	8-90	9000	Identical SQL referencing SCHEMA.SEQUENCE.NEXTVAL not shared by different users
Bug.1366837	-90	8063 8171 9000	Cursors referencing a fully qualified FUNCTION are not shared
Bug.1484634	-90	8063 8171 9000	Large row cache can cause long shared pool latch waits (OPS only)
Bug.1318267	815-90	9000	INSERT AS SELECT may not share SQL when it should
Bug.1149820	-817	8062 8162 8170	ENHANCEMENT: Reduced latch gets purging from shared pool
Bug.1150143	-817	8062 8162 8170	ENHANCEMENT: Delay purge when bind mismatch
Bug.1258708	-817	8170	ENHANCEMENT: Reduce need to get PARENT library cache latch
Bug.1348501	8-817	8163 8170	MVIEW refresh unnecessarily invalidates shared cursors
Bug.1357233	815-817	8163 8170	ALTER SESSION FORCE PARALLEL PQ/DML/DDL does not share recursive SQL
Bug.1193003b	815-817	8162 8170	Cursors may not be shared in 8.1 when they should be
Bug.1210242	815-817	8162 8170	Cursors not shared if both TIMED_STATISTICS and SQL_TRACE are enabled
Bug.986149	7-816	8060 8160	ENHANCEMENT: More freelists for shared pool memory chunks (reduced latch contention)
Bug.858015	815-816	8160	Shared pool memory for views higher if QUERY_REWRITE_ENABLED set
Bug.918002	815-816	8151 8160	Cursors are not shared if SQL_TRACE or TIMED_STATISTICS is TRUE
Bug.888551	815-816	8151 8160	TIMED_STATISTICS can affect cursor sharing / Dump from EXPLAIN or enable/disable SQL_TRACE
Bug.1065010	8-817	8062 8162 8170	Access to DC_HISTOGRAM_DEFS from Remote Queries can impact shared pool performan ce.
Bug.1115424	-817	8062 8162 8170	Cursor authorization and dependency lists too long - can impact shared pool
Bug.1131711	803-8062	8062 8150	SQL from PLSQL using NUMERIC binds may not be shared when it should
Bug.1397603	817	82	ORA-4031 / SGA leak of PERMANENT memory for buffer handles
Bug.1640583	816	8171 8200	ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access

 

历史记录

这里的记录是关于Oracle7.3之前的版本的，只是为了完整性的原因被包含在本文档中:

• 在7.3中，PLSQL被增强为可使用页执行编码以减少shared pool大量内存分配的数量，并且减少使用KEEP的需求。
• Oracle 7.1.6 到7.2.3 有很多已知问题。请查看 Note:32871.1
• 从Oracle 7.1 到 7.2，library cache的latch机制发生了改变。
• 一些历史bug:

 

Bug	Versions	Fixed	Description
Bug.596953	80-81	8044 8052 8060 8150	Excessive shared pool fragmentation due to 2K context area chunk size.
Bug.724620	700-814	7344 8043 8052 8060	Select from VIEW now uses less shared memory (less latch gets)
Bug.633498	7-815	7343 8043 8050 8150	Selecting from some V$ views can make statements unsharable
Bug.625806	7X-806	7343 8042 8051 8060 8150	Cursor not shared for a VIEW using FUNCTION / with DBMS_SQL
Bug:520708	7X-804	7336 7342 8040	Better handling of small memory chunks in the SGA

References

NOTE:43600.1  - VIEW: X$KSMLRU - LRU flushes from the shared pool - (7.3 - 8.1)
NOTE:61760.1  - Using the Oracle DBMS_SHARED_POOL Package
NOTE:94036.1  - Init.ora Parameter "CURSOR_SHARING" Reference Note
BUG:1065010  - PERFORMANCE PROBLEM WITH RECURSIVE LINKS
BUG:1115424  - CURSOR AUTHORIZATION AND DEPENDENCY LISTS GROW CAUSING LATCH CONTENTENTION



NOTE:68955.1  - Init.ora Parameter "_SQLEXEC_PROGRESSION_COST" [Hidden] Reference Note
NOTE:73922.1  - Tuning Precompiler Applications
NOTE:751876.1  - DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4




BUG:1366837  - CURSOR NOT SHARED FOR TABLES INVOKING A FUNCTION

BUG:1484634  - ONE INSTANCE OF OPS HANGS
BUG:1623256  - IDENTICAL SQL REFERENCING SCHEMA.SEQUENCE.NEXTVAL NOT SHARED BY DIFFERENT USERS
BUG:1640583  - ORA-4031 AND CACHE BUFFER CHAIN CONTENTION AFTER UPGRADE TO 8163



NOTE:62140.1  - Fundamentals of the Large Pool
NOTE:62143.1  - Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention

BUG:625806  - CURSOR NOT SHARED FOR VIEWS INVOKING A FUNCTION

BUG:633498  - STATEMENTS IN SHARED POOL DON'T GET REUSED AFTER SELECTING FROM V$OPEN_CURSOR
BUG:897615  - EXPLAIN PLAN OVER DBLINK PUTS GARBAGE IN THE PLAN TABLE


NOTE:115656.1  - WAIT SCENARIOS REGARDING LIBRARY CACHE PIN AND LIBRARY CACHE LOAD LOCK

NOTE:1169017.1  - ANNOUNCEMENT: Deprecating the cursor_sharing = 'SIMILAR' setting
NOTE:123214.1  - Truncate - Causes Invalidations in the LIBRARY CACHE
NOTE:1012046.6  - How to Calculate Your Shared Pool Size
NOTE:32871.1  - ALERT: Library Cache Performance Problems in Oracle Releases 7.1.6 to 7.2.3
NOTE:316533.1  - AIX: Database performance gets slower the longer the database is running

BUG:770924  - ORA-600 [17302][9] DOING A QUIT IN SQLPLUS AFTER CONTEXT QUERY