Performance Analysis and Optimization Techniques for Oracle Relational Databases

Almeida, Fernando; Silva, Pedro; Araújo, Fernando

doi:10.2478/cait-2019-0019

Abstract

Databases provide an efficient way to store, retrieve and analyze data. Oracle relational database is one of the most popular database management systems that is widely used in a different variety of industries and businesses. Therefore, it is important to guarantee that the database access and data manipulation is optimized for reducing database system response time. This paper intends to analyze the performance and the main optimization techniques (Forall, Returning, and Bulk Collect) that can be adopted for Oracle Relational Databases. The results have shown that the adoption of Forall and Bulk Collect approaches bring significant benefits in terms of execution time. Furthermore, the growth rate of the average execution time is lower for Bulk Collect than Forall. However, adoption of Returning approach doesn’t bring significant statistical benefits.

References

1. Blaheta, R., K. Georgiev, O. Jaki, R. Kohut, S. Margenov, J. Stary. High Performance Computing in Micromechanics with an Application. – Cybernetics and Information Technologies, Vol. 17, 2017, No 5, pp. 5-16. https://doi.org/10.1515/cait-2017-005010.1515/cait-2017-0050
Open DOI Search in Google Scholar Back to article
2. Dimitrov, D., E. Atanassov. Accounting Services for Heterogeneous Computing Resources. – Cybernetics and Information Technologies, Vol. 17, 2017, No 5, pp. 81-88. https://doi.org/10.1515/cait-2017-005710.1515/cait-2017-0057
Open DOI Search in Google Scholar Back to article
3. Hellerstein, J., M. Stonebraker, J. Hamilton. Architecture of a Database System. – Foundations and Trends in Databases, Vol. 1, 2007, No 2, pp. 141-259. https://doi.org/10.1561/190000000210.1561/1900000002
Open DOI Search in Google Scholar Back to article
4. Feuerlicht, G. Database Trends and Directions: Current Challenges and Opportunities. – In: J. Pokorný, V. Snásel, K. Richta, Eds. Dataeso. 2010, pp. 163-174. CEUR-WS.org.
Search in Google Scholar Back to article
5. Moniruzzaman, A., S. Hossain. NoSQL Database: New Eraof Databases for Big Data Analytics – Classification, Characteristics and Comparison. – International Journal of Database Theory and Application, Vol. 6, 2013, No 4, pp. 1-14.
Search in Google Scholar Back to article
6. Nayak, A., A. Poriya, D. Poojary. Typeof NOSQL Databases and Its Comparison with Relational Databases. – International Journal of Applied Information Systems (IJAIS), Vol. 5, 2013, No 4, pp. 16-19. https://doi.org/10.5120/ijais12-45088810.5120/ijais12-450888
Open DOI Search in Google Scholar Back to article
7. Damesha, H. Object Oriented Database Management Systems-Concepts, Advantages, Limitations and Comparative Study with Relational Database Management Systems. – Global Journal of Computing Science and Technology: Software & Data Engineering, Vol. 15, 2015, No 3, pp. 1-9.
Search in Google Scholar Back to article
8. Liu, H. Oracle Database Performance and Scalability: A Quantitative Approach. Wiley-IEEE Computer Society, New Jersey, 2011.10.1002/9781118135532
Search in Google Scholar Back to article
9. Feuerstein, S., B. Pribyl. Oracle PL/SQL Programming. Sebastopol, O’Reilly Media, 2014.
Search in Google Scholar Back to article
10. Darwen, H. An Introduction to Relational Database Theory. London, Bookboon, 2010.
Search in Google Scholar Back to article
11. Silberschatz, A., H. Korth. Database System Concepts. Columbus, McGraw-Hill Education, 2010.
Search in Google Scholar Back to article
12. Greenwald, R., R. Stackowiak, J. Stern. Oracle Essentials – Oracle Database 11g. Sebastopol, O’Reilly Media, 2008.
Search in Google Scholar Back to article
13. Medhi, S., H. Baruah. Relational Database and Graph Database: A Comparative Analysis. – Journal of Process Management and New Technologies, Vol. 5, 2017, No 2, pp. 1-9. https://doi.org/10.5937/jouproman5-1355310.5937/jouproman5-13553
Open DOI Search in Google Scholar Back to article
14. Colley, D., C. Stanier. Identifying New Directions in Database. – Performance Tuning, Vol. 121, 2017, pp. 260-265. https://doi.org/10.1016/j.procs.2017.11.03610.1016/j.procs.2017.11.036
Open DOI Search in Google Scholar Back to article
15. Shasha, D., P. Bonnet. Database Tuning: Principles, Experiments, and Troubleshooting Techniques. Burlington, Morgan Kaufmann Publishers, 2002.10.1145/564691.564799
Search in Google Scholar Back to article
16. Ziauddin, M., D. Das, H. Su, Y. Zhu, K. Yagoub. Optimizer Plan Change Management: Improved Stability and Performance in Oracle 11g. – In: Proc. of PVLBD’08 Conference, Auckland, New Zealand, 2008, pp. 1346-1355.10.14778/1454159.1454175
Search in Google Scholar Back to article
17. Pavlo, A., E. Paulson, A. Rasin. A Comparison of Approaches to Large-Scale Data Analysis. – In: Proc. of PVLBD’09 Conference, New York, USA, 2009, pp. 165-178.10.1145/1559845.1559865
Search in Google Scholar Back to article
18. Corlatan, C. M. Lazar, V. Luca, O. Petricica. Query Optimization Techniques in Microsoft SQL Server. – Database Systems Journal, Vol. 5, 2014, No 2, pp. 33-48.
Search in Google Scholar Back to article
19. Pons, A. Database Tuning and Its Rolein Information Technology Education. – Journal of Information Systems Education, Vol. 14, 2003, No 4, pp. 381-387.
Search in Google Scholar Back to article
20. Connolly, T., C. Begg. Database Systems: A Practical Approach to Design, Implementation, and Management. New York, Pearson Education, 2014.
Search in Google Scholar Back to article
21. Coronel, C., S. Morris. Database Systems: Design, Implementation, & Management. New Hampshire, Cengage Learning, 2018.
Search in Google Scholar Back to article
22. Mullins, C. Database Administration: The Complete Guide to DBA Practices and Procedures. Boston, Addison-Wesley Professional, 2012.
Search in Google Scholar Back to article
23. Pratt, P., M. Last. Concepts of Database Management. New Hampshire, Cengage Learning, 2014.
Search in Google Scholar Back to article
24. Tiwari, V. Oracle Performance Tuning (for Oracle DBAs). – International Journal of Human Computer Interaction and Data Mining, Vol. 1, 2018, No 1-2, pp. 30-36.
Search in Google Scholar Back to article
25. Hellström, I. Oracle SQL & PL/SQL Optimization for Developers Documentation. Oracle Media Networks. Retrieved 10 November 2017. https://media.readthedocs.org/pdf/oracle/latest/oracle.pdf
Search in Google Scholar Back to article
26. Oracle. Benefits of PL/SQL. Oracle Academy. Retrieved 22 November 2017. http://web.cerritos.edu/hohly/SitePages/Oracle/PL_SQL/PLSQL_course_materials%206_07/PLSQL_s01_l02.pdf
Search in Google Scholar Back to article
27. Bisset, K., J. Chen, S. Deodhar, X. Feng, Y. Ma, M. Marathe. Indemics: An Interactive High-Performance Computing Framework for Data Intensive Epidemic Modeling. – ACM Transaction on Modelling and Computer Simulation, Vol. 24, 2014, No 1, pp. 1-54. https://dx.doi.org/10.1145%2F250160210.1145/2501602420712825346586
Search in Google Scholar Back to article
28. Kaula, R. Operational Intelligence through Performance Trends: An Oracle Prototype. – International Journal of Business Intelligence and Systems Engineering, Vol. 1, 2017, No 2, pp. 140-165. https://doi.org/10.1504/IJBISE.2017.08868610.1504/IJBISE.2017.088686
Open DOI Search in Google Scholar Back to article
29. Almeida, F., J. Monteiro, J. Ferreira. Building an Effective Data Warehousing for Financial Sector. – Automatic Control and Information Sciences, Vol. 3, 2017, No 1, pp. 16-25. http://dx.doi.org/10.12691/acis-3-1-410.12691/acis-3-1-4
Search in Google Scholar Back to article
30. Poljak, R., P. Poscic, D. Jaksic. Comparative Analysis of the Selected Relational Database Management Systems. – In: Proc. of International Convention on Information and Communication Technology, Electronics and Microelectronics (MIPRO’17), Opatija, 2017, pp. 1496-1500. https://doi.org/10.23919/MIPRO.2017.797365810.23919/MIPRO.2017.7973658
Search in Google Scholar Back to article
31. Almeida, F. Developing Effective PL/SQL. Palo Alto, ISSUU Publishing, 2016.
Search in Google Scholar Back to article
32. Feuerstein, S. The Bestof Oracle PL/SQL: Must Know Features, Best Practices and New Features in Oracle Database 11g. Toad World. Retrieved 21 December 2017. https://stage.toadworld.com/cfs-file/__key/communityserver-wikis-components-files/00-00-00-00-03/Best-of-Oracle-PLSQL-11g.pdf
Search in Google Scholar Back to article
33. Feuerstein, S., B. Pribyl, C. Dawes. Oracle PL/SQL Language: Pocket Reference. Sebastopol, O’Reilly Media, 2008.
Search in Google Scholar Back to article
34. Gupta, S. Advanced Oracle PL/SQL Developer’s Guide. Birmingham, Packt Publishing, 2016.
Search in Google Scholar Back to article
35. Greenwald, R., R. Stackowiak, G. Dodge, D. Klein, B. Shapiro, C. Chelliah. Professional Oracle Programming. New Jersey, Wrox, 2005.
Search in Google Scholar Back to article
36. Campbell, L., C. Majors. Database Reliability Engineering: Designing and Operating Resilient Database Systems. Newton, Massachusetts, O’Reilly Media, 2017.
Search in Google Scholar Back to article
37. Martin, W., K. Bridgmon. Quantitative and Statistical Research Methods: From Hypothesis to Results. London, Jossey-Bass, 2012.
Search in Google Scholar Back to article
38. Creswell, J. Research Design: Qualitative, Quantitative, and Mixed Methods Approaches. New York, SAGE Publications, 2013.
Search in Google Scholar Back to article
39. Queirós, A., D. Faria, F. Almeida. Strengths and Limitations of Qualitative and Quantitative Research Methods. – European Journal of Education Studies, Vol. 3, 2017, No 9, pp. 369-387. https://doi.org/10.5281/zenodo.88708910.5281/zenodo.887089
Open DOI Search in Google Scholar Back to article

Performance Analysis and Optimization Techniques for Oracle Relational Databases

Abstract

Paradigm

My account