| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-03 |
|
|
PDF · Mobi · ePub |
| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-03 |
|
|
PDF · Mobi · ePub |
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 "Managing 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.
Using Oracle Enterprise Manager, you can manage SQL tuning sets by:
This section describes how to create a SQL tuning set using Oracle Enterprise Manager.
Specify the initial options for the SQL tuning set, as described in "Creating a SQL Tuning Set: Options".
Select the load method to use for collecting and loading SQL statements into the SQL tuning set, as described in "Creating a SQL Tuning Set: Load Method".
Specify the filter options for the SQL tuning set, as described in "Creating a SQL Tuning Set: Filter Options".
Schedule and submit a job to collect the SQL statements and load them into the SQL tuning set, as described in "Creating a SQL Tuning Set: Schedule".
The first step in creating a SQL tuning set is to specify the initial options, such as name, owner, and description.
To specify options for creating a SQL tuning set:
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.
Click SQL Tuning Sets.
The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.
Click Create.
The Create SQL Tuning Set: Options page appears.
In the SQL Tuning Set Name field, enter a name for the SQL tuning set.
In the Owner field, enter the owner of the SQL tuning set.
In the Description field, enter a description of the SQL tuning set.
Click Next.
The Create SQL Tuning Set: Load Methods page appears.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Load Method".
After specifying options for the SQL tuning set, select the load method to use for collecting and loading SQL statements into the SQL tuning set, as described in the following sections:
Tip:
Before selecting the load method for the SQL tuning set, you need to create a SQL tuning set and specify the initial options, as described in "Creating a SQL Tuning Set: Options"You can load active SQL statements from the cursor cache into the SQL tuning set incrementally over a specified period of time. This allows you to not only collect current and recent SQL statements stored in the SQL cache, but also SQL statements that will run during the specified time period in the future.
To load active SQL statements incrementally from the cursor cache:
On the Create SQL Tuning Set: Load Methods page, select Incrementally capture active SQL statements over a period of time from the cursor cache.
In the Duration field, specify how long active SQL statements will be captured.
In the Frequency field, specify how often active SQL statements will be captured during the specified duration.
Click Next.
The Create SQL Tuning Set: Filter Options page appears.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".
You can load SQL statements from the cursor cache into the SQL tuning set. However, because only current and recent SQL statements are stored in the SQL cache, collecting these SQL statements only once may result in a SQL tuning set this is not representative of the entire workload on your database.
To load SQL statements from the cursor cache:
On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
In the Data Source field, select Cursor Cache.
Click Next.
The Create SQL Tuning Set: Filter Options page appears.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".
You can load SQL statements captured in AWR snapshots. This is useful when you want to collect SQL statements for specific snapshot periods of interest that can be used for later comparison or tuning purposes.
To load SQL statements from AWR snapshots:
On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
In the Data Source field, select AWR Snapshots.
In the AWR Snapshots field, select the snapshots to include. Do one of the following:
Select Last 24 hours.
Only snapshots that are captured and stored in the AWR in the last 24 hours will be included.
Select Last 7 days.
Only snapshots that are captured and stored in the AWR in the last 7 days will be included.
Select Last 31 days.
Only snapshots that are captured and stored in the AWR in the last 31 days will be included.
Select ALL.
All snapshots that are captured and stored in the AWR will be included.
The Create SQL Tuning Set: Filter Options page is shown.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".
You can load SQL statements captured in preserved snapshot sets. This is useful when you want to collect SQL statements that are representative of a time period during known performance levels that can be used for later comparison or tuning purposes.
To load SQL statements from preserved snapshot sets:
On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
In the Data Source field, select Preserved Snapshot Sets.
In the Preserved Snapshot Sets field, select the preserved snapshot set to include.
Click Next.
The Create SQL Tuning Set: Filter Options page is shown.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".
You can load SQL statements by importing from a table or view. This is useful if the workload you want to analyze is not currently running on the database, or captured in an existing AWR snapshot or a preserved snapshot set.
There are no restrictions on which schema the workload resides in, the name of the table, or the number of tables that you can define. The only requirement is that the format of the table must match the USER_WORKLOAD table.
To load SQL statements from a user-defined workload:
On the Create SQL Tuning Set: Load Methods page, select Load statements one time only.
In the Data Source field, select User-Defined Workload.
In the User-Defined Workload field, select the table or view to include.
Click Next.
The Create SQL Tuning Set: Filter Options page is shown.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Filter Options".
After the load method is selected, you can apply filters to reduce the scope of the SQL statements found in the SQL tuning set. While using filters is optional, it can be very beneficial due to the following benefits:
Using filters directs the various advisors that uses the SQL tuning set as a workload source—such as the SQL Tuning Advisor, the SQL Access Advisor, and the SQL Performance Analyzer—to make recommendations based on a specific subset of SQL statements, which may lead to better recommendations.
Using filters removes extraneous SQL statements from the SQL tuning set, which may greatly reduce processing time when it is used as a workload source for the various advisors.
Tip:
Before you can specify the filter options for the SQL tuning set, you need to:Create a SQL tuning set and specify the initial options, as described in "Creating a SQL Tuning Set: Options"
Select the load method, as described in "Creating a SQL Tuning Set: Load Method"
To specify filter options for a SQL tuning set:
On the Create SQL Tuning Set: Filter Options page, specify the values of filter conditions that you want use in the search in the Value column, and an operator or a condition in the Operator column.
Only the SQL statements that meet all of the specified filter conditions will be added into the SQL tuning set. Unspecified filter values will not be included as filter conditions in the search.
By default, the following filter conditions are displayed:
Parsing schema name
SQL text
SQL ID
Elapsed time (sec)
To add filter conditions, under Filter Conditions, select the filter condition you want to add and click Add a Filter or Column.
The available filter conditions include:
Plan hash value
Module
Action
CPU time (sec)
Buffer gets
Disk reads
Disk writes
Rows processed
Fetches
Executions
End of fetch count
Command type
After the desired filter conditions have been added, specify their values in the Value column, and an operator or a condition in the Operator column.
To remove any unused filter conditions, click the icon in the Remove column for the corresponding filter condition you want to remove.
Click Next.
The Create SQL Tuning Set: Schedule page appears.
Proceed to the next step, as described in "Creating a SQL Tuning Set: Schedule".
After the filter options are specified for the SQL tuning set, you can schedule and submit a job to collect the SQL statements and load them into the SQL tuning set.
Tip:
Before you can schedule a job to create the SQL tuning set, you need to:Create a SQL tuning set and specify the initial options, as described in "Creating a SQL Tuning Set: Options"
Select the load method, as described in "Creating a SQL Tuning Set: Load Method"
Specify the filter options, as described in "Creating a SQL Tuning Set: Filter Options"
To schedule and submit a job to create a SQL tuning set:
On the Create SQL Tuning Set: Schedule page, enter a name for the job in the Job Name field if you do not want to use the system-generated job name.
In the Description field, enter a description of the job.
Under Schedule, select:
Immediately to run the job immediately after it has been submitted
Later to run the job at a later time as specified using the Time Zone, Date, and Time fields
Click Next.
The Create SQL Tuning Set: Review page appears.
Review the SQL tuning set options that you have selected.
To view the SQL statements used by the job, expand Show SQL.
Click Submit.
The SQL Tuning Sets page appears.
If the job was scheduled to run immediately, a message is displayed to inform you that the job and the SQL tuning set was created successfully. If the job was scheduled to run at a later time, a message is displayed to inform you that the job was created successfully.
To view details about the SQL tuning set, select the SQL tuning set and click View.
The SQL Tuning Set page appears to display the SQL statements captured in the selected SQL tuning set.
This section describes how to delete a SQL tuning set. To conserve storage space, you may want to periodically remove unused SQL tuning sets stored in the database.
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.
Click SQL Tuning Sets.
The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.
Select the SQL tuning set you want to delete and click Delete.
The Confirmation page appears to verify if you want to delete the selected SQL tuning set.
Click Yes.
The SQL Tuning Sets page appears.
A confirmation message is displayed to indicate that the SQL tuning set was successfully deleted.
You can transport SQL tuning sets from one system to another by first exporting a SQL tuning set from one system, then importing it into another system.
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.
Click SQL Tuning Sets.
The SQL Tuning Sets page appears. Existing SQL tuning sets are displayed on this page.
Select the SQL tuning set you want to export and click Export.
The Export SQL Tuning Set page appears.
In the Directory Object field, select a directory where the export file will be created.
For example, to use the Data Pump directory, select DATA_PUMP_DIR. The Directory Name field refreshes automatically to indicate the selected directory.
In the Export File field, enter a name for the dump file that will be exported.
Alternatively, you can accept the name generated by the system.
In the Log File field, enter a name for the log file for the export operation.
Alternatively, you can accept the name generated by the system.
Select a tablespace to temporarily store the data for the export operation.
By default, SYSAUX is used.
In the Job Name field, enter a name for the job.
Alternatively, you can accept the name generated by the system.
Under Schedule, select:
Immediately to run the job immediately after it has been submitted.
Later to run the job at a later time as specified by the values in the Time Zone, Date, and Time fields.
Click OK.
The SQL Tuning Sets page appears.
A confirmation message is displayed to indicate that the job was successfully created.
Transport the export file to another system using the mechanism of choice (such as Data Pump or database link).
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.
|
 Copyright © 2006, 2010, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
