SQL Performance Tuning

SQL performance tuning occurs once you have concluded, or at least suspect, that one or more particular SQL statements are causing the performance issues. These statements could be embedded in PL/SQL programs, views, server-side applications, or client-side applications such as Application Express or Oracle Forms.

Using Explain Plan

At this stage the goal of your SQL performance tuning is most likely be to reduce the response time of SQL queries since virtually all databases perform more queries than updates. The action to take is first to determine the current execution plans for the statements in question. This is done by prefixing the SQL statement with EXPLAIN PLAN FOR before running it to generate the execution plan and then using dbms_xplan.display to extract and display the plan in a readable format.

For example, the following statement

EXPLAIN PLAN FOR
SELECT first_name||' '||last_name, email
FROM employees e
, departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Sales'

generates the following plan (displayed by running the statement SELECT plan_table_output FROM TABLE(dbms_xplan.display) )

Plan hash value: 2912831499

-------------------------------------------------------------------------------------------------
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    10 |   430 |     4   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   270 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                   |    10 |   430 |     4   (0)| 00:00:01 |
|* 3 |    TABLE ACCESS FULL        | DEPARTMENTS       |     1 |    16 |     3   (0)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPARTMENT_NAME"='Sales')
   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

The plan is read from the inside out (i.e. most nested first) and the bottom up and you may also need to look at the parent operation (the first operation above with slightly less indentation).

In this case, therefore we can see that the first operations that Oracle will perform when running this statement are the index read of the index on the department_id column on the employee table (id=4) and a full table scan (i.e the whole table is read) of the departments table (id=3). These are compared in a nested loop operation (id=2) which just means that the two sets of rows are compared and those that match are kept and those that don't are discarded. This is typically used in OLTP systems to compare small amounts of data. In this case, only one row is expected from the department table and only 10 rows from the employee table/

What look for in the execution plans

Now that we have the execution plan, to make a difference with our Oracle SQL performance tuning we need to know what to look for and what we might want to change in the plan.

This is where knowledge of the application is important as well as a good understanding of SQL and how the optimizer works. In general terms we want to look for full table scans and for use of indexes.

Watch this video to learn how to interpret execution plans.

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.

Full Table Scans

Full table scans are much slower than index reads for large tables so you might think that any full table scans should always be replaced with an index read. This is not correct, however. The Oracle optimizer will choose a full table scan when there is no filter on the table or when the table is small enough such that an index read followed by a table read would actually take more i/o, or when a reasonable proportion of the table is required (somewhere between 1 and 10%).

Index reads

As stated above an index read isn't always better than a full table scan but if we think the use of an index would improve the performance of our SQL statement then there a few things of which we need to be aware. Index reads are most likely to be chosen by the Oracle optimizer when there is at least one index available on the column(s) being read (makes sense - the optimizer can't use an index if it doesn't exist!), when the cost of using the index is less than the cost of a full table scan and when the use of the index hasn't been prevented by the way the SQL statement has been coded.

In fact making sure that we don't write our SQL statements in such a way as to prevent use of the index is probably one of the most important aspects of SQL performance tuning.

A normal index won't be used under the following conditions:

when the clause containing the indexed column(s) uses not equals (<> or !=) eg. dept_id <> 10
when the clause containing the indexed column(s) uses a function eg. UPPER (emp_name) = 'SMITH'
when the clause containing the indexed column(s) uses an expression eg. dept_id+1 = 10

Function-based indexes can be used to overcome issues like this.

It is also important, if your SQL performance tuning is to be successful, to make sure the indexes are selective. Normal (b-tree) indexes should not be placed on columns which will have the same value for a large percentage of the rows in the table. Bitmap indexes can be used for this but only when the table is updated very infrequently. Inserts, updates and deletes are very expensive in terms of i/o for bitmap indexes so using them for columns being updated frequently would severely diminish performance.

Normal (B-tree) indexes also need to be modified when updates are made to the corresponding table so it is better to not have too many indexes as this will obviously reduce the performance of insert, update and delete statements.

More information on interpreting execution plans specifically and SQL performance tuning in general can be found in the Oracle Database Performance Tuning Guide.

Looking for high quality Oracle training? Our partners offer Oracle training in the UK for both developers and dbas. Click here for details of Oracle training in the UK. Advance your skills and sky rocket your career with expert instructor-led Oracle training.

Need help with sql performance tuning? With skills honed to a fine edge over many years with myriad clients in a variety of industries, Smartsoft's consultants have the knowledge and expertise to help you. Click here for a consultation application form.