Oracle Tips by Burleson

Standard SCSI/ATA Runs

Runs number one and two were standard query runs with the exception they were done as nologging and noarchivelog mode. In many cases, run times for query number one, with a couple of notable differences, were longer than run number two due to increased need for disk reads to populate the data cache in the Oracle memory system.

Run Three with Temporary and Undo on SSD

Run three placed the temporary and undo segments on the SSD array. This improved the response time for queries that used a lot of sorts or hash joins such as eight, ten and 13, allowing 13 to finish in less than 24 hours, but overall the gains did not outweigh the losses.

Run Four with DATA on SSD

In run four, the data segments were moved to the SSD, and the temporary and undo segments were placed back on normal drives. The total run time improved by a factor of three, three days versus nine days, even allowing for the two queries that still could not complete. In many cases, the run times improved by an order of magnitude.

Run Five with Data on SSD and Reduced Memory

In an effort to gauge the importance of setting db cache size when using SSD assets, in run five the db_cache_size was reduced by 50% from one gigabyte to 500 megabytes. The results were surprising in that the overall run time, if query 13a is set to the 86,400 value instead of its actual time, was reduced by eight percent with most queries showing some improvement in runtime. However, this may be an artifact of several queries that did not complete before having completed during run four and populated the smaller cache with useful data. This would be one area of additional research for future work.

The above book excerpt is from:

Oracle RAC & Grid Tuning with Solid State Disk
Expert Secrets for High Performance Clustered Grid Computing

ISBN: 0-9761573-5-7
Mike Ault, Donald K. Burleson

http://www.rampant-books.com/book_2005_2_rac_ssd_tuning.htm