|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
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.
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 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:
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.
Poorly performing SQL arises in applications for a number of reasons:
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.
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.
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.
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.
Within a query, you can specify hints that direct the Cost-based optimizer in its processing of the query.
Hints affect the following:
As described above, hints can be used to determine the optimizer mode.
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.
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.
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.
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.
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".
There are several ways to begin a tuning session, depending on the status of the SQL you want to tune.
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.
After reviewing the statistics, it's time to tune the statement. You can:
You can use the same methods you used for gathering information to verify that the performance of your statement has been improved: