ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于hard parses以及latches争用

关于hard parses以及latches争用

原创 Linux操作系统 作者:husthxd 时间:2004-10-18 00:00:00 0 删除 编辑

sth about hard parses & latches.


2004-10-18

过多的hard parses会引起消耗系统资源,降低查询响应时间/latches争用/会引起shared_pool的碎片
以下查询用于监测执行过多hard parses的程序
column program format a20
select /*+ rule*/ s.program,count(*) users,sum(t.value) parses,sum(t.value)/count(*) parses_per_session,
sum(t.value)/(sum(sysdate - s.logon_time)*24) parses_per_hour
from v$session s,v$sesstat t
where t.statistic# = 153
and s.sid = t.sid
group by s.program having sum(t.value)/count(*) > 2.0
order by parses_per_hour desc
/

-- session等待事件
select /* +rule*/ substr(event,1,30) event , count(*)
from v$session_wait
where wait_time = 0
group by substr(event,1,30),state
/
如果latch free持续出现,表明有latch争用.
-- 下面查询获得latches存在争用
select /* +rule*/ name,count(*)
from v$latchholder
group by name
/
如果library cache或者shared pool持续出现,表明存在争用.

使用statspack等工具监测系统,在wait events事件中检查latch free事件,
如果事件的等待时间比例高于1%,表明存在latch争用.
假如很少的sql语句没有使用绑定变量,那么问题就可能就是shared_pool太小了.
通常的解决方法:
1.如果真的是shared pool大小.对于小范围的争用问题,增大20%是合适的.对于比较严重的问题,增大50%
2.考虑减少参数OPTIMIZER_MAX_PERMUTAIONS参数,该参数控制cbo用于决定最优执行计划时尝试的
  最大的执行计划的数目.默认是80000
3.定期的flush shared pool,用于减少碎片.
4.把经常使用的PL/SQL函数和包pin到shared pool中.

2004-11-09

This script which is from oracle support provides information on latch performance. 

======== 
Script 1: 
======== 

SET ECHO off 
REM NAME:   TFSLATCH.SQL 
REM USAGE:"@path/tfslatch" 
REM ------------------------------------------------------------------------
REM AUTHOR:  
REM    Virag Saksena, Craig A. Shallahamer, Oracle US      
REM    (c)1994 Oracle Corporation      
REM ------------------------------------------------------------------------ 
REM Main text of script follows

ttitle - 
   center   'Latch Contention Report'  skip 3 
 
col name form A25 
col gets form 999,999,999 
col misses form 999.99 
col spins form 999.99 
col igets form 999,999,999 
col imisses form 999.99 
 
select name,gets,misses*100/decode(gets,0,1,gets) misses, 
spin_gets*100/decode(misses,0,1,misses) spins, immediate_gets igets 
,immediate_misses*100/decode(immediate_gets,0,1,immediate_gets) imisses 
from v$latch order by gets + immediate_gets 
/  

======== 
Script2: 
========  
 
SET ECHO off 
REM NAME:   TFSLTSLP.SQL 
REM USAGE:"@path/tfsltslp" 
REM ------------------------------------------------------------------------
REM AUTHOR:  
REM    Virag Saksena, Craig A. Shallahamer, Oracle US      
REM    (c)1994 Oracle Corporation 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 

col name form A18 trunc 
col gets form 999,999,990 
col miss form 90.9 
col cspins form A6 heading 'spin|sl06' 
col csleep1 form A5 heading 'sl01|sl07' 
col csleep2 form A5 heading 'sl02|sl08' 
col csleep3 form A5 heading 'sl03|sl09' 
col csleep4 form A5 heading 'sl04|sl10' 
col csleep5 form A5 heading 'sl05|sl11' 
col Interval form A12 
set recsep off 
 
select a.name 
      ,a.gets gets 
      ,a.misses*100/decode(a.gets,0,1,a.gets) miss 
      ,to_char(a.spin_gets*100/decode(a.misses,0,1 
       ,a.misses),'990.9')|| 
       to_char(a.sleep6*100/decode(a.misses,0,1 
       ,a.misses),'90.9') cspins 
      ,to_char(a.sleep1*100/decode(a.misses,0,1 
       ,a.misses),'90.9')|| 
       to_char(a.sleep7*100/decode(a.misses,0,1 
       ,a.misses),'90.9') csleep1 
      ,to_char(a.sleep2*100/decode(a.misses,0,1 
       ,a.misses),'90.9')|| 
       to_char(a.sleep8*100/decode(a.misses,0,1 
       ,a.misses),'90.9') csleep2 
      ,to_char(a.sleep3*100/decode(a.misses,0,1 
       ,a.misses),'90.9')|| 
       to_char(a.sleep9*100/decode(a.misses,0,1 
       ,a.misses),'90.9') csleep3 
      ,to_char(a.sleep4*100/decode(a.misses,0,1 
       ,a.misses),'90.9')|| 
       to_char(a.sleep10*100/decode(a.misses,0,1 
       ,a.misses),'90.9') csleep4  
      ,to_char(a.sleep5*100/decode(a.misses,0,1 
       ,a.misses),'90.9')|| 
       to_char(a.sleep11*100/decode(a.misses,0,1 
       ,a.misses),'90.9') csleep5 
from v$latch a 
where a.misses <> 0 
order by 2 desc 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-21590/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 休息
请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1395
  • 访问量
    3842122