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.

I am going to morph from instructor to student and await Jonathan Lewis who will hopefully respond.

Until then I will paraphrase what I have heard Jonathan say: Give the optimizer the best, most complete, and most accurate information from which to make decisions. Altering your system stats may be part of the problem but it may also be helping some queries while hurting others. I'd certainly be inclined to return them to their default and see what affect it would have (hopefully on a test box).

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org