Oracle Database Tips by Donald Burleson

SCSI/ATA Scenario Comparisons

Recall that the testing architecture utilized consisted of both a SCSI array and an ATA array. The SCSI array consisted of two striped 15K RPM 36 Gigabyte Cheetah SCSI disks with a strip width of 128K, which is a standard stripe width used in many Oracle installations. And when that array failed, a 7 disk ATA array of 7200 RPM 200 gigabyte ATA drives striped at 64K.

Due to the failure of the SCSI array, its initial results were compared to the initial ATA results. Since the difference for query performance was less than 5%, the SCSI results were discarded and the ATA results used for the tests.

In the SCSI/ATA query runs there was more diversity in the results as evidence by the data in Table 4.2.

Given that the reads were spread over 7 disks in the ATA array which provided an effective possible read profile of nearly a thousand or more I/O's/sec and assuming an I/O of at least one stripe width would translate into 64 megabyte to 128 megabyte I/O's per second. 64k is standard I/O for most UNIX environments with a maximum physical I/O possible of roughly 1 megabyte. From the env dump, the Linux system was set at the default of 128K maximum physical I/O.

The ATA array was expected to perform much better than it did in these tests. Here is a look at the SCSI/ATA results.

Standard SCSI/ATA Runs

Runs 1 and 2 were standard query runs with the exception that they were performed using nologging and no archivelog mode. With notable differences, in most cases run 1 query times were longer than run 2 times due to the increased need for disk reads to populate the data cache in the Oracle memory system. Table 4.2 shows that several queries weren't able to run to completion and overall performance was poor.  

Run 3 with Temporary and Undo on SSD

Run 3 placed the temporary and undo segments on the SSD array. This improved the response time for queries that used many sorts or hash joins such as 8, 10 and 13. This allowed 13 to finish in less than 24 hours. But overall, the gains did not outweigh the losses. For this size database the hash and sort activity was able to complete within the 2 gigabytes allowed for the pga_max_allocation  set point. Moving the temporary tablespace to SSD was not a big factor in performance. Since nologging was used while performing query activity, the undo requirements were minimal. Consequently, moving the undo segments also had little effect on performance. However, in databases where there were more temporary and undo related waits, this would play more of a factor in query performance. 

Run 4 with DATA on SSD

In run 4 the data segments were moved to the SSD and the temporary and undo segments were placed back on normal drives. While taking into consideration the two queries that could not complete, the total run time still improved by a factor of 3. And in many cases the run times even improved by a factor of 10.

Dramatic improvement is to be expected considering the large number of scattered and sequential read waits experienced by the instance. While moving the data files to the SSD arrays may not affect the number of such waits, it dramatically affects the duration of each wait.

Run 5 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 5 the db_cache_size was reduced by 50% from 1 gigabyte to 500 megabytes.  The results were surprising in that the overall run time was reduced by 8 percent with most queries showing some improvement in runtime. However, this may be affected by several queries that didn't complete before having completed run 4 and populating the smaller cache with useful data. This would be one area of additional research for future work.

This is an excerpt from the book Oracle Solid State Disk Tuning.  You can get it for more than 30% by buying it directly from the publisher and get immediate access to working code examples.