Query tuning in oracle database

Nofri Alhadi, Kamsuriah Ahmad

Research output: Contribution to journalArticle

2 Citations (Scopus)

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.

Original languageEnglish
Pages (from-to)1889-1896
Number of pages8
JournalJournal of Computer Science
Volume8
Issue number11
DOIs
Publication statusPublished - 2012

Fingerprint

Tuning
Industry

Keywords

  • Large scale database
  • Oracle
  • Query tuning process

ASJC Scopus subject areas

  • Software
  • Computer Networks and Communications
  • Artificial Intelligence

Cite this

Query tuning in oracle database. / Alhadi, Nofri; Ahmad, Kamsuriah.

In: Journal of Computer Science, Vol. 8, No. 11, 2012, p. 1889-1896.

Research output: Contribution to journalArticle

Alhadi, Nofri ; Ahmad, Kamsuriah. / Query tuning in oracle database. In: Journal of Computer Science. 2012 ; Vol. 8, No. 11. pp. 1889-1896.
@article{2c45c7b744404279b4d96b602b3a8f91,
title = "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.",
keywords = "Large scale database, Oracle, Query tuning process",
author = "Nofri Alhadi and Kamsuriah Ahmad",
year = "2012",
doi = "10.3844/jcssp.2012.1889.1896",
language = "English",
volume = "8",
pages = "1889--1896",
journal = "Journal of Computer Science",
issn = "1549-3636",
publisher = "Science Publications",
number = "11",

}

TY - JOUR

T1 - Query tuning in oracle database

AU - Alhadi, Nofri

AU - Ahmad, Kamsuriah

PY - 2012

Y1 - 2012

N2 - 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.

AB - 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.

KW - Large scale database

KW - Oracle

KW - Query tuning process

UR - http://www.scopus.com/inward/record.url?scp=84880111810&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=84880111810&partnerID=8YFLogxK

U2 - 10.3844/jcssp.2012.1889.1896

DO - 10.3844/jcssp.2012.1889.1896

M3 - Article

VL - 8

SP - 1889

EP - 1896

JO - Journal of Computer Science

JF - Journal of Computer Science

SN - 1549-3636

IS - 11

ER -