合 【MOS】故障排除 Shared Pool优化 (Doc ID 1523934.1) Troubleshooting: Understanding and Tuning the Shared Pool (Doc ID 62143.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 的示例代码：

消除 Literal SQL

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

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

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

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

注意：上面的语句只是一个示范的例子。如果同时执行上面的两种SQL，也许会返回不同的结果。另外，如果系统中有library cache latch争用的问题，上面的语句会导致争用加剧。

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

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

避免 Invalidations

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

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

更多的详细信息，请参考 Note:115656.1 和 Note:123214.1

CURSOR_SHARING 参数 (8.1.6 以上)

(在Oracle8.1.6引入).

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

或者

或者在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

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。