Re: Excessive Logical and Physical I/O

Matt wrote:
>
> Hello all,
>
> Oracle 8.1.7.2.1 HP-UX 11 (64-bit)
>
> I have a query which seems to be performing too many block visits.
>
> The table consists of 8,500 blocks but when Full Table Scanning the
> table Oracle seems to be doing over 4,000,000 logical I/Os and over
> 80,000 disk reads.
>
> This seems much too high for a table of this size.
>
> I would have expected to see only 8,500 block visits at the very most
> with even less physical reads....
>
> SQL text:
>
> SELECT
> S.BUSINESS_UNIT,
> S.TM_SHOP_CODE,
> S.TM_DATE,
> S.TM_DIV_CODE,
> S.TM_LABOR_CLASS2,
> S.TM_EARNED_HOURS,
> S.TM_TWT_HOURS,
> S.TM_PEFF_PCT,
> S.TM_PEFF_PCT_PREV,
> S.TM_PEFF_TRGT_PCT,
> S.TM_PEFF_TRGT_PREV,
> S.HEAD_COUNT,
> S.TM_ATTEND_AVG,
> S.STRT_COREJOB_TWT,
> S.OT_COREJOB_TWT ,
> S.OVER_TIME,
> S.TM_CJHR_STD
> FROM PS_TM_PEFF_DIVSUM S
> WHERE S.BUSINESS_UNIT = 'TMUK' AND
> S.TM_DATE_TYPE = 'D' AND
> S.TM_DATE BETWEEN '01-JAN-2004' AND '31-JAN-2004' AND
> S.TM_SHOP_CODE <> '___'
> ORDER BY S.TM_DATE, S.BUSINESS_UNIT, S.TM_DIV_CODE, S.TM_SHOP_CODE,
> S.TM_LABOR_CLASS2
>
> Rows Returned: 263
>
> Explain Plan:
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 263 SORT ORDER BY
> 263 TABLE ACCESS FULL PS_TM_PEFF_DIVSUM
>
> Stats:
>
> Disk Reads: 82,538
> Logical Reads: 4,141,652
>
> My first suspicions were due to the sort, but I would have expected
> the sorts to be done via direct I/O (i.e. bypassing the buffer cache).
>
> Anyone have any ideas why this is performing so much work...
>
> Matt

Are you sure that your query does not have to build a read-consistent image of the blocks, thus necessitating many trips to your undo or rollback segments?

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"