Best Practice: Basic performance tuning of SQL statements in Oracle

Though this topic seem to be already known to many developers (mainly Oracle developers), I decided to blog on this so as to provide a quick start on basic tuning of SQL statements for Java developers. Reason being most Java developers always tend to concentrate on thier core areas and do not provide much focus on back-end related tasks. Either they write SQL statements sometimes at high performance cost without thier knowledge or highly depend upon Oracle experts to tune the SQL statements later.

Understanding of Oracle execution plan:

For every SQL statements executed to read or write data, Oracle does the following

• Parses the statement
• Builds a execution plan for that particular statement
• Processes the statement

Using Explain Plan for logging execution plan:

EXPLAIN PLAN command is used to determine the execution plan Oracle follows to execute a specified SQL statement without actually executing it. This means EXPLAIN PLAN runs very fast even if the statement being explained is a query that takes more time to run. So the actual statement is never executed by EXPLAIN PLAN.

This important diagnostic tool will help to identify in-efficient SQL statements by which appropriate tunning on them can be performed. Running this command inserts data describing each step of the execution plan into a work or specified plan table behaving as a sample output table containing all needed sequence of information when SQL statement gets executed.

To log the execution plan, execute the below SQL statement on any SQL select statement.

Syntax (default table): explain plan for [sql_statement]

Example:
SQL> explain plan for select * from emp where ename='smith';

Syntax (user-defined table): explain plan into [user_defined_table] for [sql_statement]

Example:
SQL> explain plan into user_execute_plan for select * from emp where ename='smith';

One other way to get these execution information is to set AUTOTRACE ON which will actually run the SQL statement unlike EXPLAIN PLAN and produces the execution plan of that SQL statement. This will take the same time as executing the SQL statement as it actually runs the statement to output the result along with the execution information and statistics.

Example:

SQL> set autotrace on;
SQL> select * from emp where ename='smith';

To view the execution plan, execute a simple SQL select statement on the plan table or use the script utlxpls.sql provided by Oracle.

Example:
SQL> select operation, option, object_name from plan_table;
OR
SQL> @$oracle_home/rdbms/admin/utlxpls.sql;

Output operations of execution plan from the work table:



In this case, TABLE ACCESS is made first with a full table scan and then results are fed to SELECT STATEMENT. Note that Full table scan means the entire table is accessed first before select is made.

Basic tunning of SQL statement using index:

Based on the output of the execution plan, SQL statements can be tunned for better performance. The execution plan generated helps in identifying whether the operation TABLE ACCESS uses full table scan which may not be as appropriate for good performance. To prevent this full table scan, create an index on the column. If we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index:

Other ways to use execution plan is to determine the cost (CPU/IO) of executing that statement.

Now create an index and log the execution plan:

SQL> create index emp_index on emp(ename);
SQL> delete plan_table;
SQL> explain plan for select * from emp where ename='smith';
SQL> select operation, option, object_name from plan_table;

Output operations of execution plan from the work table after creating index:



In this case, index (EMP_INDEX) is used first, then used for TABLE ACCESS with range scan. It is not done by a full table scan but rather by using data row id. Range scan means index was used and it can return more than one row.

Now use the indexed column and log the execution plan:

SQL> delete plan_table;
SQL> explain plan for select ename from emp where ename='smith';
SQL> select operation, option, object_name from plan_table;

Output operations of execution plan for select on indexed column from the work table after creating index:



If we only query fields of a table that are already in an index, Oracle doesn't have to read the data blocks because it can get the relevant data from the index without TABLE ACCESS thus improving performance of the SQL statement.

NOTE: If the table has a constraint set, then instead of range scan, it will use unique scan which means index was used and it returns exactly one row.

EXPLAIN PLAN statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. If you want to keep the rows generated by an EXPLAIN PLAN statement in the output table, then you must commit the transaction containing the statement to maintain those data for future analysis purposes as well. If you decide not to keep the rows generated by EXPLAIN PLAN statement in the output table, then you must rollback the transaction or delete those rows from the plan table.

There is no special system privileges required to use the EXPLAIN PLAN statement if you have just the insert privileges on the plan table. However you must have sufficient privileges to execute the statement which you are trying to explain.