Re: SQL tuning nightmare - db file sequential reads

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. Received on Tue Jul 04 2006 - 12:10:19 CDT