Re: Poor Performance on Oracle9i - 9.2.0.7

From: Beko <m.elgebeli_at_gmail.com>
Date: 8 May 2006 13:50:33 -0700
Message-ID: <1147121433.598223.320720@i40g2000cwc.googlegroups.com>

Hello,
first
I agree with Benny to remove all the hidden parameters, because in Oracle 9i we don't change them but upon Oracle support Recommendations, and You need to rebuild and analyse all the objects that you use.

second
- Issue this command, this will show us the buffer cache ratio and then we will determine if we need to increase the buffer cache size.

select trunc((1-(sum(decode(name,'physical reads',value,0))/

	             (sum(decode(name,'db block gets', value,0))+
	    (sum(decode(name,'consistent gets',value,0)))))
	              )* 100) "Buffer Hit Ratio"
	 from v$sysstat;


#######################################################################################

- Show us the latches hit and miss ratio by issuing the following command

column "miss_ratio" format 999.99
column "immediate_miss_ratio" format 999.99 select substr(l.name,1,30) name,

(misses/(gets+.001))*100 "miss_ratio",
(immediate_misses/(immediate_gets+.001))*100

        "immediate_miss_ratio"
        from v$latch l, v$latchname ln
where l.latch# = ln.latch#
        and (


        (misses/(gets+.001))*100 > .2

or


        (immediate_misses/(immediate_gets+.001))*100 > .2

)
order by l.name;

#######################################################################################

- When you face bad performance issue the following command, this will show us what are we waiting for.
SELECT SID, SEQ#, EVENT, P1, P2, P3 from V$SESSION_WAIT where STATE = 'WAITING'
and event not in ('SQL*Net message from client', 'rdbms ipc message', 'smon
timer' , 'pmon timer')
order by sid
/ Received on Mon May 08 2006 - 15:50:33 CDT