合 【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;

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

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