Skip Headers
Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)

Part Number E16638-05
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

16 SQL Tuning Overview

This chapter discusses goals for tuning, how to identify high-resource SQL statements, explains what should be collected, provides tuning suggestions, and discusses how to create SQL test cases to troubleshoot problems in SQL.

This chapter contains the following sections:

See Also:

16.1 Introduction to SQL Tuning

SQL tuning involves the following basic steps:

The previous steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned.

16.2 Goals for Tuning


16.2.1 Reduce the Workload


Two examples of how you can resource usage are as follows:

  1. If a commonly executed query must access a small percentage of data in the table, then the database can execute it more efficiently by using an index. By creating such an index, you reduce the amount of resources used.


16.2.2 Balance the Workload

Systems often tend to have peak usage in the daytime when real users are connected to the system, and low usage in the nighttime. If you can schedule noncritical reports and batch jobs to run in the nighttime and reduce their concurrency during day time, then the database frees up resources for the more critical programs in the day.

16.2.3 Parallelize the Workload

Queries that access large amounts of data (typical data warehouse queries) can often run in parallel. Parallelism is extremely useful for reducing response time in a low concurrency data warehouse. However, for OLTP environments, which tend to be high concurrency, parallelism can adversely impact other users by increasing the overall resource usage of the program.

16.3 Identifying High-Load SQL

This section describes the steps involved in identifying and gathering data on high-load SQL statements. High-load SQL are poorly-performing, resource-intensive SQL statements that impact the performance of an Oracle database. The following tools can identify high-load SQL statements:

16.3.1 Identifying Resource-Intensive SQL


  • Is the problem specific to a single program (or small number of programs)?

  • Is the problem generic over the application? Tuning a Specific Program

If you are tuning a specific program (GUI or 3GL), then identifying the SQL to examine is a simple matter of looking at the SQL executed within the program. Oracle Enterprise Manager provides tools for identifying resource intensive SQL statements, generating explain plans, and evaluating SQL performance.



If the whole application is performing poorly, or if you are attempting to reduce the overall CPU or I/O load on the database server, then identifying resource-intensive SQL involves the following steps:


  2. Gather operating system and Oracle Database statistics at the beginning and end of that period. The minimum of Oracle Database statistics gathered should be file I/O (V$FILESTAT), system statistics (V$SYSSTAT), and SQL statistics (V$SQLAREA, V$SQL, or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS).

    See Also:

  3. Using the data collected in step two, identify the SQL statements using the most resources. A good way to identify candidate SQL statements is to query V$SQLSTATS. V$SQLSTATS contains resource usage information for all SQL statements in the shared pool. The data in V$SQLSTATS should be ordered by resource usage. The most common resources are:

    • Buffer gets (V$SQLSTATS.BUFFER_GETS, for high CPU using statements)

    • Disk reads (V$SQLSTATS.DISK_READS, for high I/O statements)

    • Sorts (V$SQLSTATS.SORTS, for many sorts)


Similarly, it is possible to apportion the percentage of disk reads a statement performs out of the total disk reads performed by the system by dividing V$SQL_STATS.DISK_READS by the value for the V$SYSSTAT statistic physical reads. The SQL sections of the Automatic Workload Repository report include this data, so you do not need to perform the percentage calculations manually.

See Also:

Oracle Database Reference for information about dynamic performance views

After you have identified the candidate SQL statements, the next stage is to gather information that is necessary to examine the statements and tune them.

16.3.2 Gathering Data on the SQL Identified


The tuning process begins by determining the structure of the underlying tables and indexes. The information gathered includes the following:

  1. Complete SQL text from V$SQLTEXT

  2. Structure of the tables referenced in the SQL statement, usually by describing the table in SQL*Plus

  3. Definitions of any indexes (columns, column orders), and whether the indexes are unique or non-unique


  5. Definitions of any views referred to in the SQL statement

  6. Repeat steps two, three, and four for any tables referenced in the view definitions found in step five


  8. Any previous optimizer plans for that SQL statement



16.4 Automatic SQL Tuning Features

Because the manual SQL tuning process poses many challenges to the application developer, the SQL tuning process has been automated by the automatic SQL tuning features of Oracle Database. These features are designed to work equally well for OLTP and Data Warehouse type applications:

16.4.1 ADDM

The Automatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with Oracle Database, including high-load SQL statements. See "Overview of the Automatic Database Diagnostic Monitor".

16.4.2 SQL Tuning Advisor

SQL Tuning Advisor optimizes SQL statements that have been identified as high-load SQL statements. By default, Oracle Database automatically identifies problematic SQL statements and implements tuning recommendations using SQL Tuning Advisor during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of the high-load SQL statements. You can also choose to run SQL Tuning Advisor at any time on any given SQL workload to improve performance. See "Tuning Reactively with SQL Tuning Advisor".

16.4.3 SQL Tuning Sets

When multiple SQL statements serve as input to ADDM, SQL Tuning Advisor, or SQL Access Advisor, the database constructs and stores a SQL tuning set (STS). The STS includes the set of SQL statements along with their associated execution context and basic execution statistics. See "Managing SQL Tuning Sets".

16.4.4 SQL Access Advisor

In addition to SQL Tuning Advisor, SQL Access Advisor provides advice on materialized views, indexes, and materialized view logs. SQL Access Advisor helps you achieve performance goals by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload. In general, as the number of materialized views and indexes and the space allocated to them is increased, query performance improves. SQL Access Advisor considers the trade-offs between space usage and query performance, and recommends the most cost-effective configuration of new and existing materialized views and indexes. See "Using SQL Access Advisor".

16.5 Developing Efficient SQL Statements

This section describes ways you can improve SQL statement efficiency:

16.5.1 Verifying Optimizer Statistics


Things to check:

  • If you gather statistics for some tables in your database, then it is probably best to gather statistics for all tables. This is especially true if your application includes SQL statements that perform joins.

  • If the optimizer statistics in the data dictionary are no longer representative of the data in the tables and indexes, then gather new statistics. One way to check whether the dictionary statistics are stale is to compare the real cardinality (row count) of a table to the value of DBA_TABLES.NUM_ROWS. Additionally, if there is significant data skew on predicate columns, then consider using histograms.

16.5.2 Reviewing the Execution Plan


When examining the optimizer execution plan, look for the following:

  • The driving table has the best filter.

  • The join order in each step returns the fewest number of rows to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters).

  • The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when the statement returns many rows.

  • The database uses views efficiently. Look at the SELECT list to see whether access to the view is necessary.

  • There are any unintentional Cartesian products (even with small tables).

  • Each table is being accessed efficiently:

    Consider the predicates in the SQL statement and the number of rows in the table. Look for suspicious activity, such as a full table scans on tables with large number of rows, which have predicates in the where clause. Determine why an index is not used for such a selective predicate.

    A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table, or to perform a full table scan to leverage a better join method (for example, hash_join) for the number of rows returned.


16.5.3 Restructuring the SQL Statements

Often, rewriting an inefficient SQL statement is easier than modifying it. If you understand the purpose of a given statement, then you might be able to quickly and easily write a new statement that meets the requirement. Compose Predicates Using AND and =

To improve SQL efficiency, use equijoins whenever possible. Statements that perform equijoins on untransformed column values are the easiest to tune. Avoid Transformed Columns in the WHERE Clause

Use untransformed column values. For example, use:

WHERE a.order_no = b.order_no

rather than:




AND charcol = numexpr

where numexpr is an expression of number type (for example, 1, USERENV('SESSIONID'), numcol, numcol+0,...), Oracle Database translates that expression into:

AND TO_NUMBER(charcol) = numexpr

Avoid the following kinds of complex expressions:

  • col1 = NVL (:b1,col1)

  • NVL (col1,-999) = ….

  • TO_DATE(), TO_NUMBER(), and so on


Add the predicate versus using NVL() technique.

For example:

SELECT employee_num, full_name Name, employee_id 
  FROM mtl_employees_current_view 
  ORDER BY employee_num;


SELECT employee_num, full_name Name, employee_id 
  FROM mtl_employees_current_view 
  ORDER BY employee_num;

When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index, as in the following statement:

varcol = TO_CHAR(numcol)


TO_CHAR(numcol) = varcol

See Also:

Chapter 14, "Using Indexes and Clusters" for more information on function-based indexes Write Separate SQL Statements for Specific Tasks





Optimization (determining the execution plan) takes place before the database knows the values substituted in the query. An execution plan cannot, therefore, depend on what those values are. For example:

SELECT info 
FROM tables
WHERE ... 



To use the index whenever real values are given for :loval and :hival (if you expect narrow ranges, even ranges where :loval often equals :hival), you can rewrite the example in the following logically equivalent form:

FROM   tables 
WHERE  ... 
AND    somecolumn BETWEEN :loval AND :hival
FROM   tables
WHERE  ... 



16.5.4 Controlling the Access Path and Join Order with Hints

You can influence the optimizer's choices by setting the optimizer approach and goal, and by gathering representative statistics for the query optimizer. Sometimes, the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to execute a SQL statement. You can use hints in SQL statements to instruct the optimizer about how the statement should be executed.


SELECT /*+ FULL(e) */ e.last_name
  FROM employees e
 WHERE e.job_id = 'CLERK';


  • Avoid a full-table scan if it is more efficient to get the required rows through an index.

  • Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.

  • Choose the join order so as to join fewer rows to tables later in the join order.

The following example shows how to tune join order effectively:

FROM taba a, tabb b, tabc c
WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.key1 = b.key1
AND a.key2 = c.key2;




    With nested loop joins, the joins all happen through the join indexes, the indexes on the primary or foreign keys used to connect that table to an earlier table in the join tree. Rarely do you use the indexes on the non-join conditions, except for the driving table. Thus, after taba is chosen as the driving table, use the indexes on b.key1 and c.key2 to drive into tabb and tabc, respectively.


    You can reduce the work of the following join by first joining to the table with the best still-unused filter. Thus, if "bcol BETWEEN ..." is more restrictive (rejects a higher percentage of the rows seen) than "ccol BETWEEN ...", then the last join becomes easier (with fewer rows) if tabb is joined before tabc.

  3. You can use the ORDERED or STAR hint to force the join order. Use Caution When Managing Views

Be careful when joining views, when performing outer joins to views, and when reusing an existing view for a new purpose. Use Caution When Joining Complex Views



SELECT d.department_id, d.department_name, d.location_id,
     e.employee_id, e.last_name, e.first_name, e.salary, e.job_id
FROM  departments d
     ,employees e
WHERE e.department_id (+) = d.department_id;

The following query finds employees in a specified state:

SELECT v.last_name, v.first_name, l.state_province
  FROM locations l, emp_dept v
 WHERE l.state_province = 'California'
  AND   v.location_id = l.location_id (+);

In the following plan table output, note that the emp_dept view is instantiated:

| SELECT STATEMENT          |          |       |      |        |       |       |
|  FILTER                   |          |       |      |        |       |       |
|   NESTED LOOPS OUTER      |          |       |      |        |       |       |
|    VIEW                   |EMP_DEPT  |       |      |        |       |       |
|     NESTED LOOPS OUTER    |          |       |      |        |       |       |
|      TABLE ACCESS FULL    |DEPARTMEN |       |      |        |       |       |
|       INDEX RANGE SCAN    |EMP_DEPAR |       |      |        |       |       |
|     INDEX UNIQUE SCAN     |LOC_ID_PK |       |      |        |       |       |


Consider the following example:

SELECT department_name 
FROM emp_dept
WHERE department_id = 10;

The entire view is first instantiated by performing a join of the employees and departments tables and then aggregating the data. However, you can obtain department_name and department_id directly from the departments table. It is inefficient to obtain this information by querying the emp_dept view. Use Caution When Unnesting Subqueries


See Also:

Oracle Database Data Warehousing Guide for an explanation of the dangers with subquery unnesting Use Caution When Performing Outer Joins to Views

In the case of an outer join to a multi-table view, the query optimizer (in Release 8.1.6 and later) can drive from an outer join column, if an equality predicate is defined on it.

An outer join within a view is problematic because the performance implications of the outer join are not visible. Store Intermediate Results

Intermediate, or staging, tables are quite common in relational database systems, because they temporarily store some intermediate results. In many applications they are useful, but Oracle Database requires additional resources to create them. Always consider whether the benefit they could bring is more than the cost to create them. Avoid staging tables when the information is not reused multiple times.

Some additional considerations:

  • Storing intermediate results in staging tables could improve application performance. In general, whenever an intermediate result is usable by multiple following queries, it is worthwhile to store it in a staging table. The benefit of not retrieving data multiple times with a complex statement at the second usage of the intermediate result is better than the cost to materialize it.

  • Long and complex queries are hard to understand and optimize. Staging tables can break a complicated SQL statement into several smaller statements, and then store the result of each step.

  • Consider using materialized views. These are precomputed tables comprising aggregated or joined data from fact and possibly dimension tables.

    See Also:

    Oracle Database Data Warehousing Guide for detailed information on using materialized views

16.5.5 Restructuring the Indexes


  • Remove nonselective indexes to speed the DML.

  • Index performance-critical access paths.

  • Consider reordering columns in existing concatenated indexes.

  • Add columns to the index to improve selectivity.

Do not use indexes as a panacea. Application developers sometimes think that performance improves when they create more indexes. If a single programmer creates an appropriate index, then this index may improve the application's performance. However, if 50 developers each create an index, then application performance will probably be hampered.

16.5.6 Modifying or Disabling Triggers and Constraints

Using triggers consumes system resources. If you use too many triggers, then performance may be adversely affected. In this case, you might need to modify or disable the triggers.

16.5.7 Restructuring the Data

After restructuring the indexes and the statement, consider restructuring the data:

  • Introduce derived values. Avoid GROUP BY in response-critical code.

  • Review your data design. Change the design of your system if it can improve performance.

  • Consider partitioning, if appropriate.

16.5.8 Maintaining Execution Plans Over Time

You can maintain the existing execution plan of SQL statements over time either using stored statistics or SQL plan baselines. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan as a SQL plan baseline maintains the plan for set of SQL statements. If both statistics and a SQL plan baseline are available for a SQL statement, then the optimizer first uses a cost-based search method to build a best-cost plan, and then tries to find a matching plan in the SQL plan baseline. If a match is found, then the optimizer proceeds using this plan. Otherwise, it evaluates the cost of each of the accepted plans in the SQL plan baseline and selects the plan with the lowest cost.

16.5.9 Visiting Data as Few Times as Possible

Applications should try to access each row only once. This reduces network traffic and reduces database load. Consider doing the following: Combine Multiples Scans Using CASE Expressions

Often, it is necessary to calculate different aggregates on various sets of tables. Usually, you achieve this goal with multiple scans on the table, but it is easy to calculate all the aggregates with a single scan. Eliminating n-1 scans can greatly improve performance.

You can combine multiple scans into one scan by moving the WHERE condition of each scan into a CASE expression, which filters the data for the aggregation. For each aggregation, there could be another column that retrieves the data.

The following example asks for the count of all employees who earn less then 2000, between 2000 and 4000, and more than 4000 each month. You can obtain this result by executing three separate queries:

  FROM employees
  WHERE salary < 2000;

  FROM employees
  WHERE salary BETWEEN 2000 AND 4000;

  FROM employees
  WHERE salary>4000;

However, it is more efficient to run the entire query in a single statement. Each number is calculated as one column. The count uses a filter with the CASE expression to count only the rows where the condition is valid. For example:

SELECT COUNT (CASE WHEN salary < 2000 
                   THEN 1 ELSE null END) count1, 
       COUNT (CASE WHEN salary BETWEEN 2001 AND 4000 
                   THEN 1 ELSE null END) count2, 
       COUNT (CASE WHEN salary > 4000 
                   THEN 1 ELSE null END) count3 
  FROM employees; 

This is a very simple example. The ranges could be overlapping, the functions for the aggregates could be different, and so on. Use DML with RETURNING Clause


See Also:

Oracle Database SQL Language Reference for syntax on the INSERT, UPDATE, and DELETE statements Modify All the Data Needed in One Statement

When possible, use array processing. This means that an array of bind variable values is passed to Oracle Database for repeated execution. This is appropriate for iterative processes in which multiple rows of a set are subject to the same operation.

For example:

 FOR pos_rec IN (SELECT * 
   FROM order_positions 
   WHERE order_id = :id) LOOP
      DELETE FROM order_positions
      WHERE order_id = pos_rec.order_id AND
        order_position = pos_rec.order_position;
 DELETE FROM orders 
 WHERE order_id = :id;


16.6 Building SQL Test Cases

For many SQL-related problems, obtaining a reproducible test case makes it easier to resolve the problem. Starting with the 11g Release 2 (11.2), Oracle Database contains the SQL Test Case Builder, which automates the somewhat difficult and time-consuming process of gathering and reproducing as much information as possible about a problem and the environment in which it occurred.

The objective of a SQL Test Case Builder is to capture the information pertaining to a SQL-related problem, along with the exact environment under which the problem occurred, so that you can reproduce and test the problem on a separate Oracle database. After the test case is ready, you can upload the problem to Oracle Support to enable support personnel to reproduce and troubleshoot the problem.

The information gathered by SQL Test Case Builder includes the query being executed, table and index definitions (but not the actual data), PL/SQL functions, procedures, and packages, optimizer statistics, and initialization parameter settings.

16.6.1 Creating a Test Case

You can access the SQL Test Case Builder from Enterprise Manager and manually using the DBMS_SQLDIAG package. Accessing SQL Test Case Builder from the Enterprise Manager

From Enterprise Manager, the SQL Test Case Builder is accessible only when a SQL incident occurs. A SQL-related problem is referred to as a SQL incident, and each SQL incident is identified by an incident number. You can access the SQL Test Case Builder from the Support Workbench page in Enterprise Manager.

You can access the Support Workbench page in either of the following ways:

  • In the Database Home page of Enterprise Manager, under Diagnostic Summary, click the link to Active Incidents (indicating the number of active incidents). This opens the Support Workbench page, with the incidents listed in a table.

  • Click Advisor Central under Related Links to open the Advisor Central page. Next, click SQL Advisors and then Click here to go to Support Workbench to open the Support Workbench page.

From the Support Workbench page, to access the SQL Test Case Builder:

  1. Click an incident ID to open the problem details for the particular incident.

  2. Next, click Oracle Support in the Investigate and Resolve section.

  3. Click Generate Additional Dumps and Test Cases.

  4. For a particular incident, click the icon in the Go To Task column to run the SQL Test Case Builder.

    Provide a location to store the test case, and also provide a name for the output.

The output of the SQL Test Case Builder is a SQL script that contains the commands required to re-create all the necessary objects and the environment. Accessing SQL Test Case Builder Using DBMS_SQLDIAG

You can also invoke the SQL Test Case Builder manually, using the DBMS_SQLDIAG package. This package consists of various subprograms for the SQL Test Case Builder, some of which are listed in Table 16-1.

Table 16-1 SQL Test Case Builder Procedures in DBMS_SQLDIAG

Procedure Name Function


Generate a SQL test case.


Generates a SQL test case corresponding to the incident ID passed as an argument.


Generates a SQL test case corresponding to the SQL text passed as an argument.

For more information on this package and all of its procedures and parameters, see Oracle Database PL/SQL Packages and Types Reference.