Re: resolving buffer busy waits

From: Noons <wizofoz2k_at_yahoo.com.au>
Date: Thu, 18 Sep 2003 22:24:15 +1000
Message-ID: <3f69a50b$0$13416$afc38c87@news.optusnet.com.au>

"Casey" <cdyke_at_corp.home.nl> wrote in message news:8bc6b8d7.0309171010.26939c34_at_posting.google.com...

call     count       cpu    elapsed       disk      query    current
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

total      146      0.11    1405.75        164       1118          0
      391



> Rows     Row Source Operation

> -------  ---------------------------------------------------

>     162  NESTED LOOPS

>     167   TABLE ACCESS BY INDEX ROWID {table 1}

>     167    INDEX RANGE SCAN (object id 3020)

>     162   TABLE ACCESS BY USER ROWID {table 2}

> OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 2498 1.95 1459.09 253 7080 2400
> 1744

This is just way excessive. 253 disk I/Os to get <2K rows? With elapsed of 1500? Indexed on top? What the heck is driving that disk, steam?

Something very wrong in that I/O. It CANNOT be doing that many I/Os for so few rows. You sure there are no chained or migrated rows? How large are the rows? How many rows per block, min, avg (numbers above suggest <7 for totals, but 2 for single statement!), max? Anything out of vanilla plain tables about them, like object types or LONG or CLOB? Is it reading/locking the rows?

Another thing: are the rows physically sorted by the values of the index range scan used in this query? If they aren't, that could be causing all that I/O. Try sorting them by that order and see if it makes a diff.

Way excessive. Doesn't make any sense. Any errors in OS disk logs? Move that datafile to another f/s and see what happens. Pre-allocate for max size.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam

Received on Thu Sep 18 2003 - 07:24:15 CDT