Oracle SQL tuning - Tune individual SQL statements

Oracle SQL optimizers

One of the first things the Oracle DBA looks at is the default optimizer mode for the database. The Oracle initialization parameters offer many cost-based optimizer modes as well as the deprecated yet useful rule-based hint:

The cost-based optimizer uses 'statistics' that are collected from the table using the 'analyze table' command. Oracle uses these metrics about the tables in order to intelligently determine the most efficient way of servicing the SQL query. It is important to recognize that in many cases, the cost-based optimizer may not make the proper decision in terms of the speed of the query. The cost-based optimizer is constantly being improved, but there are still many cases in which the rule-based optimizer will result in faster Oracle queries.

Prior to Oracle 10g, Oracle's default optimizer mode was called 'choose.' In the choose optimizer mode, Oracle will execute the rule-based optimizer if there are no statistics present for the table; it will execute the cost-based optimizer if statistics are present. The danger with using the choose optimizer mode is that problems can occur in cases where one Oracle table in a complex query has statistics and the other tables do not. 

Starting in Oracle 10g, the default optimizer mode is all_rows, favoring full-table scans over index access.  The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows_n) adds additional I/O overhead, but they return rows faster, back to the originating query:

Full-table scans touch all data blocks

Hence, many OLTP shops will choose first_rows, first_rows_100 or first_rows_10, asking Oracle to use indexes to reduce block touches:

Index scans return rows fast by doing additional I/O

When only some tables contain CBO statistics, Oracle will use the cost-based optimization and estimate statistics for the other tables in the query at runtime. This can cause significant slowdown in the performance of the individual query.

In sum, the Oracle database administrator will always try changing the optimizer mode for queries as the very first step in Oracle tuning. The foremost tenet of Oracle SQL tuning is avoiding the dreaded full-table scan. One of the hallmarks of an inefficient SQL statement is the failure of the SQL statement to use all of the indexes that are present within the Oracle database in order to speed up the query.

Of course, there are times when a full-table scan is appropriate for a query, such as when you are doing aggregate operations such as a sum or an average, and the majority of the rows within the Oracle table must be read to get the query results. The task of the SQL tuning expert is to evaluate each full-table scan and see if the performance can be improved by adding an index.

In most Oracle systems, a SQL statement will be retrieving only a small subset of the rows within the table. The Oracle optimizers are programmed to check for indexes and to use them whenever possible to avoid excessive I/O. However, if the formulation of a query is inefficient, the cost-based optimizer becomes confused about the best access path to the data, and the cost-based optimizer will sometimes choose to do a full-table scan against the table. Again, the general rule is for the Oracle database administrator to interrogate the SQL and always look for full-table scans. 

For the full story, see my book "Oracle Tuning: The Definitive Reference" for details on choosing the right optimizer mode.

• Rows processed:  Queries that process a large number of rows will have high I/O and may also have impact on the TEMP tablespace.
   
• Buffer gets:  High buffer gets may indicate a resource-intensive query.
   
• Disk reads:  High disk reads indicate a query that is causing excessive I/O.
   
• Memory KB:  The memory allocation of a SQL statement is useful for identifying statements that are doing in-memory table joins.
   
• CPU secs:  This identifies the SQL statements that use the most processor resources.
   
• Sorts:  Sorts can be a huge slowdown, especially if they're being done on a disk in the TEMP tablespace.
   
• Executions:  The more frequently executed SQL statements should be tuned first, since they will have the greatest impact on overall performance.

Step 2:  Determine the execution plan for SQL

As each SQL statement is identified, it will be 'explained' to determine its existing execution plan. There are a host of third-party tools on the market that show the execution plan for SQL statements. The most common way of determining the execution plan for a SQL statement is to use Oracle's explain plan utility. By using explain plan, the Oracle DBA can ask Oracle to parse the statement and display the execution class path without actually executing the SQL statement.

To see the output of an explain plan, you must first create a 'plan table.' Oracle provides a script in $ORACLE_HOME/rdbms/admin called utlxplan.sql. Execute utlxplan.sql and create a public synonym for the plan_table:
 
sqlplus > @utlxplan
Table created.
 
sqlplus > create public synonym plan_table for sys.plan_table;
Synonym created.

Most relational databases use an explain utility that takes the SQL statement as input, runs the SQL optimizer, and outputs the access path information into a plan_table, which can then be interrogated to see the access methods. Listing 1 runs a complex query against a database.

This syntax is piped into the SQL optimizer, which will analyze the query and store the plan information in a row in the plan table identified by RUN1. Please note that the query will not execute; it will only create the internal access information in the plan table. The plan tables contains the following fields:

• operation:  The type of access being performed. Usually table access, table merge, sort, or index operation
   
• options:  Modifiers to the operation, specifying a full table, a range table, or a join
   
• object_name:  The name of the table being used by the query component
   
• Process ID:  The identifier for the query component
   
• Parent_ID:  The parent of the query component. Note that several query components may have the same parent.

Now that the plan_table has been created and populated, you may interrogate it to see your output by running the following query in Listing 2.

plan.sql - displays contents of the explain plan table

SET PAGES 9999;

SELECT  lpad(' ',2*(level-1))||operation operation,

        options,

        object_name,

        position

FROM plan_table

START WITH id=0

AND

statement_id = 'RUN1'

CONNECT BY prior id = parent_id

AND

statement_id = 'RUN1';

Listing 3 shows the output from the plan table shown in Listing 1. This is the execution plan for the statement and shows the steps and the order in which they will be executed.

From this output, we can see the dreaded TABLE ACCESS FULL on the PLANSNET table. To diagnose the reason for this full-table scan, we return to the SQL and look for any plansnet columns in the WHERE clause. There, we see that the plansnet column called 'mgc' is being used as a join column in the query, indicating that an index is necessary on plansnet.mgc to alleviate the full-table scan.

While the plan table is useful for determining the access path to the data, it does not tell the entire story. The configuration of the data is also a consideration. The SQL optimizer is aware of the number of rows in each table (the cardinality) and the presence of indexes on fields, but it is not aware of data distribution factors such as the number of expected rows returned from each query component.
 

Step 3:  Tune the SQL statement

For those SQL statements that possess a sub-optimal execution plan, the SQL will be tuned by one of the following methods:
 

• Adding SQL 'hints' to modify the execution plan
   

• Re-write SQL with Global Temporary Tables

• Rewriting the SQL in PL/SQL. For certain queries this can result in more than a 20x performance improvement. The SQL would be replaced with a call to a PL/SQL package that contained a stored procedure to perform the query.

Troubleshooting tip!  For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an 'alter session' command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Oracle publishes many dozens of SQL hints, and hints become increasingly more complicated through the various releases of Oracle and on into Oracle.

Note:  Hints are only used for de-bugging and you should adjust your optimizer statistics to make the CBO replicate the hinted SQL.  Let's look at the most common hints to improve tuning:

• Mode hints:  first_rows_10, first_rows_100
• Oracle leading and ordered hints  Also see how to tune table join order with histograms
   
• Dynamic sampling: dynamic_sampling
   

• Oracle SQL undocumented tuning hints - Guru's only

• The cardinality hint
   

Let's wind up with a review of the basic components of a SQL query and see how to optimize a query for remote execution.

Tips for writing more efficient SQL

Space doesn't permit me to discuss every detail of Oracle tuning, but I can share some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:

• Rewrite complex subqueries with temporary tables - Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views).  Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.
   
• Use minus instead of EXISTS subqueries - Some say that using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.
   
• Use SQL analytic functions - The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
   
• Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins - In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests.  Note that this is a non-correlated sub-query, but it could be re-written as an outer join.

select book_key from book
where
book_key NOT IN (select book_key from sales);

Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.

select b.book_key from book b, sales s
where
   b.book_key = s.book_key(+)
and
   s.book_key IS NULL;

• Index your NULL values - If you have SQL that frequently tests for NULL, consider creating an index on NULL values.  To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns. 
   
• Leave column names alone - Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI).  Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:

where salary*5            > :myvalue
where substr(ssn,7,4)     = "1234"
where to_char(mydate,mon) = "january"

• Avoid the use of NOT IN or HAVING. Instead, a NOT EXISTS subquery may run faster (when appropriate).
   
• Avoid the LIKE predicate = Always replace a "like" with an equality, when appropriate.
   
• Never mix data types - If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:

where cust_nbr = "123"
where substr(ssn,7,4) = 1234

• Use decode and case - Performing complex aggregations with the "decode" or "case" functions can minimize the number of times a table has to be selected.
   
• Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes.  If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan.  This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache.
   
• Use those aliases - Always use table aliases when referencing columns.

Also, see these related SQL tuning notes:

Conclusion

This article should provide you with a good overall background in Oracle SQL tuning, although there are many details that are too involved to discuss in one article. 

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.