Re: Oracle Performance -- Possible Disk Bottleneck

compatible is 9.2.0.0.0

Is there any chance that you would be permitted to upgrade to Oracle 10g R2 and apply the 10.2.0.3 patchset and CPUApr2007 patch (or 10.2.0.3 p5) prior to diagnosing issues?

A great deal more instrumentation and information is available in the most recent release.

I noticed that the db_file_multiblock_read_count is set to 8. What is the stripe size across the 6 disks? (probably 64 KB). I strongly prefer to use 8 disk RAID 10 volumes with an 8KB db_block_size with at least a 256 KB stripe, in which case the db_file_multiblock_read_count could be set to 32. This would allow for a larger number of blocks to be fetched per IOP. That unit may support a stripe size as large as 1024KB. The OS also does, but back in the day of 9.2, that tended to push the CBO toward table scans. Such is not as likely the case in 10.2.

I believe that Jeff Holt published a paper having to do with "aligning block and stripe sizes". I think that its still available from OraPub.com. Did the Dell Engineer that configured the system discuss how this was going to be used? If the LUN used for datafiles has only a 64KB stripe size, you're not going to gain much by increasing the db_file_multiblock_read_count.

Making the existing table scans more efficient would be a possible step at reducing response time, although reducing the number of table scans may prove to be more effective.

If you had a couple of days of downtime available, rebuilding the LUN with an 8 drive RAID 10 volume with a 256 KB stripe size running 10.2.0.3 would be a much better place to start tuning from. You may even get lucky and the 10g R2 Cost Based Optimizer may solve your performance problems. <EG>

-bdbafh Received on Fri Jun 08 2007 - 11:33:50 CDT