Re: Long wait times for db file scattered read

comments embedded
"John Oracle DBA" <jasnoke_at_msn.com> wrote in message news:aea439d2.0403171242.b60d6b9_at_posting.google.com...
> I'm running this query during different windows. The first run
> executes in about half the time as the second. The vast majority of
> wait time in both runs, and the difference wait time between the runs
> comes from waits to cache blocks from one particular table. Note those
> blocks have been fetched after an index scan on that table.
>
> Does anyone know if the difference in physical reads alone would
> account for such a difference in wait time? What would cause such
> waits in either case? The Sys Admin has told me there's no I/O
> bottleneck at the system level.
>
> Thanks in advance,
>
> John
>
> RUN #1

<snip>
> Event
> Times Count Max. Total Blocks
> waited on
> Waited Zero Time Wait Waited Accessed
> -----------------------------------------------------------------
> --------- --------- --------- --------- ---------
> db file sequential read (single block scan)......................
> 517427 0 1.08 635.85 517427

> RUN #2

<snip>
> Event
> Times Count Max. Total Blocks
> waited on
> Waited Zero Time Wait Waited Accessed
> -----------------------------------------------------------------
> --------- --------- --------- --------- ---------
> db file sequential read (single block scan)......................
> 619315 0 2.39 1440.64 619315

unless my maths is wrong you are waiting twice as long on average for each read in the second run, however the average wait appears to be 0.001 or 0.002 of a second in the two cases, which rather suggests to me you are getting the data from a cache on the disk subsystem. I'd be looking at whether I really need to request 1million blocks from this table when I'm going to end up with 69 rows returned. What does the sql look like?

btw is this 10g or a third party tool formatting the report like this?

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************