Since your execution plan came from a tkprof output.
could you also post the summary statistics that appear
between the SQL text and the plan.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0403170312.17489ec1_at_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 - 09:04:39 CST
