3
Introduction to Oracle SQL Analyze

This chapter contains the following topics:

• Introduction
• Benefits of Oracle SQL Analyze
• SQL Tuning as Part of an Overall Tuning Process
• Methods of SQL Analysis and Tuning
• The SQL Tuning Process

Introduction

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:

• awareness of the current environment and data
• knowledge of all schema objects
• an understanding of the Oracle Optimizer
• an in-depth knowledge of SQL

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

• Provides TopSQL functionality to identify the SQL statements that consume the most resources.
• Provides access to SQL history or SQL statements that have already been run against the database.
• Executes SQL under various optimizer modes and presents explain plans and execution statistics for easy comparison.
• Walks you through the explain plan, providing the order of execution and an explanation of the operations.
• Steps you through an analysis of a SQL statement's potential join orders and methods, and provides alternative SQL for improved performance.
• Automatically checks SQL statements for basic SQL design "rules-of-thumb" violations and generates alternative SQL that corrects them.
• Presents relevant object properties to help identify and correct problems that will impact SQL performance.
• Provides easy access to initialization parameter settings that have a direct impact on SQL performance.
• Helps you add hints to your statement with the Hints Wizard.
• Saves SQL statements, execution plans, and performance statistics in a repository for future use.
• Provides index recommendations to improve SQL performance

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:

• Charting Capability for Performance Profile Comparison: The Compare Execution Statistics feature (introduced in SQL Analyze version 2.2) now allows you to select multiple explain plans and view the execution statistics for each statement in a color-coded chart. These charts show a visual representation of the overall cost of each explain plan in direct comparison to the other explain plans you have selected.
• Virtual Index Creation: This wizard allows you to test and understand how a new index will affect SQL performance. You can define an index and then, without actually creating the index, understand how this index would affect the execution plan for an individual SQL statement.

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.

SQL Tuning Issues

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

• SQL is easier to write than to analyze. Although SQL is a relatively easy language to learn, its non-procedural nature tends to obscure performance-related issues. As a result, it's much harder to write efficient SQL than it is to write functionally correct SQL.
• Collecting data and analyzing performance can be difficult and time-consuming. Before the advent of Oracle SQL Analyze, you would need to run several different SQL scripts to obtain statistics describing the various database objects such as indexes and views, to generate the explain plan, and to calculate the performance statistics for execution of a specific SQL statement. And picking the poor performer out of all the statements run against your database could be a particularly daunting task. Oracle SQL Analyze collects the relevant data for you, and helps you identify problem statements by the resources they consume.
• Programmers assume the optimizer will make the best decisions. The Oracle optimizer is the part of the Oracle server which tries to determine the most efficient way to execute your SQL statements. Although it can choose between hundreds of possible approaches much faster than any individual SQL programmer, the programmer might have essential information about the application's nature or environment that the optimizer lacks. The optimizer is an excellent aid, but it cannot make as good a decision as an experienced SQL programmer.

  SQL Analyze lets you adjust environmental information and compare different optimizer modes and execution plans, helping you to determine the most efficient way to execute your SQL statements.

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:

• the relative cost of executing the statement (if you use the cost-based optimizer)
• the execution path chosen by the optimizer
• which indexes are being used
• which join methods are being used
• the order in which joins are performed

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.

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:

• Rule evaluates possible execution paths and rates the alternative execution paths based on a series of syntactical rules.
• Cost First executes the statement in a way that most efficiently retrieves the first row of data.
• Cost All executes the statement in a way that least impacts overall performance.
• Choose invokes the Cost All mode if any tables have been analyzed, and the Rule-Based mode if no tables have been analyzed.

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:

• Execution paths

  As described above, hints can be used to determine the optimizer mode.

• Data access methods

  Hints can force the optimizer to use specific scan methods while executing a statement. For example, it can direct the optimizer to use specific index scans instead of performing full table scans.

• Parallel execution

  Hints can be used to enhance parallel operations, which can lead to potentially significant cost reduction.

  Oracle SQL Analyze provides a Hint Wizard that helps you add syntactically correct hints to your SQL statement.

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.

• To identify problem statements that have been run or are about to be run on the database, you'll want to begin by analyzing the statements gathered by TopSQL or stored in the SQL History. After you have determined the statements that need tuning, you can go on to Step Two, "Gathering Information."
• To create a new statement, you can enter a new SQL statement or import or copy an existing SQL statement into Oracle SQL Analyze.
• You can open a SQL file and edit the statements within the file.
• You can go back to SQL statements from a previous tuning session.
• You can go back and edit a previous tuning session.

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.

• To learn more about the tuning environment, take a look at the initialization parameters.
• To learn more about the SQL statement's performance, generate an explain plan. Walking through the explain plan will show you how the statement is executed. You may also want to compare the explain plan with another plan. Within the explain plan, you can gather information about:
  • Performance statistics
  • The properties of Objects such as tables, clusters, indexes, and views
  • The join order selected by the optimizer
  • Degree of Parallelism
  • Distribution

Step Three: Tuning the Statement

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

• Edit the statement manually.
• Use the Hint Wizard to add hints that specify:
  • the optimization approach for a SQL statement
  • the goal of the cost-based approach for a SQL statement
  • the access path for a table accessed by the statement
  • the join order for a join statement
• Use the SQL Tuning Wizard to help you tune for optimum performance. The SQL Tuning Wizard helps you:
  • add hints to the statement
  • apply SQL syntax guidelines (rules-of-thumb)
  • measure the effectiveness of your tuning efforts by comparing the tuned statement against the original
• Get tuning recommendations. Oracle SQL Analyze will provide recommendations and a SQL script that you can use to implement the suggested changes.

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:

• execute new statements and compare the results
• generate new explain plans and compare them to previous explain plans
• review object properties to ensure they are accurate