Re: *Very* high 'Fetch/elapse' cell in TKPROF report: cause ?

"Spendius" <spendius_at_muchomail.com> wrote in message news:aba30b75.0408260359.71281f76_at_posting.google.com...
> Hi,
> We're experiencing very bad response times on a DB, this slowlyness
> is also reflected on queries against the data dictionary
> >select segment_type,count(1),sum(bytes) from
> >dba_segments where owner='XXX' group by segment_type;
> gives
> call count cpu elapsed disk query current rows
> ------- ------ ----- ------- ------- ------- ------- -----
> Fetch 2 0.58 3.76 279 8018 0 4
> or
> >select distinct object_type from dba_objects
> >where owner='XXX' order by 1;
> call count cpu elapsed disk query current rows
> ------- ------ ----- ------- ------- ------- ------- -----
> Fetch 4 0.14 1.61 0 350 0 13
>
> We're in 9i.
> What I can't understand is that on the same server we have
> 5 other 9.2 databases with similar config. params (cf. below)
> that do not behave this way, and in particular we have the
> SAME database running in 8i on the same machine, where SELECTs
> like those above respond immediately.
>
> Where do these pretty high fetch durations come from ? Where
> should we look ?
>

Hi Spendius,

You need to determine what is causing the difference between your cpu and elapsed times (3.76 - 0.58 = 3.18 of unknown wait time for your first fetch).

The best way to determine the cause for this missing time is to perform an *extended* sql trace and have a look at the associated wait times (tkprof can pretty it up if you wish on 9.2).

I recently presented something on how to do all this. You may find the associated slides of use
(http://www.actoug.org.au/Downloads/Extended%20SQL%20Trace.pdf).

Better still, read Cary Millsap's book.

Cheers

Richard Received on Thu Aug 26 2004 - 10:05:38 CDT