Re: Statspack performance problem

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: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

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).