Oracle provides arguably the most advanced and scalable Relational Database Management System (RDBMS) and is the industry leader in the RDBMS space. One of the most powerful features of Oracle is that it offers advanced partitioning features that are leading edge in the enterprise database market. Generally many RDBMS products offer a range of partitioning options including range, list, hash, and composite portioning techniques. Oracle offers all the standard partitioning techniques mentioned as well as newer advanced techniques available in Oracle versions 10.2 and 11.1.6. In more recent versions Oracle is offering extensive partitioning options with feature that project beyond other RDBMS offerings. Recently here at UNIPLUS, a group of developers were engaged in testing some of the latest capabilities of Oracles new partitioning features. They set out to test some of the advanced partitioning features including multi-column range (10.2), and composite range-range partitioning available in 11.1.6.
Test System
The Developers used an enterprise installation of Oracle on a windows Vista 64x Server. The test system was a small mid-range quad-core server with 6GB ram. The hard disk was standard SATA (2.0) configuration with a transfer speed of 3.0MB/s.
Test Configuration
A small Oracle database of 40GB size was configured with dozens of medium-large size tables with more than 20 million records each. Additionally, several DB schemas were also configured with the same number of tables and column structure across all schemas. The same volume of data was loaded into each schema. Each schema would be used as the sole test-bed for a series of test that would measure the performance of the DB tables based on the assigned test criteria. To setup and measure the performance, the developers decided to configure a standard baseline test schema with un-partitioned tables. Another schema tested standard range partitioning. The next two schemas tested the performance for multi-column range partitioning and range-range composite partitioning configurations. The last two schemas tested the performance of multi-column range and range-range composite partitioning configurations with the use of additional global partitioned indexes on the tables.
Test Method
The tests involved measuring the performance of access and retrieve times from the database. The tests were conducted for both single record parse as well as multi-record parse. Additional test were conducted for retrieve and update time to the database.
Results
The test results from the case study were rather interesting. As expected testing in the non-partitioned schemas provided the longest access time, and thus proved to be the worst performers. The performance measurements improved dramatically in benchmark testing of the access times in the standard range partitioned schema. Next, performance measurements in the multi-column range schemas provided some of the best results in all of the study. A single test result of multi-column range partitioning provided the most outstanding results of the study. However the team was unable to replicate the top performance in the study, in any subsequent test of the multi-column range configuration. As such the team discarded the top result and attributed the said result to an anomaly.
Performance benchmarks conducted in the range-range composite partitioning schema provided nearly identical results to the multi-column range partitioning. However the superior structure and ease of configuration in the range-range composite partitioning was much desired over the cumbersome effort of configuring multi-column range partitions. Addition of global partitioned indexes to the advanced multi-column range and range-range partitioning provided nearly no performance gains. However, this may have to do with the small size of the database. Global partitioned indexes should provide more complex and/or larger database size with desirable performance gains.
Conclusion
Overall the test results were somewhat expected. Partitioning in Oracle provided significant improvement in access time over non-partitioned tables. More significantly, both multi-column range partition and range-range partition configured schemas, dramatically improved the access time over standard range partition schemas. This was rather significant and illustrates the performance gains and usefulness of new partitioning features available in Oracle versions 10.2 and 11.1.6. In conclusion the study would recommend configuring database tables to use advanced partitioning features in Oracle. These features provide significant performance gains over standard Oracle partitioning techniques.
Limitations
The research is based on a case study performance of several Oracle database schemas on Windows platform. Although the study was rather useful, additional research in other platforms would be of significance. A compare-contrast study of the performance of partitioning of different DB platforms (i.e. IBM, Sybase) would be highly desirable. Although the study observed significant performance gains, it is thought that performance gains in testing of larger databases could prove to be even more significant. Finally, performance testing in a number of environments, with different configurations, and different database sizes, could strengthen the results obtained from this study.
Recent Comments