Re: Statspack analysis

"Peter Laursen" <ptl_at_REMOVEedbgruppenTHIS.dk> wrote in message news:442c5d4f$0$12109$ba624c82_at_nntp02.dk.telia.net...
>> a quarter - v$sysstat shows
>> 61 centisecond per second CPU.
>> (of course, we don't know how many CPUs
>> you have - but maybe it's just the one).
>
> its 4 cpus's, but the server also hosts a number of automated programs
> that access the database.
>
>> But the most anomalous figures are:
>>> enqueue releases 897,706 217.8 556.5
>>> enqueue requests 897,705 217.8 556.5
>> coupled with calls on v$rowcache
>>> dc_segments 892,027 0.0 0 0
>>> 1,219 dc_tablespaces 891,241 0.0 0 0
>>> 4
>>
>> At a guess, you have an object that is growing (and possibly shrinking)
>> very rapidly in a tablespace declared with a very small extent size, I
>> can't think of any other reason why these figures might be so extreme.
>
> Hmm, could that be in temp tablespace? I think its a locally managed
> uniform 1M or 4M extents. I will look at it tomorrow. How could I find out
> what object that is growing and/or shrinking fast?
>
> TIA
> Peter Laursen
>
>

It could be a temporary tablespace - but only if you have a permanent tablespace with very small extents assigned as the temporary tablespace; and it's not really consistent with your statistics and parameters for sorting.

Perhaps the next place to look is x$ksqst (logged on as SYS) to find out which lock type (ksqsttyp is the really busy one. You'll have to do take a couple of snapshots of this object at a critical period to get some useful numbers. (In 9i, this x$ is exposed as v$enqueue_stat - but you don't have that in 8i).

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html