| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-01 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-01 |
|
|
View PDF |
A SQL statement expresses the data you want Oracle Database to retrieve. For example, you can use a SQL statement to retrieve all employees in a department. When Oracle Database executes the SQL statement, it first determines the best and most efficient way to retrieve the results. The part of Oracle Database that makes this determination is called the query optimizer, or simply the optimizer. The optimizer determines if it is more efficient to read all data in the table, called a full table scan, or to use an index. It compares the cost of all possible approaches and chooses the approach with the least cost. The method that a SQL statement is physically executed is called an execution plan, which the optimizer is responsible for generating. The determination of an execution plan is an important step in the processing of any SQL statement, and can greatly affect execution time.
Starting with Oracle Database 10g, the query optimizer can also help you tune SQL statements. Using the SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a given SQL statement, or a set of SQL statements, and provide recommendations to improve their efficiency. The SQL Tuning Advisor and SQL Access Advisor can make various types of recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics. Additionally, Enterprise Manager enables you to accept and implement many of these recommendations with just a few mouse clicks.
The SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. The SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements. In some cases where significant performance improvements can be gained by creating a new index, the SQL Tuning Advisor may recommend doing so. However, such recommendations should be verified by running the SQL Access Advisor using a SQL workload that contains a set of representative SQL statements.
This chapter describes how to tune SQL statements using the SQL Tuning Advisor and contains the following sections:
See Also:
Chapter 11, "Optimizing Data Access Paths" for information about the SQL Access Advisor
You can use the SQL Tuning Advisor to tune a single or multiple SQL statements. When tuning multiple SQL statements, keep in the mind that the SQL Tuning Advisor does not recognize interdependencies between the SQL statements. Instead, it is meant to be a convenient way for you to run the SQL Tuning Advisor for a large number of SQL statements.
As described in Chapter 9, "Identifying High-Load SQL Statements", ADDM automatically identifies high-load SQL statements. In such cases, simply click Schedule/Run SQL Tuning Advisor in the Recommendation Detail page to invoke the SQL Tuning Advisor. This section describes how to run the SQL Tuning Advisor manually to tune SQL statements.
To tune SQL statements using the SQL Tuning Advisor:
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Tuning Advisor.
The SQL Tuning Advisor Links page appears.
To run a SQL tuning task for:
One or more high-load SQL statements, click Top Activity.
The Top Activity page appears.
In the Top SQL section, select the SQL statement you want to tune and click Schedule SQL Tuning Advisor. For information about identifying high-load SQL statements using the Top Activity page, see "Identifying High-Load SQL Statements Using Top SQL".
Historical SQL statements from the Automatic Workload Repository (AWR), click Period SQL.
The Period SQL page appears. Under Historical Interval Selection, click the band below the chart, and select the 24-hour interval for which you want to view SQL statements that ran on the database. Under Detail for Selected 24 Hour Interval, select the SQL statement you want to tune, and click Schedule SQL Tuning Advisor.
A SQL tuning set, click SQL Tuning Sets.
The SQL Tuning Sets page appears. Select the SQL tuning set that contains the SQL statements you want to tune and click Schedule SQL Tuning Advisor. For information about creating SQL tuning sets, see "Creating a SQL Tuning Set".
The Schedule Advisor page appears.
Under Scope, select the scope of tuning to perform.
A limited scope takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile. A comprehensive scope performs a complete analysis and recommends a SQL profile, when appropriate, but may take much longer. When running a comprehensive tuning task, you can set a time limit (in minutes) in the Total Time Limit field. Note that setting the time limit too small may affect the quality of the recommendations. Running a SQL Tuning Advisor task in comprehensive mode may take several minutes to tune a single SQL statement, and is both time and resource intensive to do so every time a query has to be hard-parsed. This method should only be used for high-load SQL statements that have a significant impact on the entire system.
For information about SQL profiles, see "Using SQL Profiles".
Under Schedule, select Immediately to run the SQL tuning task immediately, or Later to schedule a specific time in the future, and click OK.
Depending on your selection, the SQL tuning task will either run immediately or at its scheduled time.
If the SQL tuning task runs immediately, the SQL Tuning Results page appears once the task is complete. Proceed to Step 8.
If the SQL tuning task is scheduled to run at a later time, various actions can be performed on the Advisor Central page:
To view results for the SQL tuning task after it completes, select the SQL Tuning Advisor task and click View Result.
The SQL Tuning Results page appears. Proceed to the next step.
To delete a SQL tuning task, select the SQL Tuning Advisor task and click Delete.
To reschedule a SQL tuning task, select the SQL Tuning Advisor task. From the Actions list, select Re-schedule and click Go.
To interrupt a SQL tuning task that is running, select the SQL Tuning Advisor task. From the Actions list, select Interrupt and click Go.
To cancel a scheduled SQL tuning task, select the SQL Tuning Advisor task. From the Actions list, select Cancel and click Go.
To change the expiration of a SQL tuning task, select the SQL Tuning Advisor task. From the Actions list, select Change Expiration and click Go.
Results of each advisor run are stored in the database so that they can be referenced later. This data is stored until it expires, at which point it will be deleted by the AWR purging process.
To edit a scheduled SQL tuning task, select the SQL Tuning Advisor task. From the Actions list, select Edit and click Go.
The Recommendations for SQL ID page appears. To implement the recommendation, click Implement.
If you used a SQL tuning set, multiple recommendations may be displayed. To help you decide whether or not to implement a recommendation, an estimated benefit of implementing the recommendation is displayed in the Benefit (%) column. The Rationale column displays an explanation of why the recommendation is made. To view the original execution plan for the SQL statement, click Original Explain Plan. To view the new execution plan for the SQL statement, click the icon in the New Explain Plan column.
The SQL Tuning Results page appears with a confirmation that the recommended action was completed.
A SQL tuning set is a database object that includes one or more SQL statements, their execution statistics and execution context, and can be used as an input source for the SQL Tuning Advisor and SQL Access Advisor. You can load SQL statements into a SQL tuning set from different SQL sources, such as the Automatic Workload Repository (AWR), the cursor cache, or high-load SQL statements that you identified.
A SQL tuning set includes:
A set of SQL statements
Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment
Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type
Associated execution plans and row source statistics for each SQL statement (optional)
SQL statements can be filtered using the application module name and action, or any of the execution statistics. In addition, SQL statements can be ranked based on any combination of execution statistics.
SQL tuning sets are transportable across databases and can be exported from one system to another, allowing SQL workloads to be transferred between databases for remote performance diagnostics and tuning. When high-load SQL statements are identified on a production system, it may not be desirable to perform investigation and tuning activities on the production system directly. This feature enables you to transport the high-load SQL statements to a test system, where they can be safely analyzed and tuned.
See Also:
Oracle Database Performance Tuning Guide for information about transporting SQL tuning sets
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Tuning Advisor.
The SQL Tuning Advisor Links page appears.
To view and create a SQL tuning set, click SQL Tuning Sets.
The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.
A SQL tuning set can be created from SQL statements selected from period SQL, snapshots, or preserved snapshot sets.
To create a SQL tuning set from:
Period SQL
From the Create SQL Tuning Set From list, select Period SQL and click Go.
The Period SQL page appears. Under Historical Interval Selection, click the band below the chart, and select the 24-hour interval for which you want to view SQL statements that will be used in the SQL tuning set. Under Detail for Selected 24 Hour Interval, select the desired SQL statements, and click Create SQL Tuning Set.
Snapshots
From the Create SQL Tuning Set From list, select Snapshots and click Go.
The Snapshots page appears. Under Select Beginning Snapshot, select the starting point for the range of snapshots containing the SQL statements to be used in the SQL tuning set. From the Actions list, select Create SQL Tuning Set and click Go.
Preserved snapshot set
From the Create SQL Tuning Set From list, select Preserved Snapshot Sets and click Go.
The Preserved Snapshot Sets page appears. Select the preserved snapshot set containing the SQL statements that you want to include in the SQL tuning set. From the Actions list, select Create SQL Tuning Set and click Go.
The Create SQL Tuning Set page appears.
To create the SQL tuning set, click OK. For SQL tuning sets created using snapshots, first select the end point for the range of snapshots before clicking OK.
The newly created SQL tuning set appears on the SQL Tuning Sets page.
To view the SQL statements in a SQL tuning set, on the SQL Tuning Sets page, select the SQL tuning set and click View.
The SQL statements in the SQL tuning set and their details are displayed.
When running a SQL Tuning Advisor task with a limited scope, the query optimizer makes estimates about cardinality, selectivity, and cost. These estimates can sometimes be off by a significant amount, resulting in poor execution plans.
To address this problem, consider running a SQL Tuning Advisor task with a comprehensive scope to collect additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates. These auxiliary statistics about the SQL statement are collected into a SQL profile.
During SQL profiling, the query optimizer uses the execution history information about the SQL statement to set appropriate settings for optimizer parameters. After the SQL profiling completes, the query optimizer uses the information stored in the SQL profile, in conjunction with regular database statistics, to generate execution plans. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statements.
After running a SQL Tuning Advisor task with a comprehensive scope, a SQL profile may be recommended. If you accept the recommendation, the SQL profile will be created and enabled for the SQL statement.
In some cases, you may want to disable a SQL profile. For example, you may want to test the performance of a SQL statement without using a SQL profile to determine if the SQL profile is actually beneficial. If the SQL statement is performing poorly after the SQL profile is disabled, you should enable it again to avoid performance deterioration. If the SQL statement is performing optimally after you have disabled the SQL profile, you may want to remove the SQL profile from your database.
To enable, disable, or delete a SQL profile:
On the Database Performance page, click Top Activity.
The Top Activity page appears.
Under Top SQL, click the SQL ID link of the SQL statement this is using a SQL profile.
The SQL Details page appears.
Click the Tuning Information tab.
A list of SQL profiles are displayed under SQL Profiles and Outlines.
Select the SQL profile you want to manage. To:
Enable a SQL profile that is disabled, click Disable/Enable.
Disable a SQL profile that is enabled, Disable/Enable.
Remove a SQL profile, click Delete.
A confirmation page appears.
Click Yes to continue, or No to cancel the action.
