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