Why so many consistent block reads?

From: Dave Wotton <Dave.Wotton_at_it.camcnty.gov.uk>
Date: 1997/02/24
Message-ID: <5esacu$9bh@dns.camcnty.gov.uk>#1/1

Hi,

can anyone explain the following results from tkprof?

SELECT COUNT(RFW_ORDER_NUMBER) FROM REQUEST_FOR_WORK WHERE RFW_DATE IS NOT NULL
   AND CANCELLATION_DATE IS NULL
   AND COMPLETE_INDICATOR != 'Y'
   AND CONTRACTOR_PARTY_ID = NVL(:b1,CONTRACTOR_PARTY_ID)    AND RFW_ORDER_NUMBER LIKE UPPER(:b2)

            count     cpu    elap    phys      cr     cur    rows
Parse:          4       0       0       0       0       0
Execute:       22       0       0   84252 1220685       0       0
Fetch:         22       0       0       0       0       0      22

Execution plan:
SORT (GROUP BY)
TABLE ACCESS (BY ROWID) OF 'REQUEST_FOR_WORK' INDEX (UNIQUE SCAN) OF 'REQUEST_FOR_WORK_004' (UNIQUE) Why is my query performing such huge amounts of I/O?

RFW_ORDER_NUMBER is the unique key for this table. REQUEST_FOR_WORK_004 is the unique index on this column. Although the SQL is constructed to allow the user to enter wild-card characters, in practice all they enter is a complete order-number ( note: 22 executions, 22 rows returned ). I *believe* the users are providing a null value for :b1.

All appears correct: the correct index is being used to perform the query. It should be fast.When I try to reproduce the problem, I get instant response. So why does tkprof report such large amounts of physical I/O and consistent block reads? ( and the users report slow response times? )

It is possible that there may be some chaining in the table, but surely this can't account for such big numbers?

Please note: we are still running Oracle RDBMS V6.0.36.5.1 ( so rule-based optimiser only )

Any help much appreciated.

Dave. Received on Mon Feb 24 1997 - 00:00:00 CST