Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Data Loading

The data loading into the two configurations, SSD and SCSI/ATA, was performed using the SQLLoader script previously shown in Chapter 2. The dependency of the various tables was determined and the load script created to avoid data dependency issues. Each of the flat files (10 each for the large tables) was loaded in numerical sequence and the timing data recorded. The data loading was used as a test of the insert speed of the various configurations.

Overall the load speeds for the SCSI 2-disk array and the ATA 7-Disk array were found to be nearly identical. Only one set of statistics from the ATA second series of loads will be compared. Figure 3.1 shows a graph of load time comparisons for the PARTSUPP table between the SSD and SCSI/ATA data loads. The average speed improvement for data loading was 30% for SSD over SCSI/ATA arrays

The examples used are the partsupp table loads, each consisting of 1.6 million rows. The complete results for the partsupp table are in Table 3.1.

The rows per second insert rate is shown in Table 3.1 and also reflects the performance improvements as demonstrated by the data represented in the graph in Figure 3.2.

The SSD drives loaded 66 percent more rows per second. Although this doesn’t correlate to the 30 percent speed improvement shown in Figure 3.1, the 30% (40% maximum) improvement does correlate with the findings from other testers listed in Chapter 1. The complete data showing all load statistics are provided in Appendix C

Figure 3.3 shows the average load times for each table compared between the SSD and ATA loads.

As can be seen, with the possible exception of the nation and region tables the average load time for the SSD load was 30% less than the average load time for ATA. This data is shown in tabular form in Table 2.

This performance was seen across the board with the exception of the two inconsequential small tables which can be disregarded. Figure 3.3 shows the average rows per second during loading for all tables for both SSD and SCSI.

Figure 3.3 shows graphically that SSD loads rows considerably faster than ATA drives The reason for the mismatch between row loading efficiency (up to 60%) and the difference in load times (up to 40 %) is still not clear but could be due to the primary key index insert time which may not have been properly captured in the statistics. Table 3.3 shows the actual values that support Figure 3.3.

In conclusion, the SSD array outperformed the SCSI and ATA arrays in load time by an average of 30%. This means that for a data load sequence that may take 3 hours on a SCSI or ATA array, it would only require close to 2 hours on a SSD array. In addition the rows per second load rate was nearly 60% faster. So, in a situation where there are no other factors such as primary key index builds, the actual load time may be even better than the 30% average peak performance shown.

Next, it is time to cover the index build efficiency.

The above book excerpt is from:

Oracle Solid State Disk Tuning

High Performance Oracle tuning with RAM disk

ISBN 0-9744486-5-6  

Donald K. Burleson & Mike Ault

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