Re: SQL tuning nightmare - db file sequential reads

joshic75_at_gmail.com wrote:
> Hi Steve and Dan,
>
> Many thanks for replying. My apologies - I should have mentioned the
> version before hand.
>
> Anyway its 9.2.0.6 EE. OS is Solaris 8.
>
> I had a chat with our DBA who said that statspack shows the average db
> file sequential read times between 2 to 11 ms, which seems to be quite
> healthy.
>
> The hardware is quite powerful with 24 CPUs, 16GB RAM , SAN storage
> etc. So I doubt that it will be a problem at the OS level.
>
> Of late I am seeing this behaviour (huge db file sequential read waits
> on the same block) on different databases. So I was wondering if
> anybody else had seen this behaviour before and knows the reason behind
> it.
>
> Ours is a data warehousing environment, so the queries process large
> volumes of data. However we still have a setup that favours indexed
> scans (no partitioning, parallelism, bitmap indexes as yet). Also we
> have manipulated the system statistics to reduce the SREADTIM compared
> to MREADTIM to favour indexed scans. I sometimes wonder if that might
> be the reason for seeing such behaviour.
>
> Would anybody like to throw more light on this?
>
> Many thanks,
> Charu.

Charu,

Have you set a 10046 trace? 900 seconds for single block read is not good. 3 seconds is the timeout on this event before it is posted again, so there is some reason that Oracle cannot get that block. I would be curious to see what the raw 10046 trace looks like. Does it literally look something similar to the following?

WAIT #1: nam='db file sequential read' ela= 3000000 file#=263
block#=50960 blocks=1 obj#=0 tim=64996433721
WAIT #1: nam='db file sequential read' ela= 3000000 file#=263
block#=50960 blocks=1 obj#=0 tim=64999433721
WAIT #1: nam='db file sequential read' ela= 3000000 file#=263
block#=50960 blocks=1 obj#=0 tim=65002433721
WAIT #1: nam='db file sequential read' ela= 3000000 file#=263
block#=50960 blocks=1 obj#=0 tim=65005433721
WAIT #1: nam='db file sequential read' ela= 3000000 file#=263
block#=50960 blocks=1 obj#=0 tim=65008433721

Regards,

Steve Received on Tue Jul 04 2006 - 12:36:58 CDT