性能测试调优方案知识汇总

1、一数据库实例优化、1 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经常

2、发生ORA-04031错误Troubleshooting Steps什么是 shared poo ？Oracle在SGA的一个特定区域中保留SQL语句packages,对象信息以及其它一些内容，这就是大 家熟悉的shared pool。这个共享内存区域是由一个复杂的cache和heap manager构成 的。它需要 解决三个基本问题：每次分配的内存大小是不一致的，从几个字节到上千个字节；因为shared pool的目的是为了最大化共享信息，所以不是每次一个用户用完之后就可以释放这段内存（在传统的 heap man ager方式会遇到这个问题）。内存中的信息可能对于其他session来说是有用

3、的Oracle并不能事先知道这些内容是否会被再次用到；Shared pool中的内容不能被写入到硬盘区域中，这一点和传统cache是不一样的。只有H可重建的信息可以被覆盖，因为他们可以在下次需要时重建。基于这些背景，我们就可以理解shared pool的管理是一件非常复杂的事情。下面的章节列出了一些影响shared pool性能和它相关的latch的关键问题，包括：专用术语Literal SQL一个Literal SQL语句是指在predicate中使用具体值，而不是使用绑定变量，即不同的执行语句使用 的具体值可能是不一样的。例1:应用程序使用了：SELECT * FROM emp WHERE

4、 en ame=CLARK：而不是：SELECT * FROM emp WHERE en ame=:bi nd1;例2:以下语句不用绑定变量但是也不会被认为是literal SQL，因为这个语句可以被多次执行共享。SELECT sysdate FROM dual;例3:如果整个应用都是用相同的值20来检查version的话，那么这个语句可以被认为是可以共享的。SELECT version FROM app_version WHERE versio n2.0;Hard Parse （硬解析）如果一个新的SQL被发起，但是又不在shared pool里面的话，它将被完整的解析一次。例 如： Ora

5、cle必须在shared pool中分配内存，检查句法和语义等等这被称为hard parse,它在CPU使用和latch获取上的都是非常消耗资源的。Soft Parse （软解析）如果一个session发起一个已经在shared pool中的SQL语句并且它可以使用一个当前存在的 版本， 那么这个过程被称为一个soft parsed对于应用来说，它只需请求解析这个语句。完全相同的语句？如果两个SQL语句的含义相同但是没有使用相同的字符，那么Oracle认为它们是不同的语句。比如SCOTT在一个Session中提交的这两个语句：SELECT ENAME from EMP;SELECT en am

6、e from emp;尽管它们实际上是相同的，但是因为大写字母？ E和小写字母5e的区别，他们不会被认为是完全相同的语句。Sharable SQL如果是两个不同的session发起了完全相同的SQL语句，这也不意味着这个语句是可以共享 的。比 如说：用户SCOTT下有一个表EMP，发起了下面的语句：SELECT ENAME from EMP;用户FRED有一个自己的表也叫EMP并且发起相同的语句：SELECT ENAME from EMP;尽管语句完全一样但是由于需要访问的EMP表是不同的对象，所以需要对这条语句产生不同的版本。 有很多条件来判断两个完全一致的SQL文本是不是真的是完全相同（以

7、至于他们可以被共享），包括：语句中引用的所有的对象名必须都被解析成实际相同的对象发起语句的session中的optimizer相关的参数应该一致*绑定变量的类型和长度应该是”相似的”（这里不做详细讨论，但是类型和长度的不同确实会导致语句被分为不同的版本）发起语句的NLS （National Language Support）设置必须相同语句的版本正如之前在Sharable SQL冲描述的，如果两个语句字面上完全相同但是又不能被共享，则会对相同的语句产生不同的version，即版本。如果Oracle要匹配一个包含多个版本的语句，它将不得 不检查每一个版本来看它们是不是和当前被解析的语句完全相同。

8、所以最好用以下方法来避免高版本数（high version count）:*客户端使用的绑定变量最大长度需标准化如果有大量的schema会包含相同名字的对象，那么避免使用一个相同的SQL语句。比女口：SELECT xx FROM MYTABLE ;并且每个用户都有一个自己的MYTABLE的情 况 在 Oracle 8.1 可以将 _SQLEXEC_PROGRESSION_COST 设置成0Library Cache 和 Shared Pool latchesshared pool latch是用来保护从shared pool中分配和释放内存的关键性操作。Library cache latche

9、 （以及 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 在有完整的统

10、计信息并且 SQL语句在predicate （限定条件）中使用具体值时，基于成本的 优化器（CBO）能工作的最好。比较下面的语句：SELECT dist in ct cust_ref FROM orders WHERE total_cost 10000.0; 和SELECT dist in ct cust_ref FROM orders WHERE total_cost :bi ndA;对于第一个语句，CBO可以使用已经收集的histogram来判断是否使用全表扫描比使用TOTAL_COST列上索引扫描快（假设有索引的话）。第二个语句CBO并不知道绑定变量:bindA对 应行数的比例，因为该绑

11、定变量没有一个具体的值以确定执行计划。例：:bindA可以是 0.0 或者 99999999999999999.9。Orders表上两个不同的执行路径的响应时间可能会不同，所以当你需要CBO为你选出最好的执行计划的时候，选用使用literal语句会更好。在一个典型的Decision Support Systems （决策 支持系统）中，重复执行标准语句的时候非常少，所以共享一个语句的几率很小，而且花在Parse上的CPU时间只占每个语句执行时间的非常小一部分，所以更重要的是给optimizer尽可 能详细的信息，而不是缩短解析时间。Sharable SQL如果应用使用了 literal （无共

12、享）SQL，则会严重限制可扩展性和生产能力。在对CPU的需求、library cache和shared pool latch的获取和释放次数方面，新SQL语句的parse成本很 高。 比如：仅仅parse 一个简单的语句就可能需要获取和释放library cache latch 20或者30次。除非它是一个临时的或者不常用的SQL，并且需要让CBO得到尽可能多的信息来生成一个好的执行计划，否则最好让所有的SQL是共享的。减轻Shared Pool负载Parse 一次并执行多次 在OLTP类型的应用中，最好的方法是只让一个语句被解析一次，然后保持这个cursor的打开状态，在需要的时候重复执行它

13、。这样做的结果是每个语句只被Parse 了一次（不管是soft parse 还是hard parse）。显然，总会有些语句很少被执行，所以作为一个打开的cursor维护它们是一种浪费。请注意一个session最多只能使用参数:open_cursors定义的cursor数，保持cursor打开会增 力口总 体open cursors的数量。OCI中开发者能直接控制cursor，在预编译器中，HOLD_CURSOR参数控制cursor是否被保持打 开。消除 Literal SQL如果你有一个现有的应用程序，你可能没法消除所有的literal SQL，但是你还是得设法消除其中一部分会产生问题的语句。

14、从V$SQLAREA视图可能找到适合转为使用绑定变量的语句。下面的查询列出SGA中有大量相似语句的SQL :SELECT substr(sql_text,1,40) SQL,cou nt(*),sum(executi ons) TotExecsFROM v$sqlareaWHERE executio ns 30ORDER BY 2J在10g以上的版本可以用下面的语句 ：SET pages 10000SET linesize 250column FORCE_MATCHING_SIGNATURE format 99999999999999999999999WITH c AS(SELECT FORC

15、E_MATCHING_SIGNATURE,COUNT(*) entFROM v$sqlareaWHERE FORCE_MATCHING_SIGNATURE!=0GROUP BY FORCE_MATCHING_SIGNATUREHAVING COUNT(*) 20)sq AS(SELECT sql_text ,FORCE_MATCHING_SIGNATURE,row_number() over (partitio n BY FORCE_MATCHING_SIGNATURE ORDER BY sqldDESC) pFROM v$sqlarea sWHERE FORCE_MATCHING_SIGNA

16、TURE IN(SELECT FORCE_MATCHING_SIGNATUREFROM c)SELECT sq.sql_text ,sq.FORCE_MATCHING_SIGNATURE,t un shared coun t FROM c, sqWHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATUREAND sq.p =1ORDER BY t DESC对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果 SQL相同，那么SQL语句的exact_matching_signatur

17、e就是相同的。对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果 SQL中有绑定变量， force_matching_signature 就会与 exact_matching_signature 样的生成标准。可以看到，现在 exact_matching_signature 与 force_matching_signature 完全一样了。从force_matching_signature的特性，我们可以想到

18、一个用途，用于查找没有使用绑定变量 的SQL 语句，类似于使用plan_hash_value来查找。注意：如果系统中有library cache latch争用的问题，上面的语句会导致争用加剧。值40,5和30只是示例，这个查询查找前40个字符相同的，只被执行过很少次数，而又全少在shared pool里出现30次的语句。通常来说，literal语句以下面的形式开始，并且每个语句的前 面部分字符是相同的：SELECT col1,col2,col3 FROM table WHERE .注意：在转化literal SQL使用绑定变量时有一定程度的限制。请放心我们已经反复证明转化那些经常执行的语句会

19、消除 shared pool的问题并且能显著提高可扩展性。请查看你的应用中使用的工具的文档来决定如何在语句中使用绑定变量。避免 Invalidations有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上 下文环 境。它包括TRUNCATE,表或索弓I上的ANALYZE或DBMS_STATS.GATHER_XXX关联对象的权 限变更。相对应的cursor会留在SQLAREA中，但是下次被引用时会被完全reload并重新parse,所以会对数据库的整体性能造成影响。下面的查询可以帮我们找到 In validation较多的cursor:SELECT

20、 SUBSTR （sql_text, 1,40） SQL, in validati ons FROM v$sqlareaORDER BY in validatio ns DESC;更多的详细信息，请参考Note:115656.1和Note:123214.1CURSOR_SHARING 参数（816 以上）（在 Oracle8.1.6 引入）.这个参数需要小心使用。如果它被设为FORCE ,那么Oracle会尽可能用系统产生的绑定变量来替换 原来SQL中的literals部分。对于很多仅仅是literal不一样的相似的语句，这会让 它们共享cursor。 这个参数可以在系统级别或者session

21、级别动态设置：ALTER SESSION SET cursor_shari ng = FORCE;或者ALTER SYSTEM SET cursor_shari ng = FORCE; 或者在init.ora中设置注意：因为FORCE会导致系统产生的绑定变量替换literal,优化器（CBO）可能会选择一个不同的执行计划，因为能够产生最好执行计划的litera I值已经不存在了。在Oracle9i （以上），可以设置CURSOR_SHARING=SIMILAR。如果这些语句只是literal部分不同，并且这些literal不会对SQL的含义有影响，或者可能会导致使用不同的执行计划，那么 SIM

22、ILAR会共享这些语句。此增强功能适用于当FORCE会产生一个不同并且不是想要的执行计划时，从而提高了参数CURSOR_SHARING的可用性。设置CURSOR_SHARING=SIMILAR，Oracle会决定哪些literals可以被”安全”的替换成绑定变 量，这样 做的结果是有些SQL在可能产生更好执行计划的时候也不会被共享。关于这个参数的更多详细信息，请参考 Note:94036.1。注意:Similar在Oracle 12中不推荐使用。（译者注：根据Note:1169017.1，Oracle12将会移除cursor_sharing = SIMILAR的设置，而且在11g中就已经不推荐

23、使用了，因为有Adaptive Cursor Shari ng 的新特性）请参考：Docume nt:1169017.1 ANNOUNCEMENT: Deprecati ng the cursor_shari ng = SIMILAR sett ingSESSION_CACHED_CURSORS 参数是一个可以在instanee级别或者session级别设置的数值参数：ALTER SESSION SET session_cached_cursors = NNN; 数值NNN决定在一个session中可以被cached的 cursor的个数。当一个语句被parse的时候，Oracle会首先检查s

24、ession的私有缓存中指向的语句，如果有可被共享的语句版本的话，它就可以被使用。这为经常被parse的语句提供了一个捷径，可以比soft或者hard parse使用更少的CPU和非常少的Latch get。为了被缓冲在session缓存中，同样的语句必须在相同的cursor中被parse 3次，之后一个指向 shared cursor的指针会被添加到你的session缓存中。如果session缓存cursor已达上限，则最近 最少使用的那一个会被替换掉。如果你还没有设置这个参数，建议先设置为50作为初始值。之后查看bstat/estat报告的统计 信息章 节的session cursor c

25、ache hits的值，从这个值可以判断cursor缓存是否有作用。如果有必要的话，可以增加或者减少cursor缓存的值。SESSION_CACHED_CURSORS对于forms经常 被打开和关闭的Oracle Forms应用非常有用。CURSOR_SPACE_FOR_TIME 参数控制同一个语句不同执行之间一个cursor是否部分被保持（pin）住。如果设置其他参数都没效果的话，就值得尝试这个参数。这个参数在有不经常被使用的共享语句，或者有非常多的cursor被pinning / unpinning的时候是有帮助的。（查看视图：v$latch_misses -如果大多 数 latch 等待

26、是因为 cursor 的 pinning 和 unpinning 导致的kglpnc: child和kglupc: child）.你必须保证shared pool对于工作负载来说是足够大的，否则性能会受到严重影响而且最终会产生ORA-4O31 错误。如果你把这个参数设为TRUE,请留意：如果SHARED_POOL对于工作负载来说太小的话更容易产生ORA-4031错误。如果你的应用有 cursor泄漏，那么泄漏的cursor会浪费大量内存并在一段时间的运行之后 对性能产生负面影响。目前已知的设置为true可能会导致的问题：Bug:770924 (Fixed 8061 and 8160) ORA-

27、600 17302 may occurBug:897615 (Fixed 8061 and 8160) Garbage Explain Plan over DBLINKBug: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的次数。如果设成T

28、RUE的话可能会增加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

29、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_P

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

31、wap的发生的情况，因为当swap发生的 时候性能会急剧下降。参考Note:1012046.6来根据工作量计算SHARED_POOL_SIZE需要的大小。_SQLEXEC_PROGRESSION_COST parameter 参数（81.5 以上）这是一个Oracle 8.1.5引入的隐含参数。这里提到它是因为默认设置可能导致SQL共享方面的一些问题。设置成0会避免在shared pool中产生语句高版本的问题。例：在init.ora文件中增加这个参数# _SQLEXEC_PROGRESSION_COST 并且设成 0 来避免 SQL 共享问题 #参考 Note:62143.1 获取更多信息_

32、sqlexec_progressi on _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:73

33、922.1将 cursor 固定（pinning） 在 shared pool 中另外一种减少library cache latch使用的方法是将cursor固定在shared pool中，详见以下文档：Note:130699.1 How to Reduce LI BRARY CACHE LATCH C onten ti on Using a Procedure to KEEP Cursors Executed 10 timesDBMS_SHARED_POOL .KEEP这个存储过程（RDBMS/ADMIN目录下的DBMSPOOL.SQL脚本中有定义）可以用来将对 象 KEEP 到 share

34、d pool 中，DBMS_SHARED_POOL.KEEP 可以KEEP packages, procedures, functions, triggers （7.3+）和 sequences （ + ），在 Note:61760.1 中有完整的描述。通常情况下，建议将那些需要经常使用的package 一直keep在shared pool中。KEEP操作在数据库启动后需要尽快实施，因为在shutdown之后Oracle不会自动重新keep这些对象。注意:在Oracle 7.2之前DBMS_SHARED_POOL.KEEP实际上不会把需要KEEP的对象完整 的放到 shared pool中。所

35、以建议在每一个要被KEEP的package中放一个空的存储过程，在执行完DBMS_SHARED_POOL.KEEP之后再调用一下这个空存储过程来保证对象被完全装 载。这在 7.2之后已经修复了。Flushing （清空）SHARED POOL在使用大量literal SQL的系统中，shared pool随时间推移会产生大量碎片进而导致并发能力的下 降。Flushing shared pool能够使得很多小块碎片合并，所以经常能够在一段时间内恢复系统的性 能。清空之后可能也会产生短暂的性能下降，因为这个操作同时也会把没造成shared pool碎片的共享SQL也清除了。清空shared poo

36、l的命令是：ALTER SYSTEM FLUSH SHARED_POOL;注意：如果显式的使用以上命令，即使是用DBMS_SHARED_POOL.KEEP而被保留的那些对象可能也会被释放掉，包括它们占用的内存。如果是隐式的flush（由于shared pool上的内存压力）这个时候一 kept的对象不会被释放。注意：如果sequenee使用了 cache选项，冲刷shared pool有可能会使sequenee在其范围内产 生不连续的记录。使用 DBMS_SHARED_POOL.KEEP （sequence_name,Q）来保持 sequenee 会 防止这种不连续的情况发生。DBMS_SHA

37、RED_POOL .PURGE也可以不刷新整个shared pool，而只清空其中的单个对象。下面的文档说明了 10g和11g中女 M 可清空 library cache heap。Docume nt:751876.1 DBMS_SHARED_POOL.PURGE Is Not Worki ng On 1020.4使用 V$ 视图（V$SQL 和 V$SQLAREA）注意有一些V$视图需要获取相关的latch来返回查询的数据。用来展示library cache和SQL area的 视图就是值得注意的。所以我们建议有选择性的运行那些需要访问这种类型视图的语句。特别需要指 出的是，查询V$SQLA

38、REA会在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 开

39、始 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被改名为S

40、hared Server。但是对于shared pool产生影响的行为从根本 上说 还是一样的。使用SQL查看Shared Pool问题这一章节展示了一些可以用来帮助找到shared pool中的潜在问题的SQL语句。这些语句的输出最好spool到一个文件中。注意:这些语句可能会使latch竞争加剧，我们在上面的”使用V$视图(V$SQL和V$SQLAREA) above.查找 literal SQLSELECT substr(sql_text,1,4O) SQL,cou nt(*),sum(executi ons) TotExecsFROM v$sqlareaWHERE executio n

41、s 30ORDER BY 2这个语句有助于找到那些经常被使用的literal SQL -请查看上面的”消除Literal SQL检索 Library Cache hit ratioSELECT SUM(PINS) EXECUTIONS,SUM(RELOADS) CACHE MISSES WHILE EXECUTING FROM V$LIBRARYCACHE;如果misses/executions高于1%的话，则需要尝试减少library cache miss的发生。检查hash chain的长度：SELECT hash_value, coun t(*)FROM v$sqlareaGROUP B

42、Y hash_valueHAVING coun t(*) 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 个字节的话，这两个语句会

43、映射出相同的hash值。检查高版本SELECT address, hash_value, version_count , users_ope ning , users_executi ng, substr(sql_text,1,40) SQLFROM v$sqlareaWHERE version_co unt 10在上面的Sharable SQL”章节中，我们已经描述了，一个语句的不同”版本”是当语句的字符完全一致但是需要访问的对象或者绑定变量不一致等等造成的。在Oracle8i的不同版本中因为进度监控的问题也会产生高版本。在这篇文档的前面描述过了，我们可以ffi_SQLEXEC_PROGRE

44、SSION_COST设成0来禁止进度监控产生高版本。找到占用shared pool内存多的语句：SELECT substr(sql_text,1,40) Stmt, coun t(*), sum(sharable_mem) Mem, sum(users_ope ning) Ope n, sum(executi ons) ExecFROM v$sqlGROUP BY substr(sql_text,1,40)HAVING sum(sharable_mem) & MEMSIZEJ这里MEMSIZE取值为shared pool大小的10%，单位是byte。这个语句可以查出占用shared pool很

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

46、。关于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

47、交换出去的风险。例如：在AIX上操作系统的不正确设置可能会导致shared pool问题-参考 Note:316533.1 .Bug修复和增强功能这里列出了主要的影响shared pool的bug和功能增强。Fixe列是4位数字的服务器版本号，表示 在哪个版本上这个问题被修复或者功能被增强一一例如：8062表示在8.062上修复，9000表示这个问题在Oracle9i上修复。BugVersions Fixed DescriptionIdentical SQL referencingBug.16232568-9090008063Bug.1366837-9081719000SCHEMA.SEQUE

48、NCE.NEXTVAL not shared by different usersCursors referencing a fully qualified FUNCTION are not sharedBug 1484634-90806381719000Large row cache can cause long shared pool latch waits (OPS only)Bug.1318267815-909000INSERT AS SELECT may not share SQL when it shouldBug J149820-81780628162817。ENHANCEMEN

49、T: Reduced latch gets purging from shared poolBug 1150143-817806281628170ENHANCEMENT: Delay purge when bind mismatchNHANCEMENT: Reduce need to get PARENT libraryBug.1258708-81781708163Bug.13485018-81781708163Bug.1357233815-81781708162Bug.1193003 b 15-81781708162Bug.1210242815-8178170ache latchVIEW r

50、efresh unnecessarily invalidates shared cursorsALTER SESSION FORCE PARALLEL PQ/DML/DDL doesnot share recursive SQLCursors may not be shared in 8.1 when they should beCursors not shared if both TIMED STATISTICS andSQL TRACE are enabled8060Bug.9861497-8168160ENHANCEMENT: More freelists for shared pool

51、 memory chunks (reduced latch contention)Shared pool memory for views higher ifBug.858015815-8168160QUERY REWRITE ENABLED setBug. 918002815-81681518160Cursors are not shared if SQL_TRACE orTIMED_STATISTICS is TRUEBug.888551815-81681518160TIMED_STATISTICS can affect cursor sharing / Dumpfrom EXPLAIN

52、ornable/disabl 3QL_TRACEBug,10650108-817806281628170Access to DC_HISTOGRAM_DEFS from Remote Queries can impact shared pool performan ce.BugJ 115424书17806281628170Cursor authorization and dependency lists too long - can impact shared poolBug.1131711803-8062 8062SQL from PLSQL using NUMERIC binds may

53、not be8150shared when it shouldBug.139760381782ORA-4031 / SGA leak of PERMANENT memory for buffer handlesBug.16405838168171ORA-4031 due to leak / cache buffer chain contention from AND-EQUAL access8200历史记录这里的记录是关于Oracle7.3之前的版本的，只是为了完整性的原因被包含在本文档中：在7.3中，PLSQL被增强为可使用页执行编码以减少shared pool大量内存分配的数量,并且减少使

54、用KEEP的需求。 Oracle 7.1.6到7.2.3有很多已知问题。请查看Note:32871.1从 Oracle 7.1 至 U 7.2，library cache 的 latch 机制发生了改变。* 一些历史bug:BugVersionsFixedDescriptionBug.59695380-818044 8052 80608150Excessive shared pool fragmentation due to 2K context area chunk size.Bug.724620700-8147344 8043 80528060Select from VIEW now us

55、es less shared memory (less latch gets)Bug.6334987-8157343 8043 80508150Selecting from some V$ views can make statements unsharableBug.6258067X-8067343 8042 80518060 8150Cursor not shared for a VIEW using FUNCTION / with DBMS_SQLBug:5207087X-8047336 7342 8040Better handling of small memory chunks in

56、 theSGAReferencesNOTE:43600.1 - VIEW: X$KSMLRU - LRU flushes from the shared pool - (7.3 - 8.1)NOTE:61760.1 - Us ing the Oracle DBMS_SHARED_POOL PackageNOTE:94036.1 - Ini t.ora Parameter CURSOR_SHARING Refere nee NoteBUG:1065010 - PERFORMANCE PROBLEM WITH RECURSIVE LINKSBUG:1115424 - CURSOR AUTHORIZ

57、ATION AND DEPENDENCY LISTS GROWCAUSING LATCH CONTENTENTIONHidde nNOTE:68955.1 - In it.ora Parameter H_SQLEXEC_PROGRESSION_COSTRefere nee NoteNOTE:73922.1 - Tuning Precompiler Applicatio nsNOTE:751876.1 - DBMS_SHARED_POOL.PURGE Is Not Worki ng On BUG:1366837 - CURSOR NOT SHARED FOR TABLES INVOKING A

58、FUNCTIONBUG:1484634 - ONE INSTANCE OF OPS HANGSBUG:1623256 - IDENTICAL SQL REFERENCING SCHEMA.SEQUENCE.NEXTVALNOT SHARED BY DIFFERENT USERSBUG:1640583 - ORA-4031 AND CACHE BUFFER CHAIN CONTENTION AFTERUPGRADE TO 8163NOTE:62140.1 - Fun dame ntals of the Large PoolNOTE:62143.1 - Troubleshooti ng: Tuni

59、ng the Shared Pool and Tuning Library Cache Latch Conten tio nBUG:625806 - CURSOR NOT SHARED FOR VIEWS INVOKING A FUNCTIONBUG:633498 - STATEMENTS IN SHARED POOL DONT GET REUSED AFTERSELECTING FROM V$OPEN_CURSORBUG:897615 - EXPLAIN PLAN OVER DBLINK PUTS GARBAGE IN THE PLANTABLENOTE:115656.1 - WAIT SC

60、ENARIOS REGARDING LIBRARY CACHE PIN ANDLIBRARY CACHE LOAD LOCKNOTE:1169017.1 - ANNOUNCEMENT:Deprecating the cursor_sharing = SIMILARsetti ngNOTE:123214.1 - Truncate - Causes In validatio ns in the LIBRARY CACHENOTE:1012046.6 -How to Calculate Your Shared Pool SizeNOTE:32871.1 - ALERT: Library Cache