Title: Selecting and tuning the optimal query form of different SQL commands

Authors: Hasan Ali Idhaim

Addresses: Department of Computer information system (CIS), Faculty of Prince Al-Hussein Bin Abdullah II for Information Technology, Hashemite University, P.O. Box 150459, Zarqa 13115, Jordan

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.

Keywords: Oracle 11g; SQL commands; SQL code tuning; SCT; query; tuning; throughput; evaluation; optimisation; DBA.

DOI: 10.1504/IJBIS.2019.097041

International Journal of Business Information Systems, 2019 Vol.30 No.1, pp.1 - 12

Received: 24 Aug 2016
Accepted: 07 Apr 2017
Published online: 17 Dec 2018 *

Full-text access for editors Full-text access for subscribers Purchase this article Comment on this article