SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS

DBMS is a complex system that is composed of a collection of subsystems, each with a specific task and will interact with the user application programs and the database. Maintaining DBMS performance is a challenging task. Many events can cause the database poor performance and once it occurs, it is...

Full description

Saved in:
Bibliographic Details
Main Author: Nor'azah, Md. Khushairi
Format: Thesis
Language:eng
eng
Published: 2005
Subjects:
Online Access:https://etd.uum.edu.my/1302/1/NORAZAH_BT._MD._KHUSHAIRI.pdf
https://etd.uum.edu.my/1302/2/1.NORAZAH_BT._MD._KHUSHAIRI.pdf
Tags: Add Tag
No Tags, Be the first to tag this record!
id my-uum-etd.1302
record_format uketd_dc
spelling my-uum-etd.13022013-07-24T12:11:21Z SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS 2005-04-12 Nor'azah, Md. Khushairi Sekolah Siswazah Graduate School QA71-90 Instruments and machines DBMS is a complex system that is composed of a collection of subsystems, each with a specific task and will interact with the user application programs and the database. Maintaining DBMS performance is a challenging task. Many events can cause the database poor performance and once it occurs, it is important to diagnose and resolve the problem quickly. The research will focus on poor performance caused by SQL Query. Most of application developer and DBA have problem to understand the SQL Query tuning step. This guideline will determine the cause of database performance problem, identify the bad SQL and analyze it. Based on the literature review, methodologies have been introduced which cover all the steps needed to come out with a guideline. The methodology stages are gathering information, set up environment, design test cases, identify problematic SQL, use tuning tool to analyze the SQL, coding process and analysis. The results from the analysis and experiment have been compiled and arranged to get the SQL Query Tuning Guideline. This guideline has been separated into 3 sections. Firstly, explains on the scripts to identify the problematic SQL. Secondly, on the SQL Counter, the program that has been developed in order to spot poor performance SQL in the nested procedure. Finally, using tuning tool, Explain Plan and TKPROF to analyze the SQL statement. Experiments have been conducted in order to output this guideline and to prove that this guideline is able to help the user to tune the SQL. Furthermore, this guideline also introduces a new technique, SQL Counter, in identifying the problematic SQL in the nested program or sub query. This guideline can be a good practice to the developers or the DBAs themselves and can be regard as one useful recommendation. 2005-04 Thesis https://etd.uum.edu.my/1302/ https://etd.uum.edu.my/1302/1/NORAZAH_BT._MD._KHUSHAIRI.pdf application/pdf eng validuser https://etd.uum.edu.my/1302/2/1.NORAZAH_BT._MD._KHUSHAIRI.pdf application/pdf eng public masters masters Universiti Utara Malaysia
institution Universiti Utara Malaysia
collection UUM ETD
language eng
eng
topic QA71-90 Instruments and machines
spellingShingle QA71-90 Instruments and machines
Nor'azah, Md. Khushairi
SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS
description DBMS is a complex system that is composed of a collection of subsystems, each with a specific task and will interact with the user application programs and the database. Maintaining DBMS performance is a challenging task. Many events can cause the database poor performance and once it occurs, it is important to diagnose and resolve the problem quickly. The research will focus on poor performance caused by SQL Query. Most of application developer and DBA have problem to understand the SQL Query tuning step. This guideline will determine the cause of database performance problem, identify the bad SQL and analyze it. Based on the literature review, methodologies have been introduced which cover all the steps needed to come out with a guideline. The methodology stages are gathering information, set up environment, design test cases, identify problematic SQL, use tuning tool to analyze the SQL, coding process and analysis. The results from the analysis and experiment have been compiled and arranged to get the SQL Query Tuning Guideline. This guideline has been separated into 3 sections. Firstly, explains on the scripts to identify the problematic SQL. Secondly, on the SQL Counter, the program that has been developed in order to spot poor performance SQL in the nested procedure. Finally, using tuning tool, Explain Plan and TKPROF to analyze the SQL statement. Experiments have been conducted in order to output this guideline and to prove that this guideline is able to help the user to tune the SQL. Furthermore, this guideline also introduces a new technique, SQL Counter, in identifying the problematic SQL in the nested program or sub query. This guideline can be a good practice to the developers or the DBAs themselves and can be regard as one useful recommendation.
format Thesis
qualification_name masters
qualification_level Master's degree
author Nor'azah, Md. Khushairi
author_facet Nor'azah, Md. Khushairi
author_sort Nor'azah, Md. Khushairi
title SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS
title_short SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS
title_full SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS
title_fullStr SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS
title_full_unstemmed SQL Performance : Guideline for Tuning the SQL Queries in Oracle RDBMS
title_sort sql performance : guideline for tuning the sql queries in oracle rdbms
granting_institution Universiti Utara Malaysia
granting_department Sekolah Siswazah
publishDate 2005
url https://etd.uum.edu.my/1302/1/NORAZAH_BT._MD._KHUSHAIRI.pdf
https://etd.uum.edu.my/1302/2/1.NORAZAH_BT._MD._KHUSHAIRI.pdf
_version_ 1747827116743852032