Re: Statspack analysis

"Peter Laursen" <ptl_at_REMOVEedbgruppenTHIS.dk> wrote in message news:442c039a$0$11696$ba624c82_at_nntp02.dk.telia.net...
> Hi,
>
> Users complain about a recent drop in response times. I did a statspack
> report. Would someone take a peek and see if there is something unusual in
> it?
>
> Snaps were made about an hour apart. During that hour i used one of the
> application functions, that users report are now suddenly slower. This
> function basicly runs one sql which then used 99,5% of total reads in that
> hour.
> Another SQL selecting from dba_data_files, dba_extents (counting free
> space in tablespaces?) is using 73% of total buffer gets.
>
> TIA
> Peter Laursen
>

One thing that is clear is that you are doing a reasonable amount of work in the hour and 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).

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.

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