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

一、数据库实例优化

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

∙经常发生ORA-04031 错误

Troubleshooting Steps

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

例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'