On Jun 8, 6:33 pm, bdbafh <bdb..._at_gmail.com> wrote:
> 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
What a fine thread! - CDOS at it's best. I appreciate the helpful tone
and scientific spirit.
Erik
Received on Fri Jun 08 2007 - 15:21:51 CDT
