Tune the SQL Statement Oracle Tips by Burleson Consulting

The process of tuning the SQL statement involves several activities. SQL tuning moves from a global level to a specific one. The goal of SQL tuning is to verify that the execution plan is optimal for the statement. Verifying the speed to SQL execution is normally done by using SQL*Plus with the set timing on command and actually timing the speed of the query.

• Changing the optimizer mode  You can try changing the optimizer mode to  rule, all_rows, or first_rows. This will normally result in several execution plans, and each must be timed to determine the plan with the fastest execution time.

• Adding indexes  You can add indexes (especially bitmapped and function-based indexes) to remove unwarranted full-table scans. However, be aware that adding an index can change the execution plan for many other SQL statements. It is not uncommon to add an index only to find that the speed of other SQL statements changes.

• Adding hints  You can force a change to the execution plan by adding hints to the select statement. Oracle provides dozens of hints to change the execution plan for SQL, and we will be going into detail on hint-based SQL tuning in Chapter 12.

Make the Tuning Permanent

Once tuned, it is critical that the tuning change become permanent. There are several methods for doing so. Making a SQL tuning change permanent is especially important in an environment where global changes such as changing the optimizer_mode initialization parameter or adding indexes could potentially change the execution plans for many SQL statements.

Optimizer Plan Stability

Oracle8i provides a new package called OUTLINE that allows the Remote DBA to store a ready-to-run execution plan for any SQL statement. This utility has several features:

• Parsing and execution time is reduced because Oracle will quickly grab and execute the stored outline for the SQL.
• Tuning of SQL statements can easily be made permanent without locating the source code.
• SQL from third-party products (e.g., SAP, Peoplesoft) can be tuned without touching the SQL source code.

Optimizer plan stability enables you to maintain the same execution plans for the same SQL statements, regardless of changes to the database such as reanalyzing tables; adding or deleting data; modifying a table's columns, constraints, or indexes; changing the system configuration; or even upgrading to a new version of the optimizer.

To use optimizer plan stability, you must run the dbmsol.sql script from $ORACLE_HOME/rdbms/admin. When the script is executed, a new Oracle user called OUTLN is created (with Remote DBA privileges) and a package called OUTLN_PKG is installed to provide procedures used for managing stored outlines.

Oracle provides the CREATE OUTLINE statement to create a stored outline. The stored outline contains a set of attributes that the optimizer uses to create an execution plan. Stored outlines can also be created automatically by setting the initialization parameter create_stored_outlines=true. For more details on using stored outlines, see your Oracle8i documentation and Chapter 13.

Change the SQL Source

In pre-Oracle8i environments, the SQL source code must be located in order to make the tuning change permanent. This can be a challenging problem for an application where the SQL is distributed in client-side applications, systems that use ODBC to communicate with Oracle, and systems that generate dynamic SQL.

In practice, the Oracle Remote DBA should strongly advocate removing all SQL from application programs. This is normally achieved by placing all SQL inside stored procedures, and then placing the stored procedures inside Oracle packages. This approach makes the SQL source easy to locate because it is in the Oracle data dictionary. It also has the side benefit of making all remote applications portable, because all calls to Oracle are encapsulated into function and stored procedure calls.

Before we get into the details of this SQL tuning text, let’s review some of the tools that we will be using in this book. In order to make this book useful for everyone, we are going to use standard Oracle utilities and scripts for all of our SQL analysis. This alleviates the need for expensive third-party products and ensures that every reader can analyze and tune all SQL.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.