导言
---------
本文的目的是介绍在Oracle7-Oracle9中，如果共享池调优。如果系统有以下问题，需要关注共享池调优。
- library cache latch的争用
- shared pool latch的争用
- 解析CPU消耗较高
- V$LIBRARYCACHE的reloads次数较高
- 大量解析调用
- 频繁发生ORA-4031错误
什么是共享池？
-------------------
Oracle在SGA中用来保存SQL语句、包、对象信息以及其他相关内容的区域叫做共享池。内存的共享区域，由复杂的cache以及大量内部的管理器管理。主要解决以下3个基本问题：
1.内存单元的分配不是固定的，池中的内存分配可以根据需要从几字节到几十K不等。
2.并不是所有使用结束之后的内存都会释放(类似传统的堆管理)，共享池的目的是最大化信息的可共享性。内存信息可能也可用于其他session-Oracle无法预先知道其他session是否需要这些信息。
3.没有磁盘区的page out，所以不像传统的cache有一个文件存储备份。
共享池管理较为复杂，下面列出了影响共享池性能以及相关latch的关键点
包括的内容有：
- 相关术语
- 全文字SQL的好处
- 为什么需要共享SQL
- 降低共享池的负载
- 一次解析多次执行
- 消除全文字SQL
- 避免失效
- CURSOR_SHARING参数(8.1.6之后版本)
- SESSION_CACHED_CURSORS参数
- CURSOR_SPACE_FOR_TIME参数
- CLOSE_CACHED_OPEN_CURSORS参数
- SHARED_POOL_RESERVED_SIZE参数
- SHARED_POOL_RESERVED_MIN_ALLOC参数
- SHARED_POOL_SIZE参数
- _SQLEXEC_PROGRESSION_COST参数(8.1.5之后版本)
- 预编译HOLD_CURSOR和RELEASE_CURSOR选项
- DBMS_SHARED_POOL.KEEP
- 刷新共享池
- 使用V$视图(V$SQL和V$SQLAREA)
- MTS和XA(共享服务器和分布式事务)
- 检查共享池问题的SQL
- 不同Oracle版本产生的问题
相关术语
=============
全文字SQL
-----------
全文字SQL考虑将谓词(条件内容)变更为绑定变量时，需要考虑不同的值会造成不同的执行计划。
例1：
SELECT * FROM emp WHERE ename='CLARK';
替换绑定变量为：
SELECT * FROM emp WHERE ename=:bind1;
例2：
SELECT sysdate FROM dual;
不需要使用绑定变量，只使用全文字SQL，该SQL可以被共享。
例3：
SELECT version FROM app_version WHERE version>2.0;
如果在应用中，总是这个version>2.0的条件来检查version，那么该语句可以被考虑为是共享的。
硬解析
---------
1条新的SQL，不存在共享池中，需要被完全解析。例如：Oracle需要从共享池中为该语句分配内存，检查该语句的语法和语义等，这个过程被称为硬解析，需要消耗较多的CPU以及较多的latch数量。
软解析
---------
如果session执行的SQL语句已经在共享池中了并且可以使用现有语句译本，这称为软解析。对于应用程序而言，只需要执行已解析的语句。
完全相同语句
-----------
如果2个SQL语句表示相同的意思，但具有不同的字符，ORACLE认为是不同的2个语句。
SELECT ENAME from EMP;
SELECT ename from emp;
2个语句内容是一样的，只是一些字母是大写，一些是小写，但在共享池中会保存2种sql文本。
可共享SQL
------------
2个session执行相同的SQL语句，并不意味着该语句就具有共享性，如下：
用户SCOTT下有一张表名为EMP，执行如下语句：
SELECT ENAME from EMP;
用户FRED用户下同样有一张名为EMP的表，也执行相同的语句：
SELECT ENAME from EMP;
虽然2个session执行的语句文本是一样的，但涉及的EMP表分别是不同的对象。因此，相同的语句具有不同的译本。在一些情况下，2个文本相同的SQL表示相同的含义(可以共享)，如下：
- 每个语句中的对象名称，必须表示相同的对象。
- 每个session执行该语句，优化器给出的执行计划必须是相同的。
- 每个语句中相同的绑定变量的类型和长度需要类似(这里暂不展开讨论，绑定变量不同的长度和类型会导致不同的语句译本)
- 应用于语句的NLS(National Language Support)环境必须相同。
语句的译本
------------
在上面"可共享SQL"中描述的，具有相同文本的SQL却不能共享，它们调用的是相同SQL的不同译本。如果一个语句有很多译本，ORACLE需要对每个译本轮询检查一遍寻找是否与当前语句相同含义已解析的SQL语句。所以最好避免出现大量的译本，避免可以用以下方法：
- 通过客户端指定统一的绑定变量最大长度。
- 避免在不同的schema中使用相同的文本SQL语句涉及私有对象。例如SELECT xx FROM MYTABLE;每个用户下都有MYTABLE表，修改为表名前带owner。
- 在ORACLE 8.1中设置_SQLEXEC_PROGRESSION_COST为0。
Library Cache和Shared Pool的latch
----------------------------------
当在共享池中分配和释放内存时，共享池latch用来保护重要的操作。
Library Cache latch(在Oracle 7.1中是library cache pin latch)用来保护library cache自身的操作。
这些latch都存在潜在的争用，共享池的活动情况，尤其是解析操作，会直接影响到获得到latch的次数。任何能减少获得到latch的数量甚至是共享池的活动量，都有助于性能和可扩展性。
全文本SQL与可共享SQL的比较
=============================
下面分别描述全文本SQL与可共享SQL各自的好处
全文本SQL
----------------
在成本优化器(CBO)工作的环境中，假设已经对表和索引进行了充分分析统计，SQL语句谓词使用文本内容，如下:
SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0;
对比下面的SQL
SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA;
对于第一个语句，CBO将通过已收集的柱状图统计，对比对ORDERS表的全表扫描和使用索引的TOTAL_COST，看哪种更快。在第二个语句中，CBO无法通过变量":bindA"得知取得行在表中占的百分比，无法根据这个没有准确值的绑定变量来分配准确的执行计划。例如":bindA"为0.0 或 99999999999999999.9绑定变量相同。
由于条件的扩度不同，可能会有不同的执行计划，这种情况下，最好使用全文本SQL，让CBO选择最适合的执行计划。在DSS系统中，通常不会重复地执行相同的语句，所以很少有共享语句的情况。这种情况下，大量的CPU消耗在解析上，只有少部分用于执行每个不同的SQL，但也因为给优化器更多的信息比减少解析时间更重要，所以采用全文本SQL。
可共享SQL
------------
如果使用全文本SQL(非共享)会严重影响可扩展性和吞吐量。每个新的SQL语句都要消耗CPU进行解析，以及多次请求和释放library cache和shared pool的latch。
例如：解析一个简单的SQL语句，也需要请求获得library cache latch20或30次。
最好是所有的SQL语句都具有共享性，除非是临时的或较少使用的SQL，需要给CBO较多的信息才能有一个较好的执行计划。
降低共享池的负载
======================
一次解析多次执行
------------------
到目前为止，对于OLTP类型的应用，最好的方法对语句只解析1次，保持该游标的打开状态，需要时就可以执行。这将导致每次执行都是第一次解析的信息(不论是软解析还是硬解析)。如果有一些很少执行的语句，这样为它们打开游标是一种资源的浪费。
注意，只有1个session存在，不等于可能增加并行游标打开数，使得更多的游标打开且有效。
在预编译程序中 HOLD_CURSOR 参数控制游标是否保持打开，OCI的开发人员可以直接控制游标。
消除全文字SQL
------------------
如果现有应用，不可能消除所有的全文字SQL，那么至少应该消除那些影响性能的全文字SQL。通过检查V$SQLAREA视图，可以查看哪些SQL可以修改使用绑定变量。下面的查询语句可以检查出，在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
;
注意，如果系统存在library cache的latch争用，上面检查出的那些语句还会引起其他的资源争用问题。
上面的查询SQL的意思是表示SQL文本前40个字符相等，执行次数少于5次，在共享池中不同的SQL多于30个。例如有一批SQL开头都为"SELECT col1,col2,col3 FROM table WHERE ..."。
将全文本SQL转为绑定变量，是消除共享池问题和提高可扩展性，最好的一种方法。
结合应用的使用，尽可能地考虑使用绑定变量。
避免失效
------------
一些特殊命令将游标的状态置为INVALIDATE。这些命令直接修改与游标有关对象的上下文。这些命令有TRUNCATE、ANALYZE、DBMS_STATS.GATHER_(TABLE/INDEX)_STATS、底层对象授权变化。这些相关的游标虽然保留在SQLAREA中，但当下次重新启用时，需要重新装载重新分析，invalidations计数增加1，对整体性能有影响。
下面的查询SQL便捷地找出这类游标的SQL。
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之后版本)
----------------------------------
该参数在8.1.6中开始引入，该参数的设置需要谨慎。如果该参数设置为FORCE，相关的文字内容会被替换成系统产生的绑定变量。对于大量类似语句，只有条件中的文本内容不同的，可以允许共享游标，即使在应用中这些SQL是使用全文字的。该参数可以动态设置在系统级别或会话级别：
ALTER SESSION SET cursor_sharing = FORCE;
或
ALTER SYSTEM SET cursor_sharing = FORCE;
或者设置在初始化参数中。
例如：
设置了该参数为FORCE，分别执行以下2个语句：
select * from skew where id=15;
select * from skew where id=17;
在v$sqlarea都是同一条可以共享的语句：
select * from skew where id=:"SYS_B_0"
注意：设置了FORCE，系统将产生绑定变量替代文本内容。CBO不再会根据不同的值来选择执行计划，有可能造成执行计划的偏差。
从9i开始，可以设置CURSOR_SHARING=SIMILAR，SIMILAR表示允许只有一些文字不同，但其他大部分相同的语句可以共享游标，除非这些文字影响到语句的意思或执行计划的优化程度。改善了FORCE设置造成的执行计划不同的问题。Oracle可以决定安全地将部分文本替换为绑定变量，试图共享一个更高效的执行计划。
例如
select * from skew where id=14;
select * from skew where id=15;
select * from skew where id=17;
select * from skew where id=501;
在v$sqlarea为同一条语句的2个译本：
select * from skew where id=:"SYS_B_0"
其中前3个为同一个译本，最后1个为另一个译本。
关于该参数，查看：Note:94036.1
SESSION_CACHED_CURSORS参数
-----------------------------
该参数为数值类型，可以在实例及或会话级设置：
ALTER SESSION SET session_cached_cursors = NNN;
其中NNN表示在一个session中可以cached多少个游标。
当一个语句被解析过，Oracle首先去检查session的私有cache，如果这个语句的共享译本存在，且可以使用，那么就在指向cache中的该语句。这为频繁解析的语句提供了一个捷径，更少地消耗CPU，并且比软、硬解析都获取更少的latch。
相同的语句解析3次都是相同的游标，就把它放到session cache中，增加一个共享游标指向该session cache中。最近较少被使用的游标，将会从session cache中除去。
如果该参数没有设置，建议设置该参数为50。在bstat/estat报告中的'session cursor cache hits'部分，可以看到cache游标的好处。cache游标的数量可以根据需求增大或减小。SESSION_CACHED_CURSORS参数对于需要频繁打开和关闭的Oracle表单应用很有用。
CURSOR_SPACE_FOR_TIME参数
-----------------------------
该参数可以让执行过的语句始终保持pin在cache中，不会老化。对于那些不常使用但又希望共享的语句来说比较有用。或是常常出现游标的pinning / unpinning(查看如果出现由于"kglpnc: child"和"kglupc: child"导致的latch等待，可能是游标的pinning / unpinning导致的)
设置该参数为TRUE之前，必须要确保有足够大的共享池，如果不够大，很有可能导致ORA-4031错误。
设置该参数为TRUE，必须了解以下几点：
-如果共享池太小，不足以满足工作量，很有可能产生ORA-4031错误。
-如果应用中任何一个指针发生泄露，将会消耗大量内存资源，这样反而会极大地影响性能。
-设置该参数为TRUE，可能会带来一些问题，参看下面的一些BUG。
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的游标在事务COMMIT之后是否关闭。
SHARED_POOL_RESERVED_SIZE参数
-------------------------------
每个版本的数据库在这部分都有相当多的要点需要解释。该参数在Oracle7.1.5中引入，目的是为了保留一部分共享池为大内存分配使用。保留区在共享池之外。
从实用的观点来看SHARED_POOL_RESERVED_SIZE大约要设置为SHARED_POOL_SIZE的10%，除非共享池非常大或者_SHARED_POOL_RESERVED_MIN_ALLOC参数被设置得比默认值小。
-如果共享池非常大，当只需要几MB的时候，10%的设置会浪费大量内存。
-如果_SHARED_POOL_RESERVED_MIN_ALLOC参数被设置得较低，那么所需的保留池空间可能要更多，共享池的10%可能不够。
监控保留池的使用情况，可以查看视图的FREE_SPACE字段。
SHARED_POOL_RESERVED_MIN_ALLOC参数
-----------------------------------
从Oracle8i开始，该参数变为隐含参数
SHARED_POOL_RESERVED_MIN_ALLOC的默认值通常已经可以满足，但在一些情况下，设置为4100或4200可以有效地减轻共享池的负载。
SHARED_POOL_SIZE参数
----------------------
控制共享池自身大小。共享池的大小对性能有着直接的影响。如果太小，常常发生需要共享的信息被最近进入的信息给置换出去。如果使用大量全文字SQL并且共享池很大，随着时间的推移，会在内存的fresslist上产生大量小的内存chunk，从而导致共享池latch在轮流的时候会被持有更长的时间，影响性能。在这种情况下，较小的共享池比更大的共享池更有效。
注意，共享池并不是越大越好，太大的共享池反而会引起paging或swapping，导致性能严重下降。
参看Note:1012046.6 ，根据当前工作量计算SHARED_POOL_SIZE参数的设置。
_SQLEXEC_PROGRESSION_COST参数(8.1.5之后版本)
-----------------------------------------------
该隐含参数在Oracle8.1.5中引入。该参数的设置与SQL的共享性有关，设置该参数可能会带来一些SQL共享的问题。该参数设置为0，可以消除在共享池中出现同一个语句的多个译本。
例如：增加该参数到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共享池的预编译行为时，需要在修改程序预编译参数RELEASE_CURSOR 和 HOLD_CURSOR，这些参数决定了游标在library cache中的状态，session一旦执行过一次，就将其cache。
更多信息参看： Note:73922.1 和ID 2055810.6
DBMS_SHARED_POOL.KEEP
----------------------
该程序(默认在RDBMS/ADMIN下的DBMSPOOL.SQL脚本中)用于将对象KEEP在共享池中。DBMS_SHARED_POOL.KEEP可以KEEP：packages, procedures, functions, triggers (7.3+) 和sequences (7.3.3.1+)，更多描述参看Note:61760.1
通常用于将哪些经常使用的包KEEP到共享池中。对象需要在实例启动后不久后KEEP，因为数据库如果关闭，对象不会在下次启动时自动KEEP。
刷新共享池
--------------
如果系统中有大量全文字SQL，随着时间的推移，会产生大量碎片，使系统的并发度下降。刷新共享池可以恢复一段时间的性能，因为刷新会将多数较小的内存chunk整合起来。在刷新之后，会在一段时间内影响性能，因为移除了共享SQL，刷新共享池并不能根本地解决共享池碎片的问题。刷新共享池的命令如下：
ALTER SYSTEM FLUSH SHARED_POOL;
需要注意的是，使用DBMS_SHARED_POOL.KEEP的对象被KEEP在共享池中，在刷新时，不会被刷新。所有被session pin住的内容(对象或SQL)，在刷新时都会被留在原来的地方。
注意：刷新共享池时，可能会导致cache序列出现间隔差距。使用DBMS_SHARED_POOL.KEEP('sequence_name','Q')可以将序列KEEP住，从而避免出现间隔差距。
使用V$视图(V$SQL和V$SQLAREA)
------------------------------
一些V$视图的记录通常是根据相关latch去获得数据而重复请求的信息。这里主要关注library cache和SQL AREA。通常从这些视图中有选择性地挑选关注的SQL。V$SQLAREA中就记录了大部分的library cache latch的负载。注意，V$SQL常常可以替代V$SQLAREA的使用，该视图可以减少latch的gets。因为V$SQLAREA中有一个GROUP BY语句，V$SQL中没有GROUP语句。
MTS和XA(共享服务器和分布式事务)
----------------------------------
在共享服务器模式中，需要增加共享池的负载，此类信息在共享池中，叫做用户全局区UGA(User Global Area)，在Oracle7中XA的session也将UGA放在共享池中(从Oracle8开始，XA session的UGA不放在共享池中)。从Oracle8开始，MTS使用Large Pool，减少对共享池的影响，但是分配Large Pool的内存时，仍然需要使用shared pool latch。参看 Note:62140.1对Large Pool的描述。
使用专用服务器模式连接比使用MTS更好，因为UGA的分配是由进程的私有内存而不是共享池管理的。私有内存分配不会用到shared pool latch，所以将MTS转变为专用服务器模式连接，可以减少一些争用。
在Oracle9i中，MTS被称为共享服务器模式。对于共享池来说，连接机制本质是相同的。
检查共享池问题的SQL
============================
以下SQL脚本用于帮助查找共享池的潜在问题，可以将这些语句内容输出到一个文件中。
注意：使用这些语句查找v$视图时，势必会增加一些latch的争用，参考上面的"使用V$视图(V$SQL和V$SQLAREA)"部分。
-查找全文字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;
用于检查哪些SQL可以修改为绑定变量，查考上面"消除全文字SQL"的部分。
-检查Library Cache的命中率：
SELECT SUM(PINS) "EXECUTIONS",
SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
FROM V$LIBRARYCACHE;
如果执行的miss超过1%，就需要考虑减少library cache的miss。
-检查hash链长：
SELECT hash_value, count(*)
FROM v$sqlarea
GROUP BY hash_value
HAVING count(*) > 5;
通常没有行返回，如果HASH_VALUES有较高的数量(达到2位数)，那么需要检查是否遭遇了BUG，或是否有不常用的全文本SQL语句格式。根据该HASH_VALUE检查具体的SQL内容：
SELECT sql_text FROM v$sqlarea WHERE hash_value= ;
查看V$SQLTEXT可以获得SQL的完整语句，分多行多个片段显示。
-检查译本较多的SQL：
SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) "SQL"
FROM v$sqlarea
WHERE version_count > 10;
关于译本的描述，参考上面的"可共享SQL"部分。译本数量较多在Oracle8i的不同版本中都有不同问题，在早期版本中可以通过设置_SQLEXEC_PROGRESSION_COST为0，来禁用共享。
-查找使用大量共享池内存的语句：
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) > ;
其中可以设置为共享池大小的10%。上面的语句可以显示，哪些相似文本内容的SQL或多个译本的SQL，消耗了大量的共享池内存。
-分配导致老化的共享池内存：
SELECT *
FROM x$ksmlru
WHERE ksmlrnum>0;
注意：通常不会超过10行。X$KSMLRU内容很快会消失，所以建议用SPOOL输出。 X$KSMLRU里显示了最近查询中分配了大量内存chunk，将要被扔出共享池的分配信息。用于确定那些频繁请求空间的session或语句。如果系统有一条SQL具有很好的共享性，但偶然该语句变慢，可以通过上面脚本检查。参看Note:43600.1可以获得更多关于X$KSMLRU的信息。
不同Oracle版本产生的问题
============================
以下是一些在不同版本中，影响共享池性能的重要内容：
-增强每个CPU的处理能力，可以帮助减少共享池争用的问题，在所有Oracle版本中都可以减少latch总体的持有时间。
-如果由于检查的原因设置了EVENT参数，该event不能对共享池性能造成影响。
-确保不会出现内存不足的情况，使得Oracle实例的内存SGA内存需要page out。
例如：在AIX上由于OS的设置造成共享池的性能问题，查看Note:316533.1
