Oracle Performance Tuning

Abstract

This study aims to explain some effective tuning strategies for ensuring the best performance for Oracle database applications. Studying the overall concepts behind Oracle tuning, since Oracle is currently considered as one of the most powerful and strong RDBMS. Oracle allows a large part of memory to be allocated for the database. This makes Oracle suitable for systems that contain large database and also it provides a high level of security and contains an intelligent optimizer especially in version 9i and 10g. These are some of the reasons for choosing Oracle database for this research. The thesis concern with identifying the meaning of tuning process and the goals of tuning and steps that must be taken towards best tuning in general. The thesis concentrates on memory tuning and application tuning since they are the major parts that have greater effect on performance. Experience shows that more than 80 percent of all performance gains are to be realized at the application level. Finaly the researcher presents an implementation, as a case study of Student Registration System of Sudan University of Science and Technology. Examining the queries, the researcher has managed to achieve over 50% improvement in performance. This shows that in spite of query optimization utilities, performance still requires a lot of diligence and expertise.