【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列4

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列3

http://www.zzzyk.com/database/201309/240537.html

CURSOR_SHARING 参数 (8.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值已经不存在了。

注意: Similar在Oracle 12中不推荐使用。(译者注：根据Note:1169017.1，Oracle12将会移除cursor_sharing = SIMILAR的设置，而且在11g中就已经不推荐使用了，因为有Adaptive Cursor Sharing(传说可以根据数据量的实际大小来选择合适的执行计划，避免10g之前的绑定变量窥探)的新特性)，请参考: 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已达上限，则最近最少使用的那一个会被替换掉(LRU策略)。

如果你还没有设置这个参数，建议先设置为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%也许就不够了。

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

SHARED_POOL_RESERVED_MIN_ALLOC参数

在 Oracle8i这个参数是隐藏的.

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

SHARED_POOL_SIZE参数

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 需要的大小。

预编译器的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之后已经修复了。