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

25/25性能测试调优方案知识汇总一、数据库实例优化

1.oracle_SharedPool优化和LibraryCacheLatch冲突优化

简介

本文档旨在介绍从Oracle7到Oracle11gsharedpool调优的关键问题。特别对于存在下列问题的系统非常重要：

?librarycachelatch/es或者latch:librarycache之类的Latch争用

?sharedpoollatch或者latch:sharedpool之类的Latch争用

?高CPU解析时间

?V$LIBRARYCACHE中的高reloads

?多版本的cursors

?大量的parsecall

?经常发生ORA-04031错误

TroubleshootingSteps

什么是sharedpool？

Oracle在SGA的一个特定区域中保留SQL语句,packages,对象信息以及其它一些内容，这就是大家熟悉的sharedpool。这个共享内存区域是由一个复杂的cache和heapmanager构成的。它需要解决三个基本问题：

1.每次分配的内存大小是不一致的，从几个字节到上千个字节；

2.因为sharedpool的目的是为了最大化共享信息，所以不是每次一个用户用完之后就可

以释放这段内存（在传统的heapmanager方式会遇到这个问题）。内存中的信息可能对于其他session来说是有用的——Oracle并不能事先知道这些内容是否会被再次用到；

3.Sharedpool中的内容不能被写入到硬盘区域中，这一点和传统cache是不一样的。只有

―可重建‖的信息可以被覆盖，因为他们可以在下次需要时重建。

基于这些背景，我们就可以理解sharedpool的管理是一件非常复杂的事情。下面的章节列

出了一些影响sharedpool性能和它相关的latch的关键问题，包括：

专用术语

LiteralSQL

一个LiteralSQL语句是指在predicate中使用具体值，而不是使用绑定变量,即不同的执行语句使用的具体值可能是不一样的。

例1：应用程序使用了：

SELECT*FROMempWHEREename='CLARK';

而不是:

SELECT*FROMempWHEREename=:bind1;

例2:以下语句不用绑定变量但是也不会被认为是literalSQL，因为这个语句可以被多次执

行共享。

SELECTsysdateFROMdual;

例3:如果整个应用都是用相同的值'2.0'来检查'version'的话，那么这个语句可以被认为是可以共享的。

SELECTversionFROMapp_versionWHEREversion>2.0;

HardParse（硬解析）

如果一个新的SQL被发起，但是又不在sharedpool里面的话，它将被完整的解析一次。例如：Oracle必须在sharedpool中分配内存，检查句法和语义等等……这被称为hardparse，它在CPU使用和latch获取上的都是非常消耗资源的。

SoftParse（软解析）

如果一个session发起一个已经在sharedpool中的SQL语句并且它可以使用一个当前存在的版本，那么这个过程被称为一个'softparse'。对于应用来说，它只需请求解析这个语句。

完全相同的语句?

如果两个SQL语句的含义相同但是没有使用相同的字符，那么Oracle认为它们是不同的语句。比如SCOTT在一个Session中提交的这两个语句：

SELECTENAMEfromEMP;

SELECTenamefromemp;

尽管它们实际上是相同的，但是因为大写字母?E‘和小写字母'e'的区别，他们不会被认为是完全相同的语句。

SharableSQL

如果是两个不同的session发起了完全相同的SQL语句，这也不意味着这个语句是可以共享的。比如说：用户SCOTT下有一个表EMP，发起了下面的语句：

SELECTENAMEfromEMP;

用户FRED有一个自己的表也叫EMP并且发起相同的语句:

SELECTENAMEfromEMP;

尽管语句完全一样但是由于需要访问的EMP表是不同的对象，所以需要对这条语句产生不同的版本。有很多条件来判断两个完全一致的SQL文本是不是真的是完全相同（以至于他们可以被共享），包括：

?语句中引用的所有的对象名必须都被解析成实际相同的对象

?发起语句的session中的optimizer相关的参数应该一致

?绑定变量的类型和长度应该是"相似的"

(这里不做详细讨论，但是类型和长度的不同确实会导致语句被分为不同的版本)

?发起语句的NLS(NationalLanguageSupport)设置必须相同

语句的版本

正如之前在'SharableSQL'中描述的，如果两个语句字面上完全相同但是又不能被共享，则会对相同的语句产生不同的'version'，即版本。如果Oracle要匹配一个包含多个版本的语句，它将不得不检查每一个版本来看它们是不是和当前被解析的语句完全相同。所以最好用以下方法来避免高版本数（highversioncount）：

?客户端使用的绑定变量最大长度需标准化

?如果有大量的schema会包含相同名字的对象，那么避免使用一个相同的SQL语句。比如：SELECTxxFROMMYTABLE;并且每个用户都有一个自己的MYTABLE的情况

?在Oracle8.1可以将_SQLEXEC_PROGRESSION_COST设置成'0'

LibraryCache和SharedPoollatches

sharedpoollatch是用来保护从sharedpool中分配和释放内存的关键性操作。

Librarycachelatche(以及Oracle7.1中的librarycachepinlatch)是用来保护librarycache中的操作。

所有的这些Latch都是潜在的资源争用的对象，latchgets发生的次数直接受到sharedpool中活动（activity）个数的影响，特别是parse操作。任何减少latchgets或者sharedpool中活动（activity）个数的尝试都有助于提高性能和可扩展性。

LiteralSQL和SharedSQL的比较

这一个小章节中描述了literalSQL和sharableSQL各自的优点：

LiteralSQL

在有完整的统计信息并且SQL语句在predicate（限定条件）中使用具体值时，基于成本的优化器(CBO)能工作的最好。比较下面的语句：

SELECTdistinctcust_refFROMordersWHEREtotal_cost20

)

,

sqAS

(SELECTsql_text,

FORCE_MATCHING_SIGNATURE,

row_number()over(partitionBYFORCE_MATCHING_SIGNATUREORDERBYsql_idDESC)p

FROMv$sqlareas

WHEREFORCE_MATCHING_SIGNATUREIN

(SELECTFORCE_MATCHING_SIGNATURE

FROMc

)

)

SELECTsq.sql_text,

sq.FORCE_MATCHING_SIGNATURE,

http://./doc/f06905589b89680202d825aa.htmlt"unsharedcount"

FROMc,

WHEREsq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE

ANDsq.p=1

ORDERBYhttp://./doc/f06905589b89680202d825aa.htmltDESC

对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均为大写（不包括字符常量）后，如果SQL相同，那么SQL语句的exact_matching_signature就是相

同的。对SQL语句，去掉重复的空格（不包括字符常量），将大小写转换成相同，比如均

为大写（不包括字符常量），然后去掉SQL中的常量，如果SQL相同，那么SQL语句的force_matching_signature就是相同的。但是例外的情况是：如果SQL中有绑定变量，

force_matching_signature就会与exact_matching_signature一样的生成标准。

可以看到，现在exact_matching_signature与force_matching_signature完全一样了。

从force_matching_signature的特性，我们可以想到一个用途，用于查找没有使用绑定变量

的SQL语句，类似于使用plan_hash_value来查找。

注意：如果系统中有librarycachelatch争用的问题，上面的语句会导致争用加剧。

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

语句的前面部分字符是相同的：

"SELECTcol1,col2,col3FROMtableWHERE..."

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

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

避免Invalidations

有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上

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

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

SELECTSUBSTR(sql_text,1,40)"SQL",

invalidations

FROMv$sqlarea

ORDERBYinvalidationsDESC;

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

CURSOR_SHARING参数(8.1.6以上)

(在Oracle8.1.6引入).

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

ALTERSESSIONSETcursor_sharing=FORCE;

ALTERSYSTEMSETcursor_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在Oracle12中不推荐使用。（译者注：根据Note:1169017.1，Oracle12将会移除cursor_sharing=SIMILAR的设置，而且在11g中就已经不推荐使用了，因为有AdaptiveCursorSharing的新特性）请参考:Document:1169017.1ANNOUNCEMENT:Deprecatingthecursor_sharing=SIMILARsetting

SESSION_CACHED_CURSORS参数

是一个可以在instance级别或者session级别设置的数值参数:

ALTERSESSIONSETsession_cached_cursors=NNN;

数值NNN决定在一个session中可以被'cached'的cursor的个数。

当一个语句被parse的时候，Oracle会首先检查session的私有缓存中指向的语句，如果有可被共享的语句版本的话，它就可以被使用。这为经常被parse的语句提供了一个捷径，可以比soft或者hardparse使用更少的CPU和非常少的Latchget。

为了被缓冲在session缓存中，同样的语句必须在相同的cursor中被parse3次，之后一个指向sharedcursor的指针会被添加到你的session缓存中。如果session缓存cursor已达上限，则最近最少使用的那一个会被替换掉。

如果你还没有设置这个参数，建议先设置为50作为初始值。之后查看bstat/estat报告的统计信息章节的'sessioncursorcachehits'的值，从这个值可以判断cursor缓存是否有作用。如果有必要的话，可以增加或者减少cursor缓存的值。SESSION_CACHED_CURSORS对于forms经常被打开和关闭的OracleForms应用非常有用。

CURSOR_SPACE_FOR_TIME参数

控制同一个语句不同执行之间一个cursor是否部分被保持（pin）住。如果设置其他参数都没效果的话，就值得尝试这个参数。这个参数在有不经常被使用的共享语句，或者有非常多的cursor被pinning/unpinning的时候是有帮助的。(查看视图：v$latch_misses–如果大多数latch等待是因为cursor的pinning和unpinning导致的"kglpnc:child"和"kglupc:child").你必须保证sharedpool对于工作负载来说是足够大的，否则性能会受到严重影响而且最终会产生ORA-4031错误。

如果你把这个参数设为TRUE，请留意:

o

?如果SHARED_POOL对于工作负载来说太小的话更容易产生ORA-4031错误。

?如果你的应用有cursor泄漏，那么泄漏的cursor会浪费大量内存并在一段时间的运行之后对性能产生负面影响。

?目前已知的设置为true可能会导致的问题:

?

?Bug:770924(Fixed8061and8160)ORA-600[17302]mayoccur

?Bug:897615(Fixed8061and8160)GarbageExplainPlanoverDBLINK

?Bug:1279398(Fixed8162and8170)ORA-600[17182]fromALTERSESSIONSETNLS...

CLOSE_CACHED_OPEN_CURSORS参数

这个参数已经在Oracle8i被废弃。

控制当一个事务提交时是否PL/SQLcursor被关闭。默认值是FALSE，该设置在不同commits之后保持PL/SQLcursor打开以减少hardparse的次数。如果设成TRUE的话可能会增加SQL在不用的时候被从sharedpool中清除出去的可能性。

SHARED_POOL_RESERVED_SIZE参数

已经有相当多的文档解释过参数。这个参数在Oracle7.1.5被引进，它把sharedpool的一部分预留出来用于较大内存的分配。这个预留区域是从sharedpool自身划分出来的。

从实践角度来说我们应该把SHARED_POOL_RESERVED_SIZE设成

SHARED_POOL_SIZE的10%，除非sharedpool非常大或

者SHARED_POOL_RESERVED_MIN_ALLOC被设得小于默认值：

?如果sharedpool非常大的话，设成10%会浪费很多内存因为可能设成几MB就够用了。?如果SHARED_POOL_RESERVED_MIN_ALLOC被设的较小，则很多的空间请求都会符合从保留空间中分配的条件，那么10%也许就不够了。

查看视图的FREE_SPACE列可以很容易监控保留区域的使用情况。

SHARED_POOL_RESERVED_MIN_ALLOC参数

在Oracle8i这个参数是隐藏的.

尽管有些情况下SHARED_POOL_RESERVED_MIN_ALLOC设成4100或者4200可能对缓解较大压力下的sharedpool的冲突有帮助，但是在大多数情况下应保持默认值。

SHARED_POOL_SIZE参数

控制sharedpool自己的大小，它能对性能造成影响。如果太小，则共享的信息会被从共享池中交换出去，过一阵子有需要被重新装载（重建）。如果literalSQL使用较多而且sharedpool又很大，长时间使用后内部内存freelist上会产生大量小的内存碎片，使得sharedpoollatch被持有的时间变长，进而导致性能问题。在这种情况下，较小的sharedpool也许比较大的sharedpool好。因为Bug:986149的改进，这个问题在8.0.6和8.1.6以上版本被大大减少了。.

注意:一定要避免由于sharedpool设置过大进而导致的swap的发生的情况，因为当swap

发生的时候性能会急剧下降。

参考Note:1012046.6来根据工作量计算SHARED_POOL_SIZE需要的大小。

_SQLEXEC_PROGRESSION_COSTparameter参数(8.1.5以上)

这是一个Oracle8.1.5引入的隐含参数。这里提到它是因为默认设置可能导致SQL共享方面的一些问题。设置成0会避免在sharedpool中产生语句高版本的问题。

例:在init.ora文件中增加这个参数

#_SQLEXEC_PROGRESSION_COST并且设成0来避免SQL共享问题

#参考Note:62143.1获取更多信息

_sqlexec_progression_cost=0

注意设成'0'的一个副作用会导致V$SESSION_LONGOPS视图中不记录长时间运行的查询。

参考Note:68955.1获取关于这个参数的更多信息。

预编译器的HOLD_CURSOR和RELEASE_CURSOR选项

当使用Oracle预编译器预编译程序的时候，sharedpool的行为可以通过参数

RELEASE_CURSOR和HOLD_CURSOR来控制。这些参数可以决定当cursor执行完毕之后librarycache和sessioncache中cursor的状态。

关于这个参数的更多信息，请参考Note:73922.1

将cursor固定（pinning）在sharedpool中

另外一种减少librarycachelatch使用的方法是将cursor固定在sharedpool中，详见以下文档：

Note:130699.1HowtoReduce'LIBRARYCACHELATCH'ContentionUsingaProceduretoKEEPCursorsExecuted>10times

DBMS_SHARED_POOL.KEEP

这个存储过程(RDBMS/ADMIN目录下的DBMSPOOL.SQL脚本中有定义)可以用来将对象KEEP到sharedpool中，DBMS_SHARED_POOL.KEEP可以'KEEP'packages,procedures,functions,triggers(7.3+)和sequences(+)，在Note:61760.1中有完整的描述。

通常情况下，建议将那些需要经常使用的package一直keep在sharedpool中。KEEP操作在数据库启动后需要尽快实施，因为在shutdown之后Oracle不会自动重新keep这些对象。注意:在Oracle7.2之前DBMS_SHARED_POOL.KEEP实际上不会把需要KEEP的对象完整的放到sharedpool中。所以建议在每一个要被KEEP的package中放一个空的存储过程，在执行完DBMS_SHARED_POOL.KEEP之后再调用一下这个空存储过程来保证对象被完全装载。这在7.2之后已经修复了。

Flushing（清空）SHAREDPOOL

在使用大量literalSQL的系统中，sharedpool随时间推移会产生大量碎片进而导致并发能力的下降。Flushingsharedpool能够使得很多小块碎片合并，所以经常能够在一段时间内恢复系统的性能。清空之后可能也会产生短暂的性能下降，因为这个操作同时也会把没造成sharedpool碎片的共享SQL也清除了。清空sharedpool的命令是：

ALTERSYSTEMFLUSHSHARED_POOL;

注意：如果显式的使用以上命令，即使是用DBMS_SHARED_POOL.KEEP而被保留的那些对象可能也会被释放掉，包括它们占用的内存。如果是隐式的flush（由于sharedpool上的内存压力）这个时候―kept"的对象不会被释放。

注意:如果sequence使用了cache选项，冲刷sharedpool有可能会使sequence在其范围内产生不连续的记录。使用DBMS_SHARED_POOL.KEEP('sequence_name','Q')来保持sequence会防止这种不连续的情况发生。

DBMS_SHARED_POOL.PURGE

也可以不刷新整个sharedpool，而只清空其中的单个对象。下面的文档说明了10g和11g中如何清空librarycacheheap。

Document:751876.1DBMS_SHARED_POOL.PURGEIsNotWorkingOn

使用V$视图(V$SQL和V$SQLAREA)

注意有一些V$视图需要获取相关的latch来返回查询的数据。用来展示librarycache和SQLarea的视图就是值得注意的。所以我们建议有选择性的运行那些需要访问这种类型视图的语句。特别需要指出的是，查询V$SQLAREA会在librarycachelatch上产生大量的负载，所以一般可以使用对latch访问比较少的v$sql做替代——这是因为V$SQLAREA的输出是基于sharedpool中所有语句的GROUPBY操作，而V$SQL没有用GROUPBY操作。

MTS,SharedServer和XA

由于多线程服务器(MTS)的UserGlobalArea(UGA)是存放在sharedpool中的，所以会增加sharedpool的负载。在Oracle7上的XAsession也会产生同样的问题，因为他们的UGA也是在sharedpool里面（在Oracle8/8i开始XAsession不再把UGA放到sharedpool中）。在Oracle8中LargePool可以被用来减少MTS对sharedpool活动的影响——但是，LargePool中的内存分配仍然会使用"sharedpoollatch"。对LargePool的描述请参考Note:62140.1.

使用dedicateconnections(专有连接)替代MTS可以使UGA在进程私有内存中分配而不是sharedpool。私有内存分配不会使用"sharedpoollatch"，所以在有些情况下从MTS切换到专有连接可以帮助减少竞争。

在Oracle9i中，MTS被改名为"SharedServer"。但是对于sharedpool产生影响的行为从根本上说还是一样的。

使用SQL查看SharedPool问题

这一章节展示了一些可以用来帮助找到sharedpool中的潜在问题的SQL语句。这些语句的输出最好spool到一个文件中。

注意:这些语句可能会使latch竞争加剧，我们在上面的"使用V$视图(V$SQL和

V$SQLAREA)"above.

?查找literalSQL

SELECTsubstr(sql_text,1,40)"SQL",

count(*),

sum(executions)"TotExecs"

FROMv$sqlarea

WHEREexecutions30

ORDERBY2

;

这个语句有助于找到那些经常被使用的literalSQL–请查看上面的"消除LiteralSQL"

?检索LibraryCachehitratio

SELECTSUM(PINS)"EXECUTIONS",

SUM(RELOADS)"CACHEMISSESWHILEEXECUTING"

FROMV$LIBRARYCACHE;

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

?检查hashchain的长度:

SELECThash_value,count(*)

FROMv$sqlarea

GROUPBYhash_value

HAVINGcount(*)>5

;

这个语句正常应该返回0行。如果有任何HASH_VALUES存在高的count（两位数的）的话，你需要查看是否是bug的影响或者是literalSQL使用了不正常的形式。建议进一步列出所有有相同HASH_VALUE的语句。例如：

SELECTsql_textFROMv$sqlareaWHEREhash_value=;

如果这些语句看起来一样，则查询V$SQLTEXT去找完整的语句。有可能不同的SQL文本会映射到相同的hash值，比如：在7.3中，如果一个值在语句中出现2次而且中间正好间隔32个字节的话，这两个语句会映射出相同的hash值。

?检查高版本:

SELECTaddress,hash_value,

version_count,

users_opening,

users_executing,

substr(sql_text,1,40)"SQL"

FROMv$sqlarea

WHEREversion_count>10

;

在上面的"SharableSQL"章节中，我们已经描述了，一个语句的不同"版本"是当语句的字符完全一致但是需要访问的对象或者绑定变量不一致等等造成的。在Oracle8i的不

同版本中因为进度监控的问题也会产生高版本。在这篇文档的前面描述过了，我们可以把_SQLEXEC_PROGRESSION_COST设成'0'来禁止进度监控产生高版本。

?找到占用sharedpool内存多的语句:

SELECTsubstr(sql_text,1,40)"Stmt",count(*),

sum(sharable_mem)"Mem",

sum(users_opening)"Open",

sum(executions)"Exec"

FROMv$sql

GROUPBYsubstr(sql_text,1,40)

HAVINGsum(sharable_mem)>

这里MEMSIZE取值为sharedpool大小的10%，单位是byte。这个语句可以查出占用sharedpool很大内存的那些SQL，这些SQL可以是相似的literal语句或者是一个语句的不同版本。

?导致sharedpool内存'aged'out的内存分配

SELECT*

FROMx$ksmlru

WHEREksmlrnum>0

;

注意:因为这个查询在返回不超过10行记录后就会消除X$KSMLRU的内容，所以请用SPOOL保存输出的内容。X$KSMLRU表显示从上一次查询该表开始，哪些内存分配操作导致了最多的内存块被清除出sharedpool。有些时候，这会有助

于找到那些持续的请求分配空间的session或者语句。如果一个系统表现很好而且共享SQL使用得也不错，但是偶尔会变慢，这个语句可以帮助找到原因。关于X$KSMLRU的更多信息请查看Note:43600.1。

在不同OracleReleases中的都会遇到的问题

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

?增加每个CPU的处理能力可以减少latch被持有的时间从而有助于在Oracle的各个release上减少sharedpool竞争。换一个更快的CPU一般来说会比增加一个慢的CPU效果要好。

?如果你设置了一个EVENT，不管基于什么原因，请让Oraclesupport检查这个event是否会对sharedpool的性能造成影响。

?确保Oracle实例有足够的内存，避免SGA内存被操作系统swap交换出去的风险。

例如:在AIX上操作系统的不正确设置可能会导致sharedpool问题-参

考Note:316533.1.

Bug修复和增强功能

这里列出了主要的影响sharedpool的bug和功能增强。'Fixed'列是4位数字的服务器版本号，表示在哪个版本上这个问题被修复或者功能被增强——例如：8062表示在上修复，9000表示这个问题在Oracle9i上修复。

?在7.3中，PLSQL被增强为可使用页执行编码以减少sharedpool大量内存分配的数量，并且减少使用KEEP的需求。

?Oracle7.1.6到7.2.3有很多已知问题。请查看Note:32871.1

?从Oracle7.1到7.2，librarycache的latch机制发生了改变。

?一些历史bug:

NOTE:94036.1-Init.oraParameter"CURSOR_SHARING"ReferenceNote

BUG:1065010-PERFORMANCEPROBLEMWITHRECURSIVELINKS

BUG:1115424-CURSORAUTHORIZATIONANDDEPENDENCYLISTSGROWCAUSINGLATCHCONTENTENTION

NOTE:68955.1-Init.oraParameter"_SQLEXEC_PROGRESSION_COST"[Hidden]ReferenceNote

NOTE:73922.1-TuningPrecompilerApplications

NOTE:751876.1-DBMS_SHARED_POOL.PURGEIsNotWorkingOn

BUG:1366837-CURSORNOTSHAREDFORTABLESINVOKINGAFUNCTION

BUG:1484634-ONEINSTANCEOFOPSHANGS

BUG:1623256-IDENTICALSQLREFERENCINGSCHEMA.SEQUENCE.NEXTVALNOTSHAREDBYDIFFERENTUSERS

BUG:1640583-ORA-4031ANDCACHEBUFFERCHAINCONTENTIONAFTERUPGRADETO8163

NOTE:62140.1-FundamentalsoftheLargePool

NOTE:62143.1-Troubleshooting:TuningtheSharedPoolandTuningLibraryCacheLatchContention

BUG:625806-CURSORNOTSHAREDFORVIEWSINVOKINGAFUNCTION

BUG:633498-STATEMENTSINSHAREDPOOLDON'TGETREUSEDAFTERSELECTINGFROMV$OPEN_CURSOR

BUG:897615-EXPLAINPLANOVERDBLINKPUTSGARBAGEINTHEPLANTABLE

NOTE:115656.1-WAITSCENARIOSREGARDINGLIBRARYCACHEPINANDLIBRARYCACHELOADLOCK

NOTE:1169017.1-ANNOUNCEMENT:Deprecatingthecursor_sharing='SIMILAR'setting

NOTE:123214.1-Truncate-CausesInvalidationsintheLIBRARYCACHE

NOTE:1012046.6-HowtoCalculateYourSharedPoolSize

NOTE:32871.1-ALERT:LibraryCachePerformanceProblemsinOracleReleases7.1.6to

7.2.3

NOTE:316533.1-AIX:Databaseperformancegetsslowerthelongerthedatabaseisrunning

BUG:770924-ORA-600[17302][9]DOINGAQUITINSQLPLUSAFTERCONTEXTQUERY

2.数据字典高速缓存的优化

Oracle是一个高性能数据库软件。用户可以通过参数的调整，达到性能的优化。性能优化主要分为两部分：一是数据库管理员通过对系统参数的调整达到优化的目的；二是开发人员通过对应用程序的优化达到调整的目的。在此，

仅就系统参数的调整进行探讨，而不涉及应用程序的优化。对系统参数的调整，可以分为以下几个部分：调整内存分配系统全局区（SGA）是一个分配给Oracle包含Oracle数据库实例控制信息的内存段。SGA的大小对系统性能的影响极大，其缺省参数设置只适用于配置很低的计算机，不适应收入系统现有设备的需要。这些参数若不作调整，会对系统资源造成巨大浪费。就收入系统的Alpha1200而言，SGA的大小以160兆左右为宜。初始化参数文件中的一些参数对SGA的大小有决定性的影响。参数DB－BLOCK－BUFFERS（SGA中存储区高速缓存的缓冲区数目），参数SHARED－POOL－SIZE（分配给共享SQL区的字节数），是SGA大小的主要影响者。DB－BLOCK－BUFFERS参数是SGA大小和数据库性能的最重要的决定因素。该值较高，可

以提高系统的命中率，减少I/O。每个缓冲区的大小等于参数DB－BLOCK－SIZE的大小。Oracle数据库块以字节表示大小。OracleSGA区共享池部分由库高速缓存、字典高速缓存及其他一些用户和服务器会话信息组成，共享池是最大的消耗成分。调整SGA区各个结构的大小，可以极大地提高系统的性能。区各个结构的大小，可以极大地提高系统的性能。调整调整LibraryCache库高速缓存（LibraryCache）中包含私用和共享SQL区和PL/SQL区。调整SGA的重要问题是确保库高速缓存足够大，以使Oracle能在共享池中保持分析和执行语句，提高语查询V$LIBRARYCACHE数据字典视图句分析和执行效率，降低资源消耗。通过比较LibraryCache的命中率来决定它的大小。（其中，pins表示高速缓存命中率，reloads表示高速缓存失败）SQL〉SELECTSUM(pins),SUM(reloads)FROMv$librarycache;如果

sum(reload)/sum(pins)≈0，说明LibraryCache的命中率比较合适，若大于1，则需要增加共享池（SHARED－POOL－SIZE）的大小（在初始化参数文件中）。

调整数据字典高速缓存（调整数据字典高速缓存（DictionaryCache））数据字典高速缓存包括了有关数据库的结构、用户、实体信息等。数据

字典的命中率对系统有很大的影响。命中率的计算中，getmisses表示失败次数，gets表示成功次数。查询V$ROWCACHE表：

SQLSELECT(1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100FROMv$rowcache;如果该值90%，说明命中率合适。否则，应增大共享池的大小。调整数据库缓冲区高速缓存Oracle在运行期间向数据库高速缓存读写数据，高速缓存命中表示信息已在内存中，高速缓存失败意味着Oracle必需进行磁盘I/O。保持高速缓存失败率最小的关键是确保高速缓存的大小。初始化参数DB－BLOCK－BUFFERS控制数据库缓冲区高速缓存的大小。可通过查询V$SYSSTAT命中率，以确定是否应当增加DB－BLOCK－BUFFERS的值。SQLSELECTname,valueFROMV$SYSSTATWHEREnameIN(’dbblockgets’,’consistentgets’,’physicalreads’);通过查询结果命中率

=1-physicalreads/(dbblockgets+consistentgets)如果命中率0.6～0.7，则应增大DB－BLOCK－BUFFERS。

调整磁盘I/O磁盘I/O是系统性能的瓶颈，解决好磁盘I/O，可明显提高性能。通过查询V$FILESTAT可以知道每个物理文件的使用频率（phyrds表示每个数据文件读的次数，phywrts表示每个数据文件写的

次数）SQLSELECTname,phyrds,phywrtsFROMv$datafileWHEREdf.file#=fs.file#;对于使用频率较高的物理文件,可以采用以下策略:将I/O尽可能平均分配在尽可能多的磁盘上。为表和索引建立不同的表空间。将数据文件与重做日志文件分离在不同的磁盘上。df,v$filestatfs

减少不经OracleSERVER的磁盘I/O。调整竞争当多个进程对相同的资源发出申请时，产生竞争。修改Process参数该参数定义可以同时连接到Oracle数据库的最大进程数，缺省值为50。注意，Oracle的后台进程也包括在此数目中，建议将该值改为200。减少调度进程的竞争减少调度进程的竞争，通过查询v$dispatcher表来判定调度进程的竞争SQLSELECTnetwork,sum(busy)/sum(busy)+sum(idle)GROUPBYnetwork;FROMv$dispatcher

如果某种协议忙的比率超过50%，应增加MTS－DISPATCHERS的值。减少多线程服务进程竞争首先查询V$SYSSTAT表判定是否发生多线程服务进程竞争:SQLSELECTDECODE(totalq,0,’NoFROMv$sysstatWHEREtype=’common’;request’,wait/totalq||’hunderthsofseconds’)

如果共享服务进程数量已达到初始化参数文件中MTS－MAX－SERVERS指定的最大值，但应用运行时，平均请求等待时间仍持续增长，那么，应加大MTS－MAX－SERVERS的值。减少重做日志缓冲区竞争通过查询V$SYSSTAT表判定redolog文件缓冲区是否足够。SQLSELECTname,valueFROMv$sysstatWHEREname=’redologspacerequest’;

此处value的值应接近于0，否则，应增大初始化参数文件的LOG－BUFFEQS的值。

减少回退段竞争回退段对性能也有影响，根据事物大小情况来分配合适的回退段。

首先判定回退段的数量能否满足系统运行的需要：查询V$WAITSTAT表与V$SYSSTAT表SQLSELECTclass,countFROMv$waitstatWHEREclassIN(’systemundoheader’,systemundoblock’,’undoheader’,’undoblock’);SQLSELECTsum(value)FROMv$sysstatWHEREnameIN(’dbblockgets’,’consistentgets’);

如果任何一个class/sum(value)10%,那么考虑增加回退段。回退段的数量一般按如下规律设定：用户数n1641632=nn/4但不超过50减少FreeList竞争当多个进程同时向一个表中插入数据时，产生FreeList竞争。SQLSELECTclass,countFROMv$waitstatWHEREclass=’freelist’;SQLSELECTsum(value)FROMv$sysstatWHEREnameIN(’dbblockgets’,’consistentgets’);如果class/sum(value)1%，则应增加该表的FreeList的值。回退段个数

3.数据高速缓存的优化

数据高速缓存块由许多大小相等的缓存块组成，这些缓存块的大小和OS块大小相同。

这些缓存块分为3大类、

（1）脏缓存块(Dirtybuffers)：

脏缓存块中保存的时被修改过的缓存块。即当一条SQL语句对某个缓存块中的数据进行修改后，该缓存块就被标记为脏缓存块。最后该脏缓存块被DBWR进程写入到硬盘的数据文件中，永久保留起来。

（2）命中缓存块(Pinnedbuffers)：

命中缓存块中保存的是最近正在被访问的缓存块。它始终被保留在数据高速缓存中，不会被写入数据文件。

（3）空闲缓存块（Freebuffers）：

该缓存块中没有数据，等待被写入数据。oracle从数据文件中读取数据后，寻找空闲缓存块，以便写入其中。

Oracle通过2个列表（DIRTY、LRU）来管理缓存块

DIRTY列表中保存已经被修改但还没有写入到数据文件中的脏缓存块。

LRU列表中保存所有的缓存块（还没有被移动到DIRTY列表中的脏缓存块、空闲缓存块、命中缓存块）。

当某个缓存块被访问后，该缓存块就被移动到LRU列表的头部，其他缓存块就向LRU列表的尾部移动。放在最尾部的缓存块就最先被移出LRU列表。

数据高速缓存的工作原理过程是：

A、ORACLE在将数据文件中的数据块复制到数据高速缓存中之前，先在数据高速缓

存中找空闲缓存块，以便容纳该数据块。Oracle将从LRU列表的尾部开始搜索，直到找到所需的空闲缓存块为止。

B、如果先搜索到的是脏缓存块，将该脏缓存块移动到DIRTY列表中，然后继续搜

索。

C、如果能够搜索到足够大的空闲缓存块，就将所有的数据块写入到对应的空闲缓存块

中，该块移入LRU列表头部。则搜索写入过程结束。

D、如果没有搜索到足够大的空闲缓存块，则ORACLE就先停止搜索，而是激活

DBWR进程，开始将DIRTY列表中的脏缓存块写入到数据文件中。已经被写入到数据文件中的脏缓存块将变成空闲缓存块，并被放入到LRU列表中。执行完成这个工作后，再重新开始搜索，直到找到足够的空闲缓存块为止。

重做日志高速缓存（其大小由初始化参数LOG_BUFFER指定，可以在运行期间修改该参数）

为了加快访问和速度和工作效率，重做记录并不直接写入重做日志文件中，而是首先从数据高速缓存写入重做日志高速缓存。当重做日志高速缓存中的重做记录达到一定数量或某个时间点时，再由LGWR进程分批写入重做日志文件中（即ORACLE总是先日志后文件或先内存后磁盘）。由于重做日志文件是循环使用的。

所以当重做日志文件切换时，还会由ARCH（如果启用了归档日志模式）进程将即将要被覆盖的重做日志文件中的数据写入到归档日志文件中，作为备份

知道了这些数据库高速缓存工作原理之后，我们数据库管理员又该做些什么呢，来对Oracle数据库进行优化。为此，有以下建议：

1、为Oracle数据库配置尽量大的内存。Oracle数据库最新版本，根据官方的建议，

其内存需要1G。虽然在低于这个内存数量的时候，数据库仍然可以运行，但是，其运行适度会大打折扣。当查询大量数据的时候，更是比较吃力。如果当前使用的数据库服务器，是使用了4个G的内存。以前我用的是2个G的。内存升级后，发现数据库的性能得到了比较大的改善。

2、在对数据进行查询操作时，尽量使用限制条件。如现在需要查询销售部门的员

工信息时，我们不需要查询全部的员工信息，而是在SELECT语句中，利用WHERE条件语句设置查询条件。如此的话，就可以充分利用DIRTY列表中的空闲缓存块，而不会因为空闲缓存块容量不够而频繁的去执行数据库写操作。这会明显降低数据库的运行操作。同时，在查询时，最好也能够明确查询的信息，如你只需要员工的姓名与入职日期，那就不需要把员工的出生年月、身份证号码都查询出来。所以，有时候合理设计视图，也可以提高数据库的运行效率。

3、最好不要在数据库服务器上运行其他的服务。在数据库服务器中，若还运行其

它服务器的话，除了硬件资源争夺影响服务器的运行效率之外，还会产生一个问题。就是会使得数据库的数据高速缓存块不连续。这会直接影响数据库查询空闲缓存块的效率。

对脏缓存块进行数据库写入操作以及数据库进行标记之间的转换也会产生影响。所以，根据笔者的经验，数据库服务器最好能够独立。最多只能跟其对应的应用服务器部署在同一台服务器上。如现在Oracle数据库是一台ERP系统的后台数据库，最好数据库能够跟ERP服务器分开部署。但是，若由于服务器资金的限制，那么可以把ERP应用服务器跟数据库服务器部署在一台服务器上。但是，不能再跟邮件服务器等应用服务器放在一起。这会影响数据高速缓存的管理效率，从而最终影响数据库的运行效能。现在服务器价格逐渐下滑，服务器的成本已经不是影响企业数据库应用的关键。所以，出于数据库性能考虑，笔者认为，企业在这上面还是应该大方的进行投资。没必要为了这么一点点钱，影响到数据库的性能

4.回滚段的优化

调整系统用SYSTEM回滚段

Oracle数据库系统总是将系统用的回滚取名为SYSTEM(不要与SYSTEM用户相混)，而且该回滚段都建立在SYSTEM表空间内。比如可以从下面查询中看到系统的所有回滚段的基本信息：

SQL>selectsegment_name,tablespace_name,bytes,next_extent

2fromdba_segmentswheresegment_type='ROLLBACK';

SEGMENT_NAMETABLESPACE_NAMEBYTESNEXT_EXTENT

SYSTEMSYSTEM40960057344

RBS0RBS41943042097152

RBS1RBS41943042097152

RBS2RBS41943042097152

RBS3RBS41943042097152

RBS4RBS41943042097152

RBS5RBS41943042097152

RBS6RBS41943042097152

从上面仅仅是查询到回滚段的基本信息，要了目前各个回滚段的动态信息，还要查询V$ROLLNAME和V$ROLLSTAT视图。V$ROLLNAME视图只存放各回滚段的编号和名字，V$ROLLSTATS存放各个回滚段当前的情况信息。要想得到每个回滚段的信息，就要查询两个表的信息才能得到。如：

SQL>colnamefora12

1selecthttp://./doc/f06905589b89680202d825aa.htmln,http://./doc/f06905589b89680202d825aa.html,s.extents,s.optsize,s.hwmsize,s.status

2*fromv$rollstats,v$rollnamenwherehttp://./doc/f06905589b89680202d825aa.htmln=http://./doc/f06905589b89680202d825aa.htmln

显示的查询结果如下：

USNNAMEEXTENTSOPTSIZEHWMSIZESTATUS

0SYSTEM58388608401408ONLINE

1RBS088388608418611

2ONLINE

2RBS1883886084186112ONLINE

3RBS2883886084186112ONLINE

4RBS3883886084186112ONLINE

5RBS4883886084186112ONLINE

6RBS5883886084186112ONLINE

已选择8行。

从第1个查询语句得到的结果看，SYSTEM回滚段的初始分配太小，只有400KB，而且下一次分配的大小也太小。只有56KB。这显然是不合适的，但对于SYSTEM回滚段，建议只能调整修改下次分配（NEXT）值和最佳扩展（OPTIMAL）值。注意目前Oracle不允许修改各个段的初始分配值，包括表、索引及簇的初始分配等。

例子：根据前面的查询结果：

systen回滚段的初始分配initial=409600；

下一次分配next=57344；

最佳扩展值optimal=null；

根据这样的情况，我们可以修改下一次分配的值为1MB(=1024000字节)；修改最佳扩展值为：optimal=initial+2*next=409600+2*1024000=2457600。所以修改SYSTEM回滚的命令为：

SQL>alterrollbacksegmentsystemstorage(next1moptimal2457600);

重算段已修改。

注：optimal表示在事务处理当中，该回滚段由于增、删、改的数据量的需要而不断对回滚段进行扩展，以满足事务的要求，但是在事务处理完成后可以让回滚段缩小到一个最佳的范围内。这就是由回滚段的OPTIMAL参数来控制。一般optimal=initial+n*next,且n>1。

确定回滚段的数目

回滚段的数量直接影响到系统的性能，如果回滚段的个数不够多，则在多个用户同时进行增、删、该时就存在等待现象。

要确定是否增加回滚段的数量，先要查询两个动态视图，即V$ROLLSTAT，V$WAITSTAT。如：

SQL>select*fromv$waitstatwhereclass='undoheader';

CLASSCOUNTTIME

undoheader00

SQL>selectusn,extents,waitsfromv$rollstat;

USNEXTENTSWAITS

050

180

280

380

480

580

680

780

如果waits存在大于0的数，则需要增加回滚段的数量。一般回滚段的数量主要根据应用系统的类型来决定。比如一般的历史档案系统，由于其主要处理是查询。这类增、删、改相对较少的应用，可以建立较少的回滚段。而想银行、证券等应用。就需要很多的回滚段。那么到底要多少才相对就够了呢？下面的回答具有一般性：

在并发程度要求高的应用系统，同一时间内有多个transaction在竞争竟争回退段。假如transaction为事务的个数；则有：

n=transaction/transactions_per_rollback_segment

其中：

n=回退段数量

transaction为ORACLE系统参数，系统中允许并发处理的最大transaction数。

transactions_per_rollback_segment为ORACLE参数，每个回退段允许同时写入的最大transaction数。

另外，如果从v$waitstat动态视图中查出自数据库启动后回退头的等待次数较高，则应该多建立回滚段。

确定回滚段的大小

由于回滚段是建立在特定的表空间里，所以回滚段的多少就要根据应用的需要和硬盘的大小来决定。一般Oracle安装完成后各个回滚段的大小都太小。一般的应用都需要进行调整。那么到底每个回滚段建立多大就够了呢。下面给出一般的应用的回滚段大小的创建建议：

建立专用的大回滚段

如果说，建立每个回滚段都很大，可以满足任何增、删、改的需要。但是这样就会浪费磁盘空间，因为这些回滚段的使用频繁并不是很高。所以，建议针对专门的处理建立大的回滚段。比如管理员的数据库整理，包括将旧数据删除，恢复大量数据等。这样的操作无论是在命令方式或是通过程序来使用，都会有很大好处。

例子：为结息处理建立大的回滚段：

createrollbacksegmentinteresttablespaceinterest_tabspace

storage(initial50mnext10moptimal80mpct_increase0);

建立通用的回滚段

无论是任何类型的应用，建议重新建立新的回滚段。除了上面介绍的建立专门的回滚段以外，需要建立初始值，下次增加及最佳值合适的值。一般建议是初始值最好是5MB以上，下次增长为2MB至5MB之间，最佳值在20M至30MB之间。

改变回滚段

当回滚段建立好了之后，有时需要对它们进行修改。可以对回滚段进行存储参数的修改，可以对某个脱机（OFFLINE）回滚段修改为联机（ONLINE）。也可能对已经处于联机的回滚段设置为脱机，比如当我们进行IMP或大量修改数据时，Oracle总是需要大的回滚段。但由于分配回滚段是Oracle系统包办。为了在事务处理中得到大的回滚段，我们只能将较小的回滚段设置为脱机的状态，等到我们所处理的事务完成后在将它们设置为联机等。

§14.4.1观察回滚段的增长

虽然回滚段在使用过程中作过扩展而在用完后又自动按照OPTIMAL要求大小缩小，但在动态字典

V$ROLLSTAT中会记录曾经扩展的情况。命令如下：

selecthttp://./doc/f06905589b89680202d825aa.html,optsize,hwmsize

fromv$ROLLNAMEn,V$ROLLSTATs

wherehttp://./doc/f06905589b89680202d825aa.htmln=http://./doc/f06905589b89680202d825aa.htmln;

optsize是最优大小值，hwmsize=Hightwatermarkofrollbacksegmentsize回滚段扩展中最高值（水位）。如果optsize为空，则hwmsize就是当前最高值。

改变回滚段的存储参数

可以对已经建立的回滚段的参数进行修改，允许修改的参数有OPTIMAL或MAXEXTENTS参数。如：ALTERROLLBACKSEGMENTrbs_01

STORAGE(MAXEXTENTS120);

同样也可以修改系统回滚段的OPTIMAL参数。

手工缩短回滚段的存储分配

如果某些回滚段建立时设置参数过大，可以用Alter命令带SHRINK保留字来实现将其大小缩短到某个合适的值里。如：

ALTERROLLBACKSEGMENTrbs1SHRINKTO100K;

§14.4.4设置回滚段状态为ONLINE/OFFLINE

作为细心的管理员，应该经常注意到所运行的系统的各个回滚段的状态是处于ONLINE或OFFLINE。可以从数据字典中查询到。如：

SQL>selectname,statusfromv$rollname,v$rollstat

2wherev$http://./doc/f06905589b89680202d825aa.htmln=v$http://./doc/f06905589b89680202d825aa.htmln;

NAMESTATUS

SYSTEMONLINE

RBS0ONLINE

RBS1ONLINE

RBS2ONLINE

RBS3ONLINE

RBS4ONLINE

RBS5ONLINE

RBS6ONLINE

已选择8行。

可以用下面方法来使回滚段状态为脱机或联机：

1．在INITsid.ora参数文件中设置ROLLBACK_SEGMENTS使得所需要的回滚段变为ONLINE.

ROLLBACK_SEGMENTS=(seg1_name,seg2_name,...)

2．在命令方式下设置回滚段为ONLINE

ALTERROLLBACKSEGMENTuser_rs_2ONLINE;

3.设置回滚段为OFFLINE

ALTERROLLBACKSEGMENTuser_rs_2OFFLINE;

[@more@]

5.重做日志文件的优化（当前无解决方案）

6.排序区的优化

排序区调整：

selectname,valuefromv$sysstatwherenamelike'%sort%';

sorts(memory)表示内存排序量，sorts(disk)表示磁盘序量。

http://./doc/f06905589b89680202d825aa.html

失败率＝sort(disks)/sorts(memory)×100％排序区的失败率不得

小于5％，否则必须增加排序sort_area_size的大小，使得Oracle尽量使用内存排序。磁盘IO的调整就是把数据文件放在多个不同的磁盘上。

减少了IO的操作次数就减少了CPU的需求。

下面是我一些实际操作的过程：

altersystemdb_cache_size=256000000sc