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?

I would suggest working with your dba first. If they cannot help then work on at least getting a 10046 trace at level 12 with you dba's assistance.

Run that through a resource profiler like the free clone of the hotsos profiler that Egor wrote. ( orarsp ).

Read Cary Millsap's book on "Optimizing Oracle Performance" and follow the methodology. Bada bing ( rim shot sound ) ... problem solved! Received on Tue Jul 04 2006 - 19:37:37 CDT