14 SQL Statement Tuning

How do I…

14.1 Identify potentially inefficient queries with the V$SQLAREA view?

14.2 Use EXPLAIN PLAN to analyze query execution paths?

14.3 Use the AUTOTRACE system variable to analyze query performance?

14.4 Determine elapsed execution time in SQL*Plus?

14.5 Analyze SQL statements with SQL*Trace and the TKPROF utility?

14.6 Set the optimizer mode?

14.7 Pass hints to the optimizer?

14.8 Suppress indexes in a query?

14.9 Structure data warehouses for star query optimization?

14.10 Register applications to simplify performance tracking?

Database tuning is not an exact science. Almost everyone agrees, however, that the first place to start tuning is at the application level. The most important part of this task is to review and tune the SQL statements that users or applications issue against the database. The tuning operations that will be outlined in Chapter 15 tune the database at a fairly low level, focusing on memory, disk load balancing, and the like. None of these measures are worthwhile, however, if Oracle8 is continually processing badly written SQL statements. This chapter presents techniques for identifying problematic SQL statements, examining their execution paths, and altering the statements or the database structure to improve application performance.

14.1 Identify Potentially Inefficient Queries with the V$SQLAREA View

Most databases process many queries and the first step towards tuning them is identifying the statements that may be responsible for performance problems. This How-To presents a method for identifying queries that have exceeded a user-defined threshold value of disk activity.

14.2 Use EXPLAIN PLAN to Analyze Query Execution Paths

The sequence of physical operations that Oracle8 performs to execute a particular statement is called the statement’s execution path. One goal of application tuning is to maximize the proportion of operations that Oracle8 performs in memory versus those that the server performs on disk. Oracle8 can process most queries in more than one way. This How-To discusses the use of the EXPLAIN PLAN statement to reveal a SQL statement’s execution path.

14.3 Use the AUTOTRACE System Variable to Analyze Query Performance

The AUTOTRACE system variable provides an alternative to EXPLAIN PLAN for SQL statement analysis. This How-To explains AUTOTRACE and compares this analysis method to EXPLAIN PLAN.

14.4 Determine Elapsed Execution Time

How long does it take? This is a common question you should ask about any new query or data management SQL statement. The first step to tuning a query is to identify performance problems. This How-To explores two methods for timing queries and presents examples of each method.

14.5 Analyze SQL Statements with SQL*Trace and the TKPROF Utility

SQL*Trace is similar to EXPLAIN PLAN in that it analyzes the performance of SQL statements when they are executed. It also generates physical execution statistics, however, such as rows returned, cpu time, elapsed time, and number of disk accesses.

Once a developer of DBA enables SQL*Trace, Oracle analyzes the execution of all of an application’s SQL statements and writes this analysis to a trace file. The TKPROF utility converts the trace file to a readable form. This How-To presents the use of SQL*Trace and TKPROF to analyze SQL statements.

14.6 Set the Optimizer Mode

The optimizer mode setting indicates the method Oracle should use to determine the best access path to the data. Two optimizer modes are available in Oracle8 as well as three ways to tell Oracle which one to use. This How-To discusses optimizer modes and outlines techniques for setting them.

14.7 Pass Hints to the Optimizer

Skilled developers are sometimes able to make better decisions about statement execution paths than the optimizer. In these cases, users can employ optimizer hints to tell Oracle how to execute SQL statements. This How-To presents the method for passing hints to the optimizer.

14.8 Suppress Indexes in a Query

When a query returns more than five percent of the rows in a table developers should suppress an index. This is accomplished by using optimizer hints (explained in the previous How-To) or by modifying the WHERE clause of the query to change its syntax but not its effect. This How-To presents methods used to suppress an index during a query.

14.9 Structure Data Warehouses for Star Query Optimization

The star schema is common in data warehouses. It typically consists of multiple small dimension tables and one large table, called a fact table, storing data related to an ongoing enterprise business process. SQL statements typically target these tables during ad hoc, read-only queries. If the schema and the query are carefully structured, then special optimization techniques can result in dramatic performance gains. This How-To explains how to architect data warehouse structures for star query optimization.

14.10 Register Applications to Simplify Performance Tracking

With practice, DBAs and developers will be able to identify which SQL statements to tune and will know what SQL modifications are necessary to enhance application performance. What can remain elusive in a busy database, however, is the origin of SQL statements in need of tuning. In other words, you may not only know what to tune and how to tune it, but you may not know where it came from. Application registration can help and is the subject of this How-To.