Selecting and tuning the optimal query form of different SQL commands

Abstract

This paper presents the proposed approach of SQL tuning plan and tool in database systems based on experiments using testing data workload in Oracle 11g XE; to get an optimal execution plan for a given SQL, statements based on process time consumed and statistics about the object related to tables are referenced in the statement. These statistics include the SQL command, running time consumption, tables themselves, their indexes, access methods, and other derived objects. The quality of final execution plan depends primarily on the quality of the information found about the SQL command to improve a poorly-performed query. This work contributes to research knowledge by proposing necessary knowledge and information about database tuning using a useful tool to evaluate SQL commands. The proposed approach helps researchers, database administrators and developers to assess the impact of their decisions in the selection of the optimal SQL statement form. Oracle 11g XE is taken for the experimentation purpose.