关于hard parses以及latches争用

最新推荐文章于 2024-04-26 07:38:25 发布

cuichao1900

最新推荐文章于 2024-04-26 07:38:25 发布

阅读量517

点赞数

文章标签：数据库

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
/