Re: Spotting the real cause of a Production slowdown (10g)

On 4/20/06, Schultz, Charles <sac@xxxxxxxxxxxxx> wrote:
>
>
> Thanks. Yes, I agree with what you are saying. One of the fallouts from our 
> case with Oracle Support is that we are bumping up against a bug whereby the 
> 10g SGA auto-sizer will inflate the shared pool indiscriminately, even if you 
> are flooding it with literal statements (counter-intuitive, as you pointed 
> out below).
>
> The problem with finding many copies of sql statements that look similar is 
> how granular do you go? The common method is to substring sql_text from 
> v$sql. How many characters do you use? To be empirical, one would have to 
> loop through all reasonable numbers (ie, between 7 and 150). Unless you know 
> something I don't, which is entirely possible. *grin*
>
> Good point about which processes are consuming CPU - I did not even think 
> about that. How does one use the Historical view of EM to determine that 
> information? All I can find is "CPU used" under performance, and if I try to 
> break it down, I get user processes. But I will keep looking.
>

If you are licensed for it you could look in V$ACTIVE_SESSION_HISTORY,
something like this sqlscript perhaps


col event for a64
col elapsed_time for 9,999,999.99
set lines 120
accept period prompt "Enter period to report on in minutes [15]:" default 15

select sess.sid
, sess.username
, ash.event
, sum(ash.wait_time + ash.time_waited) elapsed_time
from
v$active_session_history ash
, v$session sess
where
ash.sample_time between sysdate - &period/2880 and sysdate
and ash.session_id = sess.sid
and ash.session_serial# = sess.serial#
group by sess.sid, sess.username, ash.event
order by username,event
/






--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l

Other related posts: