理解Oracle Shared Pool

Literal SQL

个Literal SQL语句是指在predicate中使具体值,不是使绑定变量,即不同的执语句使的具体值可能是不样的。

使了:

SELECT * FROM emp WHERE ename = 'CLARK';

而不是:

SELECT * FROM emp WHERE ename = :bind1;

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

SELECT version FROM app_version WHERE version > 2.0;

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

SELECT sysdate FROM dual;

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;

尽管它们实际上是相同的,但是因为字母大小写的区别,他们不会被认为是完全相同的语句。

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

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的关键问题,包括:

减轻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:

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

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;

set pages 10000 

set linesize 250

col SQL_TEXT for a100

col FORCE_MATCHING_SIGNATURE for 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;

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

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

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

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

避免 Invalidations 有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上下文环境。它包括TRUNCATE, 表或索引上

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

有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上下文环境。它包括TRUNCATE, 表或索引上

的ANALYZE或 DBMS_STATS.GATHER_XXX,关联对象的权限变更。相对应的cursor会留在SQLAREA中,但是下次被引时会被 完全reload并重新parse,所以会对数据库的整体性能造成影响。

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

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

ALTER SESSION SET cursor_sharing = FORCE;

或者在init.ora中设置

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

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

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,请留意:

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

ALTER SYSTEM SET cursor_sharing = FORCE;

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

注意: Similar在Oracle 12中不推荐使。(译者注:根据Note:1169017.1,Oracle12将会移除cursor_sharing = SIMILAR的设置, 而且在11g中就已经不推荐使了,因为有

Adaptive Cursor Sharing的新特性) 请参考: Document 1169017.1 ANNOUNCEMENT: Deprecating the cursor_sharing = SIMILAR setting

注意:CURSOR_SPACE_FOR_TIME 在 10.2.0.5 和 11.1.0.7 之后已经被废弃了。参考文档(565424.1)CURSOR_SPACE_FOR_TIME Has Been Deprecated

如果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发生的时候性能会急剧下降。

参考 1012046.6 ）来根据工作量计算SHARED_POOL_SIZE 需要的小。 _SQLEXEC_PROGRESSION_COST parameter 参数。

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

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

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

参考 Document 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中

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

另外种减少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和 sequences,在 Note:61760.1 中 有完整的描述。

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

Flushing(清空) SHARED POOL

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

注意:如果显式的使以上命令,即使是 DBMS_SHARED_POOL.KEEP 而被保留的那些对象可能也会被释放掉,包括它们占 的内存。如果是隐式的 ush (由于 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。

参考(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，建议在每 个要被KEEP的package中放个空的存储过程,在执完DBMS_SHARED_POOL.KEEP之后再调下这个空存储过程来保证对象被完全装载。

SELECT substr(sql_text, 1, 40) "SQL", count(*), sum(executions) "TotExecs"

  FROM v$sqlarea

 WHERE executions < 5

 GROUP BY substr(sql_text, 1, 40) count(*) > 30

 ORDER BY 2;

这个语句有助于找到那些经常被使的literal SQL – 请查看上面的"消除 Literal SQL" 另种方式是按照"plan_hash_value"进分组:

SELECT SUBSTR(sql_text, 1, 40) "SQL",

       plan_hash_value,

       COUNT(*),

       SUM(executions) "TotExecs"

  FROM v$sqlarea

 WHERE executions < 5

 GROUP BY plan_hash_value, SUBSTR(sql_text, 1, 40)

HAVING COUNT(*) > 30

 ORDER BY 2;

检索Library Cache hit ratio

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

检查 hash chain 的长度:

这个语句正常应该返回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值。

检查高版本:

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

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

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

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

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

SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"

  FROM V$LIBRARYCACHE;

SELECT hash_value, count(*)

  FROM v$sqlarea

 GROUP BY hash_value

HAVING count(*) > 5;

SELECT address,

       hash_value,

       version_count,

       users_opening,

       users_executing,

       substr(sql_text, 1, 40) "SQL"

  FROM v$sqlarea

 WHERE version_count > 10;

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;

SELECT * FROM x$ksmlru WHERE ksmlrnum > 0;

在不同Oracle Releases中的都会遇到的问题 在不同的release中有些通的会影响shared pool性能的问题，增加每个CPU的处理能力可以减少latch 被持有的时间从而有助于在Oracle 的各个release上减少shared pool竞争。换个更快的CPU般来说会比增加个慢的CPU效果要好。

------------End-----------------------------