Re: Spotting the real cause of a Production slowdown (10g)
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: sac@xxxxxxxxxxxxx
- Date: Thu, 20 Apr 2006 15:50:33 +0100
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@xxxxxxxxx> wrote:
> 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
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
