Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01


Book List



Master Index


Go to previous page Go to next page

Introduction to Oracle SQL Analyze

This chapter contains the following topics:


One of the primary benefits of the SQL language is its flexibility; you can achieve the same result by taking any of a number of different approaches. Although each approach might return the same result, performance varies dramatically depending on the database environment, structure of indexes and the access paths chosen by the Oracle optimizer.

While efficient SQL statements can help maintain peak database performance, inefficient statements can cause slow performance. In many cases, tuning your SQL statements can increase overall performance by 100% or more.

Tuning SQL, however, has not been easy in the past. It involves collecting and analyzing information, and requires expert knowledge and experience. Tuning a SQL statement requires:

Oracle SQL Analyze provides you with the tools to collect information about the database environment and schema objects, analyze SQL performance, identify and compare different optimizer approaches, and edit SQL statements for optimal performance--in some cases, automatically.

Benefits of Oracle SQL Analyze

What's New in Oracle SQL Analyze 9.0.1

Oracle SQL Analyze 9.0 introduces two new features that enhance the information you can use to tune your databases, and help automate the tuning process:

SQL Tuning as Part of an Overall Tuning Process

SQL tuning is, of course, only one part of a comprehensive tuning process. As described in Oracle9i Database Performance Guide and Reference, there are several other areas you will want to consider tuning. The tuning method prescribed in that guide suggests tuning in the following sequence:

  1. Tune the business rules
  2. Tune the data design
  3. Tune the application design
  4. Tune the logical structure of the database
  5. Tune the SQL
  6. Tune the access paths
  7. Tune the memory
  8. Tune the I/O and physical structure
  9. Tune the resource contention
  10. Tune the underlying platforms

Decisions you make in one step may influence subsequent steps. For example, in Step 5 you may rewrite some of your SQL statements. These SQL statements may have significant bearing on parsing and caching issues addressed in step 7. Also, disk I/O, which is tuned in step 8, depends on the size of the buffer cache, which is tuned in step 7. Tuning is an iterative process, from any step in the process it may be necessary to return to any previous step.

In this guide, we will be concerned primarily with tuning SQL statements. But as you will see, the logical and physical structure of the database, the access paths, the memory, and the I/O all have a bearing on the effectiveness of your SQL statements.

Oracle SQL Analyze helps you with these issues by providing information about the database structure and by letting you modify some initialization parameters to test the SQL statement against different conditions and database environments.


For detailed information on the SQL tuning process, see the Oracle9i Database Performance Guide and Reference.  

SQL Tuning Issues

Poorly performing SQL arises in applications for a number of reasons:

Methods of SQL Analysis and Tuning

As a SQL tuner, you need to be able to gather and analyze environmental data and performance statistics to help identify problem areas. The following sections describe the information you can gather and the methods Oracle SQL Analyze makes available for tuning your statements.

Analyzing Explain Plans

The explain plan allows you to evaluate the steps in an execution path for a SQL statement without actually executing the statement. The explain plan shows you the following:

You can use Oracle SQL Analyze to generate and walk through explain plans for each of the available optimizer modes (see the next section). Oracle SQL Analyze creates a graphical view of the explain plan and a compact view, which illustrates in more detail how joins are performed. The Compare Execution Statistics dialog allows you to select multiple explain plans and view the execution statistics for each statement in one view. See "Comparing Execution Statistics" for more information.


With the Oracle 9i release of the Oracle Tuning Pack, you can now get a graphical display of an explain plan from the Oracle Enterprise Manager console or from the Oracle Diagnostics Pack TopSQL charts. For more information on using the graphical explain plan, see the online help from the console. 

Controlling Optimizer Modes

The task of the Oracle optimizer is to find the most efficient method for executing a SQL statement. The optimizer has four primary modes of operation: Rule, Cost First, Cost All, and Choose. The mode you select directs the strategy of the optimizer:

These modes are explained further in Understanding Hints. You can set a default optimizer mode by specifying the OPTIMIZER_MODE parameter in your database's init.ora file. Additionally, you can set the optimizer mode for a specific SQL statement by adding hints to it.

But how do you know which optimizer mode will be most efficient for your statement? Oracle SQL Analyze lets you test each of these execution strategies against a SQL statement and provides cost information and performance statistics that help you to determine which mode is best.

Adding Hints

Within a query, you can specify hints that direct the Cost-based optimizer in its processing of the query.

Hints affect the following:

Applying Rules-of-Thumb

There are certain syntax variations that are known by Oracle experts to have a negative impact on performance. Oracle SQL Analyze can evaluate statements against a conservative set of rules, identifying less efficient coding and providing an alternative statement when possible. You can automatically evaluate your SQL against these rules using the SQL Analyze Tuning Wizard.

Analyzing Object Properties

The performance of a SQL statement is also affected by the space usage of the objects being accessed. Factors such as the existence of chained rows in a table can increase the number of I/Os required to retrieve the data set. Oracle SQL Analyze lets you examine the space usage details for tables, indexes, clusters, and views.

The SQL Tuning Process

This section suggests a methodology for using Oracle SQL Analyze to identify problem statements and tune them for greater efficiency. For a deeper understanding of the concepts involved, see Chapter 4, "Tuning SQL Statements".

Step One: Starting a Tuning Session

There are several ways to begin a tuning session, depending on the status of the SQL you want to tune.

Step Two: Gathering Information

After you have chosen a statement to tune, you need to understand more about the database environment in which the SQL statement is being executed, and more about the performance of the statement.

Step Three: Tuning the Statement

After reviewing the statistics, it's time to tune the statement. You can:

Step Four: Verifying Your Results

You can use the same methods you used for gathering information to verify that the performance of your statement has been improved:

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.


Book List



Master Index