Query Tuning in Oracle Database

Abstract

Oracle database is well suited to use for managing and exchanging of corporate data, especially for companies that have large-scale databases. Even though Oracle can work best, however, there are conditions where Oracle does not perform well and consume more time to perform the query transaction. This is when the database tuning process is needed. This process will solve the problems of low performance and high execution time when accessing data in the database. This study aims to propose an improvement process of query tuning that can reduce queries execution time. The cause of the problems can be discovered by reviewing the source code, List of Value (LOV) and view on the current system. To enhance the query, existing query scripts need to be modified to fit the tuning process. As an evaluation on the effectiveness of the proposed query tuning process, a comparison of study is done. The result of the study shows that before the tuning process, a system takes 156 milliseconds to execute the query and after implementing the proposed query tuning process, the execution time decreases to less than one millisecond. It means hundred times performance improvement on the query execution. The outcome of the study proved that the query execution time decreased after implementing the proposed query tuning process in the system.