Excessive Logical and Physical I/O

From: Matt <mccmx_at_hotmail.com>
Date: 17 Mar 2004 03:12:48 -0800
Message-ID: <cfee5bcf.0403170312.17489ec1@posting.google.com>

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 Received on Wed Mar 17 2004 - 05:12:48 CST