这本文章的目的是介绍解决oracle7到oracle11的共享池问题.如果你的系统出现以下任何问题:
对于library cache latch或latch:library cache的闩锁竞争
对于shared pool latch或latch:shared pool的闩锁竞争
高cpu解析时间
v$librarycache的高reloads次数
高版本游标
大量的解析调用
频繁的ora-04031错误
解决问题的步骤
什么是共享池
oracle在sga中的一个区域保留sql语句,包,对象信息和许多其它信息这个区域就叫作共享池.共享池由于一个复杂的缓存和堆管理器组合而成的,它有三个基本的问题要克服:
1.内存分配单元不是一个常量—共享池中的内存分配可以是几个字节到几千字节
2.当用户使用完后不是所有的内存都能释放(这种情况出现在传统的堆管理).共享池的目的是最大化的共享信息.在内存中的信息可能对另外的会话有用—oracle事先并不知道这些信息将来能不能被使用
3.这里没有磁盘page out,所以不象传统的缓存有一个文件备份存储.只会当信息从缓存中消失后当下次需要时进行重建.
基于上面的三点就可以知道管理共享池是一个复杂的工作.下面将介绍影响共享池性能的关键问题和与它相关的闩锁竞争.
Literal SQL
一个literal sql是在谓词中使用了literal值而没有使用绑定变量的sql语句.不同的literal值对于语句来说可能会有不同的执行计划.
例如:
SELECT * FROM emp WHERE ename=’CLARK’;
使用应用程序来调用可能是:
SELECT * FROM emp WHERE ename=:bind1;
例如:
select sysdate from dual;
虽然没有使用绑定变量但不会被认为是一个literal语句,这个语句是能被共享的.
例如:
SELECT version FROM app_version WHERE version>2.0;
如果相同的语句被用来检查应用程序的版本且literal值’2.0’总是相同的那么这个语句会被认为可以被共享.
硬解析
如果一个新调用的sql语句在共享池中不存在那么就要进行全面的解析.oracle会对这个语句从共享池中分配内存,检查语法和语义等等这称为硬解析对于cpu的消耗和latch获取的执行次数来说都是很能昂贵的.
软解析
如果一个会话发出的sql语句它已经在共享池中存在那么对于这个语句能使用一个已经存的版本这称为软解析.对于应用程序来说它已经要求解析这个语句了.
相同的语句
如果两个sql语句的意思相同但有些字符的格式不同oracle会认为这是不同的语句.例如下面是在单个会话中scott用户发出的语句:
SELECT ENAME from EMP;
SELECT ename from emp;
虽然两个语句实际上是相同的但是由于大小写的原因会被认为是不同的语句.例如E与e是不同的.
共享sql
如果两个会话发出相同的语句但是不一定能共享.例如scott用户有一个叫EMP的表并执行以下语句:
SELECT ENAME from EMP;
用户fred也有一个叫EMP的表并执行以下语句:
SELECT ENAME from EMP;
虽然语句的文本相同的但是EMP是来自不同用户的对象.因此对于相同的语句会有不同的游标版本.有许多信息要检查来判断两个语句是否是真的相同包括:
所有的对象名必需是相同的真实对象
发出语句的会话的optimizer goal要相同
任何绑定变量的类型和长度应该是相似的
每个语句的的国示语句支持环境必需相同
语句的版本
在共享sql中如果两个语句的语句文本相同但不能共享那么这些语句就被称作相同语句的版本.在解析期间如果oracle使用多个版本来匹配一个语句那么不得不检查每一个版本来看是否与某个特定的版本语句相同.因此高版本语句最好要通过以下方式来避免:
由客户来指定标准化的绑定变量长度
避免不同用户使用相同的语句
在oracle8.1中将_SQLEXEC_PROGRESSION_COST设置为0
library cache和shared pool latches
共享池闩锁(shared pool latches)是在共享池中分配和释放内存时来保护关键操作的
库缓存闩锁(library cache或oracle7.1中的library cache pin latch)是用来保护库缓存自身的操作
所有的闩锁都是潜在的竞争点.请求闩锁的次数会直接影响共享池中活动的数量,特别是解析操作.任何能够减少共享池中的闩锁请求和真实的活动数量的操作对于性能和可扩展性来说都是有好处的
literal sql与shared sql
literal sql
当语句引用的对象用完全的统计信息和在语句谓词中使用literal值时基于成本的优化器会工作的最好,例如:
SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0;
与
SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA;
对于第一个语句如果已经收集了直方图信息那么基于成本的优化器会使用直方图信息来判断是对orders表使用全表扫描还是使用total_cost列上的索引进行扫描.对于第二个语句基于成本的优化器不知道小于":bindA"的记录占整个记录的百分比因为在判断 一个执行计划时绑定变量是没有值的例如":bindA"可能是0.0或者99999.9
在这两个语句的两种执行计划的响应时间之间会有数量级的差别.所以你如果想基于成本优化器选择最佳的执行计划最好使用literal sql语句.这是典型的决策支持系统它没有任何标准的语句(发出重复的语句)所以能共享的语句就很少.在解析时消耗的 cpu数量通常占执行语句所消耗cpu数量很小的百分比所以相比减少解析时间来说更重要的是给优化器更多的信息.
shared sql
如果一个应用程序使用literal(unshared) sql那么这是非常限制可扩展性和吞吐量的.解析一个新语句在cpu请求和库缓存闩锁 和共享池闩锁方面都是很昂贵的.即使解析一个简单的sql语句可能也需要请求库缓存闩锁20或30次.
最好的方法是使用所有的sql语句被共享除非是很少或不频繁使用的sql语句,给基于成本的优化器更多的住处让其生成一个最佳的执行计划也是很重要的.
减少共享池的加载次数
解析一次/执行多次
到目前为止在OLTP系统中让应用程序对sql语句只解析一次并将游标打开当请求它时就执行.这样做的结果是对于每一个语句只在最初进行解析(可能是软解析也可能是硬解析).很明显有些语句是很少执行的因此对于这些语句保持打开游标会浪费资源.
注意一个会话只有(参数open_cursors)游标可用且保持游标为打开状态时才有可能增加并发打开游标的数量
在预编译程序中hold_cursor参数控制着游标是否保持持开状态而OCI开发者可以直接控制游标.
消除literal sql
如果一个程序你想消除所有的literal sql是不可能的但是在literal sql造成问题时还是要消除造成问题的这些literal sql语 句.通过查看v$sqlarea视图可以看到哪些literal语句是可以转换使用绑定变量.下面的语句查询在sga中有大量相似语句的sql:
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
;
对于oracle10g使用以下查询语句:
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 latches的竞争那么这些语句可能会更进一步的产生更严重的竞争问题.
避免无效游标
有一些特定的操作会将游标的状态改变为invalidate.这些操作会直接修改与游标相关对象的上下文.这些操作比如对表或索引进行truncate,analyze或dbms_stats.gather_xxx操作,或者改变基础对象的授权.这些相关的游标仍然会保留在sqlarea中但是当它们下次被引用时,它们会被重新加载且重新完全解析,所以会影响整个性能.
下面的查询能够帮我们识别这些无效的游标:
SELECT SUBSTR(sql_text, 1, 40) “SQL”,
invalidations
FROM v$sqlarea
ORDER BY invalidations DESC;
cursor_sharing参数(8.1.6及以后版本)
参数cursor_sharing是在oracle8.1.6中引入的.
在这个版本中使用它要谨慎.如果这个参数被设置为force那么literal值将会可能由系统生成的绑定变量来替换.对于多个相似的 且只有literal值不同的语句将会允许语句共享尽管应用程序提供的sql是使用的literal值.这个参数是动态参数可以在实例或会 话级别进行修改.
ALTER SESSION SET cursor_sharing = FORCE;
或
ALTER SYSTEM SET cursor_sharing = FORCE;
或者在init.ora文件中进行设置
注意:当这个以数设置为force会用系统生成的绑定变量来替换literal值,这时基于成本的优化器可能会选择与原先不同的执行计划因为在优化器计算最佳执行计划时没有了literal值.
在oralce9i中,cursor_sharing可以设置为similar.similar用于语句可能在某些literal值不同的情况下,这会让这些语句允许被 共享除非literal值影响了语句的意思或者影响了被优化的执行计划的并行度.这增强了这个参数的可用性不象设置为force时通 常会造一个不同的不好的执行计划.当cursor_sharing设置为similar时,oracle会判断哪个literal使用绑定变量来替换是安全的这也会造成一些语句因为为了提供一个更好的执行时而不被共享.
cursor_sharing参数在oracle12c中会被丢弃.
session_cached_cursor参数
参数session_cached_cursor是一个数字参数它能在实例或会话级别使用下面的语句来进行修改:
ALTER SYSTEM SET session_cached_cursors = NNN;
或
ALTER SESSION SET session_cached_cursors = NNN;
这个NNN决定在你的会话中能缓存多少个游标
每当一个语句被解析时oracle首先会检查你的私有会话缓存中有没有这个语句,如果对于这个语句存在一个共享的版本能被使用,
对于频繁解析的语句与软件解析或硬解析相比会使用更少的cpu和更少的闩锁请求次数从而提供了一个快捷访问.
为了能将相同的语句缓存在会话缓存中这个语句必须要使用相同的游标解析3次然后这个共享游标的一个指针会被增加到你的会话缓存中.如要所有的会话缓存游标都在被使用那么最近最少使用的游标会被丢弃.
如果你没有设置这个参数那么建议将给它设置一个初始值50.在bstat/estat报告中的统计部分有一个’session cursor cache hits’信息显示了会话缓存游标带来的好处.这个会话缓存游标的大小可以根据需要增加或减少.
cursor_space_for_time参数
cursor_space_for_time参数在10.2.0.5和11.1.0.7中被丢弃
参数cursor_space_for_time控制着部分游标是否在一个语句的不同执行计划之间保持pinned.如果所有的失败了它能在这些共享 语句被频繁使用时或者在有显著的pinning/unpinning游标时(查看v$latch_misses视图如果大部分的latch等待是由于”kglpnc:child”和”kglupc:child”,这是由于对游标进行pinning/unpinning产生的)能带来一些好处.
必须确保共享池对于工作负载来说是足够大的否则性能会受到影响且会触发ora-4031错误.
如果你设置此参数要注意:
如果shared_pool对于工作负载来说设置的太小那么可能会经常触发ora-4031错误.
如果你的程序有任何的游标泄漏那么泄漏的游标在经过一段时间的操作后会浪费大量的内存对性能产生影响.
将这个参数设置为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 SESSIONSET NLS…
CLOSE_CACHED_OPEN_CURSORS参数
这个参数在oracle8i中已经废弃了.
参数close_cached_open_cursors控制着当一个事务提交时plsql游标是否关闭.缺省值是false这意味着当事务提交时plsql游标 保持打开这能减少硬解析.如果这个参数设置为true那么这将增加当sql不使用时从共享池中被清除的机会.
SHARED_POOL_RESERVED_SIZE参数
这个参数是在oracle7.1.5引入的对保留共享池大内存分配提供了一种方法.这个共享池保留区来自共享池本身.
从实用的角度shared_pool_reserved_size的大小一般设置为shared_pool_size的10%除非共享池很大或shared_pool_reserved_min_alloc相比于缺省值设置的太小:
如果共享池非常大那么10%可能会浪费大量的内存而实际上只有几MB就够了
如果shared_pool_reserved_min_alloc已经很小那么许多空间请求可能从共享池部分能得到满足那么10%的大小就小了.
可以很容易的监控共享池保留区的使用情况查询v$shared_pool_reserved视图中的free_sapce列.
shared_pool_reserved_min_alloc参数
在oracle8i中这个参数是隐含参数
shared_pool_reserved_min_alloc参数一般使用其缺省值,尽管在特定情况下4100或4200字节可能会帮助解决共享池高负载时的一些竞争.
shared_pool_size参数
参数shared_pool_size控制着共享池本身的大小.共享池的大小会影响性能.如果共享池太小那么它会将一些共享信息从共享池中 清除而后续的请求就要重新加载.如果有大量的literal sql且共享池太大那么长时间的操作会在内部内存的可用列表中创建一些 小的内存块这会导致共享池闩锁会被持有很长时间进而影响性能.在这种情况下小的共享池比大的共享池可能会运行的更好.
注意:共享池它本身不是很大因此会有大量的分页或交换发生那么性能会呈数量级的降低.
_SQLEXEC_PROGRESSION_COST参数
这是一个隐含参数在oracle8.1.5中引入.这个参数的缺省设置会造成一些sql共享的问题,将这个参数设置为0可以避免这个问题 但是又会在共享池中产生多版本语句.
注意如果将这个参数设置为0的另一个问题是在v$session_longops视图中将不会记录长时间执行的查询.
预编译程序的hold_cursor和release_cursor选项
当使用oracle预编译程序共享池的行为可以通过使用参数release_cursor和hold_cursor来进行改变.这些参数将会判断库缓存中游标的状态和会话缓存中一旦执行完成后游标的状态.
在共享池中pinning cursors
dbms_shared_pool.keep
这个过程(它的定义在rdbms/admin目录下的dbmspool.sql脚本中)能被用来将保留对象共享池中.dbms_shared_pool.keep允许保留包,过程,函数,触发器和序列.
一般来说它通常需要标记哪些频繁使用的包这样让它们总是被保留在共享池中.对应该应该在实例启动后不久被保留在共享池中因为数据库在执行重启之后不会自动执行这个操作.
清空共享池
在使用大量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,而只清空其中的单个对象.
使用 V$ 视图 (V$SQL 和 V$SQLAREA)
注意有一些V$视图需要获取相关的latch来返回查询的数据.用来展示library cache和SQL area的视图就是值得注意的.所以我们建议有选择性的运行那些需要访问这种类型视图的语句.特别需要指出的是,查询V$SQLAREA会在library cache latch上产生大量的负载,所以一般可以使用对latch访问比较少的v$sql做替代——这是因为V$SQLAREA的输出是基于shared pool中所有语句的GROUP BY操作,而V$SQL没有用GROUP BY操作.
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”.
使用dedicate connections(专有连接)替代MTS可以使UGA在进程私有内存中分配而不是shared pool.私有内存分配不会使用”shared pool latch”,所以在有些情况下从MTS切换到专有连接可以帮助减少竞争.
在Oracle9i中,MTS被改名为”Shared Server”.但是对于shared pool产生影响的行为从根本上说还是一样的.
使用SQL查看Shared Pool问题
这里展示了一些可以用来帮助找到shared pool中的潜在问题的SQL语句.这些语句的输出最好spool到一个文件中
注意:这些语句可能会使latch竞争加剧
查找literal SQL
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
检索Library Cache hit ratio
SELECT SUM(PINS) “EXECUTIONS”,
SUM(RELOADS) “CACHE MISSES WHILE EXECUTING”,
SUM(RELOADS)/ SUM(PINS) “MISSES/EXECUTIONS”
FROM V$LIBRARYCACHE;
如果misses/executions高于1%的话,则需要尝试减少library cache miss的发生.
检查 hash chain 的长度:
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值.
检查高版本:
SELECT address, hash_value,
version_count ,
users_opening ,
users_executing,
substr(sql_text,1,40) “SQL”
FROM v$sqlarea
WHERE version_count > 10
;
一个语句的不同”版本”是当语句的字符完全一致但是需要访问的对象或者绑定变量不一致等等造成的.在Oracle8i的不同版本中 因为进度监控的问题也会产生高版本可以把_SQLEXEC_PROGRESSION_COST 设成’0’来禁止进度监控产生高版本
找到占用shared pool 内存多的语句:
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语句或者是一个语句的不同版本.
导致shared pool 内存’aged’ out的内存分配
SELECT *
FROM x$ksmlru
WHERE ksmlrnum>0
;
注意: 因为这个查询在返回不超过10行记录后就会消除X$KSMLRU的内容,所以请用SPOOL保存输出的内容.X$KSMLRU表显示从上 一次查询该表开始,哪些内存分配操作导致了最多的内存块被清除出shared pool.有些时候,这会有助于找到那些持续的请求 分配空间的session或者语句.如果一个系统表现很好而且共享SQL使用得也不错,但是偶尔会变慢,这个语句可以帮助找到原因