Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

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.

LOAD FILE

ROW COUNT

SSD LOAD SEC

ROWS PER SEC

SSD/SCSI

partsupp1

1600000

51.58

31019.775

0.559679

partsupp2

1600000

52.88

30257.186

0.60545

partsupp3

1600000

53.53

29889.781

0.707414

partsupp4

1600000

52.47

30493.615

0.512853

partsupp5

1600000

54.88

29154.519

0.674119

partsupp6

1600000

54.43

29395.554

0.688377

partsupp7

1600000

52.94

30222.894

0.505635

partsupp8

1600000

55.56

28797.696

0.721746

partsupp9

1600000

53.13

30114.813

0.628906

partsupp10

1600000

53.39

29968.159

0.516145

 

LOAD FILE

ROW COUNT

 SCSI LOAD SEC

ROWS PER SEC

partsupp1

1600000

92.16

17361.11

partsupp2

1600000

87.34

18319.21

partsupp3

1600000

75.67

21144.44

partsupp4

1600000

102.31

15638.74

partsupp5

1600000

81.41

19653.61

partsupp6

1600000

79.07

20235.23

partsupp7

1600000

104.7

15281.76

partsupp8

1600000

76.98

20784.62

partsupp9

1600000

84.48

18939.39

partsupp10

1600000

103.44

15467.9

Table 3.1: Comparison of partsupp Table Loads between SSD and SCSI/ATA

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.

TABLE NAME

SSD AVERAGE LOAD TIME

ATA AVERAGE LOAD TIME

Customer

8.61

12.35

Lineitem

587.80

802.50

Nation

0.07

0.05

Order

132.34

175.02

Part

15.66

21.91

Partsupp

53.48

88.76

Region

0.12

0.08

Supplier

0.60

0.82

Table 3.2: Average Load Times Per Table

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.

TABLE

SSD R/S

ATA R/S

Customer

34892.81

24493.74

Lineitem

20415.51

15009.14

Nation

357.14

500.00

Order

22675.75

17254.85

Part

25570.33

18574.75

Partsupp

29931.40

18282.60

Region

41.67

62.50

Supplier

33614.46

24489.11

Table 3.3: Rows Per Second Averages

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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter