Research Article Open Access

Query Tuning in Oracle Database

Nofri Alhadi1 and Kamsuriah Ahmad1
  • 1 Universiti Kebangsaan Malaysia, Malaysia
Journal of Computer Science
Volume 8 No. 11, 2012, 1889-1896

DOI: https://doi.org/10.3844/jcssp.2012.1889.1896

Submitted On: 3 August 2012 Published On: 5 November 2012

How to Cite: Alhadi, N. & Ahmad, K. (2012). Query Tuning in Oracle Database. Journal of Computer Science, 8(11), 1889-1896. https://doi.org/10.3844/jcssp.2012.1889.1896

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.

  • 1,233 Views
  • 1,851 Downloads
  • 3 Citations

Download

Keywords

  • Query Tuning Process
  • Large Scale Database
  • Oracle