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

How embarrassing.

My script is one I downloaded way back when and adapted. It suffers rather from bad maths. ASH samples occur every second. I'd want to be dividing by 1440 and not 2880 then.

On 4/20/06, Niall Litchfield <niall.litchfield_at_gmail.com> wrote:
> On 4/20/06, Schultz, Charles <sac_at_uillinois.edu> 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
>

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