【MOS】故障排除 Shared Pool优化 (Doc ID 1523934.1)

2024-04-17 11:17:34 浏览数 (1)

简介

故障排除：Shared Pool优化 (Doc ID 1523934.1)

Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.1)

适用于:

Oracle Database Cloud Service - 版本 N/A 和更高版本 Oracle Database - Enterprise Edition - 版本 7.0.16.0 到 11.2.0.2 [发行版 7.0 到 11.2] Oracle Database - Standard Edition - 版本 7.0.16.0 到 11.2.0.3 [发行版 7.0 到 11.2] Oracle Cloud Infrastructure - Database Service - 版本 N/A 和更高版本 Oracle Database Backup Service - 版本 N/A 和更高版本 本文档所含信息适用于所有平台

用途

简介

本文档旨在介绍从Oracle 7到Oracle 12c 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 错误

提出问题，得到帮助并分享您的心得

您想同 Oracle 客户，员工及其它业界专家进一步探讨这个问题吗？

在这里 您可以问问题，得到其他人的帮助以及分享您的心得。 更多其它关于数据库性能优化的讨论请到这里.

排错步骤

什么是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;

TIP: 关于在 SQLPLUS 中使用绑定变量的更多信息请参考 Using Bind Variables

例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是否被保持打开。

对于一些OLTP的应用负载，在一些情况下，某些应用的逻辑允许只解析一次但多次执行，下面是 JDBC 和 OCCI 的示例代码：

代码语言：javascript复制
可以降低解析的 JDBC code 示例  (下面的代码是执行100次，但是只解析一次)
-------------------------------------------------------------------------------------------------------------
For JDBC you can use a prepared statement like :

public void do_sql() throws Exception
{
String query =
"select f1 from hh1 where f2 = ? " ;
PreparedStatement pstmt = conn.prepareStatement (query);
for ( int i = 1; i <=100; i  )
{
pstmt.setInt(1, i);
ResultSet rs = pstmt.executeQuery();
rs.next();
String f2 = rs.getString (1);
System.out.println ("Got: "   f2);
}
}

可以降低解析的 OCCI code 示例(下面的代码是执行30次，但是只解析一次)
-------------------------------------------------------------------------------------------------------------
Statement *smst = conn->createStatement();
smst->setSQL("SELECT empno FROM emp WHERE empno = :1");
for(i=0; i<=30; i  ){
smst->setInt(1,i);
smst->executeQuery();
}
消除 Literal SQL

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

代码语言：javascript复制
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
  ;

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

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

在10g以上的版本可以用下面的语句(通过检查 FORCE_MATCHING_SIGNATURE):

代码语言：javascript复制
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，也许会返回不同的结果。另外，如果系统中有library cache latch争用的问题，上面的语句会导致争用加剧。

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

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

避免 Invalidations

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

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

代码语言：javascript复制
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 参数

</parameter:session_cached_cursors></parameter:session_cached_cursors>是一个可以在instance级别或者session级别设置的数值参数:</parameter:session_cached_cursors>

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_SPACE_FOR_TIME 在 10.2.0.5 和 11.1.0.7 之后已经被废弃了。 Document 565424.1 CURSOR_SPACE_FOR_TIME Has Been Deprecated

控制同一个语句不同执行之间一个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被废弃。

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

SHARED_POOL_RESERVED_SIZE 参数

已经有相当多的文档解释过参数<parameter:shared_pool_reserved_size style="font-size: inherit;color: inherit;line-height: inherit;">。这个参数在Oracle 7.1.5被引进，它把shared pool 的一部分预留出来用于较大内存的分配。这个预留区域是从shared pool自身划分出来的。</parameter:shared_pool_reserved_size>

从实践角度来说我们应该把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%也许就不够了。

查看视图<view:v

代码语言：javascript复制

不能识别此Latex公式: shared_pool_reserved> 的FREE_SPACE列可以很容易监控保留区域的使用情况。</view:v

代码语言：javascript复制

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

参考 Document 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'的一个副作用会导致VSESSION_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中

另外一种减少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的视图就是值得注意的。所以我们建议有选择性的运行那些需要访问这种类型视图的语句。特别需要指出的是，查询VSQLAREA会在library cache latch上产生大量的负载，所以一般可以使用对latch访问比较少的vsql做替代——这是因为VSQLAREA的输出是基于shared pool中所有语句的GROUP BY操作，而V

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到一个文件中。

  • 查找literal SQL
代码语言：javascript复制
 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"

另一种方式是按照"plan_hash_value"进行分组：

代码语言：javascript复制
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
代码语言：javascript复制
SELECT SUM(PINS) "EXECUTIONS",
 SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"
 FROM V$LIBRARYCACHE;

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

  • 检查 hash chain 的长度:
代码语言：javascript复制
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值。

  • 检查高版本:
代码语言：javascript复制
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 内存多的语句:
代码语言：javascript复制
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语句或者是一个语句的不同版本。

  • 找到使用 sharable_mem 较多，没有使用绑定变量但是执行计划相同的SQL:
代码语言：javascript复制
select *
from( select plan_hash_value, sum(sharable_mem)/1024/1024 SHRMEM_MB
from v$sqlarea
group by plan_hash_value
order by 2 desc)
where rownum<11;
  • 导致shared pool 内存'aged' out的内存分配
代码语言：javascript复制
SELECT *
  FROM x$ksmlru
WHERE ksmlrnum>0
;

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

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

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

  • 增加每个CPU的处理能力可以减少latch 被持有的时间从而有助于在Oracle 的各个release上减少shared pool竞争。换一个更快的CPU一般来说会比增加一个慢的CPU效果要好。
  • 如果你设置了一个EVENT，不管基于什么原因，请让Oracle support 检查这个event是否会对shared pool 的性能造成影响。
  • 确保Oracle 实例有足够的内存，避免SGA内存被操作系统swap交换出去的风险。

Bug 修复和增强功能

参见以下文档：

Document 2098490.1 WAITEVENT: "latch: shared pool" Reference Note

参考

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:1169017.1 - ANNOUNCEMENT: Deprecating the Cursor_Sharing = 'SIMILAR' Setting BUG:1640583 - ORA-4031 AND CACHE BUFFER CHAIN CONTENTION AFTER UPGRADE TO 8163

NOTE:68955.1 - Init.ora Parameter "_SQLEXEC_PROGRESSION_COST" [Hidden] Reference Note NOTE:73922.1 - Tuning Precompiler Applications BUG:1623256 - IDENTICAL SQL REFERENCING SCHEMA.SEQUENCE.NEXTVAL NOT SHARED BY DIFFERENT USERS BUG:1484634 - ONE INSTANCE OF OPS HANGS

NOTE:751876.1 - DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 BUG:770924 - ORA-600 [17302][9] DOING A QUIT IN SQLPLUS AFTER CONTEXT QUERY

NOTE:61760.1 - Using the Oracle DBMS_SHARED_POOL Package NOTE:62140.1 - Fundamentals of the Large Pool

NOTE:123214.1 - Truncate - Causes Invalidations in the LIBRARY CACHE NOTE:1012046.6 - How to Calculate Your Shared Pool Size NOTE:43600.1 - VIEW: X$KSMLRU - LRU flushes from the shared pool - (7.3 - 8.1) NOTE:94036.1 - Init.ora Parameter "CURSOR_SHARING" Reference Note

BUG:1115424 - CURSOR AUTHORIZATION AND DEPENDENCY LISTS GROW CAUSING LATCH CONTENTENTION BUG:1065010 - PERFORMANCE PROBLEM WITH RECURSIVE LINKS NOTE:32871.1 - ALERT: Library Cache Performance Problems in Oracle Releases 7.1.6 to 7.2.3

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

NOTE:62143.1 - Troubleshooting: Understanding and Tuning the Shared Pool

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

NOTE:115656.1 - Legacy: Wait Scenarios Regarding 'library cache pin' and 'library cache load lock'

pool shared 对象 内存 优化

0 人点赞