2017年(38)
分类: Oracle
2017-12-07 13:48:12
案例: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;