latch free等待

案例：2011/10/26 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production+Red Hat Enterprise Linux AS release 4 (Nahant Update 5)

產線反映掃描緩慢：

top:發現負載達到10，cpu :60%(us)

查看v$session_wait很多latch free等待

可以看下這個：
select sid,event,p1 as file_id, p2 as "block_id/latch", p3 as blocks,l.name
from v$session_wait sw,v$latch l
where event not like '%SQL%' and event not like '%rdbms%'
and event not like '%mon%' and sw.p2 = l.latch#( +);
慢的時候基本上都是latch free( library cache )

 

硬解析問題消耗CPU:
select name,value from v$sysstat where name like 'parse%';
parse count hard占parse count total的13%

為避免硬解析強制綁定變量：cursor_sharing=force，不需要重啟DB就能生效。

設置后，過一段觀察vmstat，CPU可用率從50%達到75%，但不久開發人員反映一些通過ADO控件連接DB的group by SQL無法執行，報ORA-00979,ORA-00939。為了使程序的正常運行寫了一個Trigger:

CREATE OR REPLACE TRIGGER SYS.CURSOR_SHARING AFTER LOGON ON DATABASE
DECLARE
UIP   VARCHAR2(300);
PRGRM  VARCHAR2(60);
CUSER VARCHAR2(60);
MO      VARCHAR2(60);
BEGIN
    --SELECT SYS_CONTEXT('USERENV','IP_ADDRESS'),USERNAME,PROGRAM,MODULE INTO UIP,CUSER,PRGRM,MO FROM V$SESSION WHERE AUDSID=USERENV('sessionid') and rownum=1;
    --If UIP='10.161.32.110' THEN
    --IF INSTR(UPPER(PRGRM),'TOAD.EXE')>0 THEN
    IF SYS_CONTEXT('USERENV','TERMINAL') IN ('IMB-XP002') THEN
        EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING=EXACT';
    END IF;
END;