Re: Excessive Logical and Physical I/O

Here you go... (See my reply to Brian for more info on this).

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       19     31.66     643.47      82538    4141582         70         263
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       21     31.66     643.48      82538    4141582         70         263

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23

Matt

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c39pe7$f5c$1_at_titan.btinternet.com>...
> 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 Thu Mar 18 2004 - 02:30:46 CST