Re: Statspack performance problem

Thanks Mogen. Let's see if something comes of it.

In the meantime, I only collect lvl 0 in that database.

Jared

On Friday 10 January 2003 13:29, Mogens Nørgaard wrote:
> Juan Loaize and the guys in ST (Server Technologies) in Oracle
> Development added some index structures to certain x$-things (and
> thereby v$-things) in 7.3 and onwards, I think. However, v$sqlarea still
> has to do a group by, which sucks. v$sql is faster, but has more data,
> of course.
>
> I've CC'ed Bjorn Engsig from Miracle on this - he worked on some
> optimisations on StatsPack at Oracle before quitting. He (or maybe
> Graham Wood) could perhaps help here.
>
> Mogens
>
> Jared.Still_at_radisys.com wrote:
> >That seems to have been the problem.
> >The snap ran in 2 seconds after setting the level to 0.
> >This database has 50k+ SQL statements cached, usually.
> >
> >Setting it back to 5 is taking a very long time. Apparently
> >there is a lot going on behind the scenes when changing
> >the snap level.
> >
> >In checking v$session_wait I find a lot of waits on latch free
> >and direct path write. This session has not done any sorts
> >to disk, so I'm not sure why there are direct path writes.
> >
> >Maybe there are LOB's in Perfstat schema? Dunno,
> >have not looked.
> >
> >Thanks,
> >
> >Jared
> >
> >
> >
> >
> >
> >
> >John Kanagaraj <john.kanagaraj_at_hds.com>
> >Sent by: root_at_fatcity.com
> > 01/08/2003 01:55 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com> cc:
> > Subject: RE: Statspack performance problem
> >
> >
> >Jared,
> >
> >Did you snap with the default value of 5? If so, then the SNAP proceduer
> >will have to scan / sort V$SQLAREA and that can be very time-consuming. If
> >you are CPU starved or have very high shared_pool access or issues in that
> >area, then this could explain it.... Try this with a snap level of 0 and
> >let
> >us know if this solves the issue.
> >
> >John Kanagaraj
> >Oracle Applications DBA
> >DBSoft Inc
> >(W): 408-970-7002
> >
> >What would you see if you were allowed to look back at your life at the
> >end
> >of your journey in this earth?
> >
> >** The opinions and statements above are entirely my own and not those of
> >my
> >employer or clients **
> >
> >>-----Original Message-----
> >>From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
> >>Sent: Wednesday, January 08, 2003 10:45 AM
> >>To: Multiple recipients of list ORACLE-L
> >>Subject: Statspack performance problem
> >>
> >>
> >>List,
> >>
> >>Is anyone aware of performance problems with statspack on 8.1.6.3 on
> >>Windoze?
> >>
> >>By performance problem, I mean that statspack.snap runs for several
> >>minutes
> >>before I eventually kill it.
> >>
> >>Trying to check on MetaLink, but it isn't responding at the moment.
> >>
> >>Thanks,
> >>
> >>Jared
> >>
> >>--
> >>Please see the official ORACLE-L FAQ: http://www.orafaq.net
> >>--
> >>Author:
> >> INET: Jared.Still_at_radisys.com
> >>
> >>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> >>San Diego, California -- Mailing list and web hosting services
> >>---------------------------------------------------------------------
> >>To REMOVE yourself from this mailing list, send an E-Mail message
> >>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >>the message BODY, include a line containing: UNSUB ORACLE-L
> >>(or the name of mailing list you want to be removed from). You may
> >>also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).