| Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) Part Number B28275-01 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) Part Number B28275-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 the names of all employees in a department. When Oracle Database executes the SQL statement, the query optimizer (also called simply the optimizer) first determines the best and most efficient way to retrieve the results.
The optimizer determines whether it is more efficient to read all data in the table, called a full table scan, or use an index. It compares the cost of all possible approaches and chooses the approach with the least cost. The access method for physically executing a SQL statement 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.
The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can invoke the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes or materialized views, and refreshing optimizer statistics. Additionally, Oracle Enterprise Manager (Enterprise Manager) enables you to accept and implement many of these recommendations with just a few mouse clicks.
SQL Access Advisor is primarily responsible for making schema modification recommendations, such as adding or dropping indexes and materialized views. SQL Tuning Advisor makes other types of recommendations, such as creating SQL profiles and restructuring SQL statements. If significant performance improvements can be gained by creating a new index, then SQL Tuning Advisor may recommend it. However, such recommendations should be verified by running 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 SQL Access Advisor
You can use SQL Tuning Advisor to tune one or more SQL statements. When tuning multiple statements, keep in the mind that SQL Tuning Advisor does not recognize interdependencies between the SQL statements. Instead, it is intended as a convenient way to run SQL Tuning Advisor for a large number of SQL statements.
Besides enabling you to tune SQL statements manually, Oracle Database generates SQL tuning reports automatically. Automatic SQL Tuning runs during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of high-load 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 on the Recommendation Detail page to invoke SQL Tuning Advisor.
To tune SQL statements manually using SQL Tuning Advisor:
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Advisors.
The SQL Advisors page appears.
Under SQL Tuning Advisor, click SQL Tuning Advisor.
The Schedule SQL Tuning Advisor page appears.
In the Name field, enter a name for the SQL tuning task.
If unspecified, a system-generated name is used.
Do one of the following:
To run a SQL tuning task for one or more high-load SQL statements, under SQL Tuning Advisor Data Source Links, click Top Activity.
The Top Activity page appears.
Under Top SQL, 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".
To run a SQL tuning task for historical SQL statements from the Automatic Workload Repository (AWR), under SQL Tuning Advisor Data Source Links, click Historical SQL (AWR).
The Historical SQL (AWR) page appears.
Under Historical SQL (AWR), 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.
To run a SQL tuning task for 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 SQL Tuning Advisor page reappears.
To display the SQL text of the selected statement, expand SQL Statements.
Under Scope, select the scope of tuning to perform. Do one of the following:
Select Limited.
A limited scope takes approximately 1 second to tune each SQL statement but does not recommend a SQL profile.
Select Comprehensive, and then set a time limit (in minutes) for each SQL statement in the Time Limit per Statement field, and a total time limit (in minutes) in the Total Time Limit field.
A comprehensive scope performs a complete analysis and recommends a SQL profile, when appropriate, but may take much longer. 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. This mode is both time and resource intensive because each time a query must be hard-parsed. Thus, you should only use comprehensive scope 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, do one of the following:
Select Immediately to run the SQL tuning task immediately, and then proceed to Step 10 after the SQL Tuning Results page appears.
Select Later to schedule a specific time in the future, and click OK.
Optionally, on the Advisor Central page, do one of the following:
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. Select the recommendation you want to implement and click View.
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.
On the SQL Tuning Results page, click View.
The Recommendations for SQL ID page appears.
If you used a SQL Tuning Set, then 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.
Optionally, if multiple recommendations are displayed, then do one of the following:
To view the original execution plan for the SQL statement, click Original Explain Plan.
To compare the new and original execution plans, click the icon in the Compare Explain Plans column.
To view the new execution plan for the SQL statement, click the icon in the New Explain Plan column.
For information about viewing execution plans, see "Viewing the SQL Execution Plan".
To implement the recommendation, click Implement.
The SQL Tuning Results page appears with a confirmation that the recommended action was completed.
By analyzing information stored in the Automatic Workload Repository (AWR), the database can identify routine maintenance tasks that need to be run. The automated maintenance tasks infrastructure (known as AutoTask) schedules these tasks to run in Oracle Scheduler time periods known as maintenance windows. By default, one window is scheduled for each day of the week. You can customize attributes of these maintenance windows, including start and end time, frequency, and days of the week.
AutoTask automatically schedules SQL Tuning Advisor to run during the maintenance windows. You can view the results of automated execution of SQL Tuning Advisor on observed high-load SQL statements.
To view automatic SQL tuning results:
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Advisors.
The SQL Advisors page appears.
Under SQL Tuning Advisor, click Automatic SQL Tuning Results.
The Automatic SQL Tuning Result Summary page appears.
The top half of the page includes sections for the status and activity summary of the SQL Tuning task.
The bottom half of the Automatic SQL Tuning Result Summary page shows statistics for the overall task and the profile effect.
The Tuned SQL DB Time Benefit chart estimates the weekly DB time saved by SQL profiles that were automatically implemented. The chart also shows time that could be saved if other recommended SQL profiles were implemented. For example, the Before bar in the Implemented set aggregates the DB times during the week before tuning for all SQL statements with profiles implemented. The After bar projects the new weekly cumulative DB time, calculated by lowering the time of each SQL statement according to the benefit found by test execution. Thus, the Implemented set shows DB time benefit that has already been realized, whereas the Recommended set shows the potential benefit of profiles that were not automatically accepted by the SQL Tuning Advisor.
Optionally, in the Task Status section, click Configure to change the attributes of the Automatic SQL Tuning task.
The Automated Maintenance Tasks Configuration page appears.
In this page, you can enable or disable the Automatic SQL Tuning task and specify which days it should run. Click Apply or Revert to return to the previous page.
In the Task Activity Summary section, leave All selected for the Time Period and then click View Report.
The Automatic SQL Tuning Result Details page appears.
The page lists SQL statements that have been automatically selected by the database as candidates for SQL tuning.
Under Recommendations, select a SQL statement and then click View Recommendations.
The Recommendations for SQL ID page appears.
This page can include recommendations for SQL profiles and indexes. See "Tuning SQL Manually Using SQL Tuning Advisor" to learn how to implement recommendations made by SQL Tuning Advisor.
A SQL Tuning Set is a database object that includes one or more SQL statements and their execution statistics and execution context. You can use the set as an input source for various advisors, such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer. You can load SQL statements into a SQL Tuning Set from different SQL sources, such as AWR, the cursor cache, or high-load SQL statements that you identified.
A SQL Tuning Set includes the following:
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. For information about transporting SQL Tuning Sets, see Oracle Database Performance Tuning Guide.
Using Oracle Enterprise Manager, you can manage SQL Tuning Sets by doing the following:
This section describes how to create a SQL Tuning Set by 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 initial options for the set such as name, owner, and description.
To specify options for creating a SQL Tuning Set:
On the Database Performance page, under Additional Monitoring Links, 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.
Optionally, if you want to create an empty SQL Tuning Set and add SQL statements to it at a later time, then complete the following steps:
Enable Create an empty SQL tuning set.
Click Next.
The Create SQL Tuning Set: Review page appears.
Review the SQL Tuning Set options that you have selected and click Submit.
The empty SQL Tuning Set is created. You can add SQL statements to it at a later time.
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 options are specified 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, 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 enables 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 SQL statements one time only.
In the Data Source field, select Cursor Cache. 
Description of the illustration sts_load_cursor.gif
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 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 and then go to Step 5.
Only snapshots that are captured and stored in AWR in the last 24 hours will be included.
Select Last 7 days and then go to Step 5.
Only snapshots that are captured and stored in AWR in the last 7 days will be included.
Select Last 31 days and then go to Step 5.
Only snapshots that are captured and stored in AWR in the last 31 days will be included.
Select ALL and then go to Step 5.
All snapshots that are captured and stored in AWR will be included.
Select Customize and then proceed to Step 4.
Only snapshots that are captured and stored in AWR during a customized time period that you specify will be included.
To select a customized time period of snapshots to include, complete the following steps:
Select Customize and click Go.
The Select Time Period window opens.
For the starting snapshot, select Period Start Time and click the snapshot icon below the Active Session graph that corresponds to the desired start time.
For the ending snapshot, select Period End Time and click the snapshot icon below the Active Session graph that corresponds to the desired end time.
Click Select.
In this example, the snapshot taken on March 4, 2007 at 9:01 p.m. is selected as the start time, and the snapshot taken on March 5, 2007 at 1:00 a.m. is selected as the end time.
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 captured in AWR baselines. 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 AWR baselines:
On the Create SQL Tuning Set: Load Methods page, select Load SQL statements one time only.
In the Data Source field, select AWR Baseline.
In the AWR Baseline field, select the baseline 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 AWR baseline.
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 format of 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:
Using filters directs the various advisors that use the SQL Tuning Set as a workload source—such as SQL Tuning Advisor, SQL Access Advisor, and 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, do the following: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 to 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 the following:
Plan hash value
Module
Action
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, do the following: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, under Job Parameters, enter a name 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, do one of the following:
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, then a message is displayed to inform you that the job and the SQL Tuning Set were 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 job, such as operation status, click View Job Details.
The View Job page appears to display details about the job.
This section describes how to drop a SQL Tuning Set. To conserve storage space, you may want to periodically drop unused SQL Tuning Sets stored in the database.
To drop a SQL Tuning Set:
On the Database Performance page, under Additional Monitoring Links, 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 drop and click Drop.
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.
This section contains the following topics:
This section describes how to export a SQL Tuning Set, thereby enabling it to be transported to another system.
To export a SQL Tuning Set:
On the Database Performance page, under Additional Monitoring Links, 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 Oracle 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.
Under Job Parameters, in the Job Name field, enter a name for the job.
Alternatively, you can accept the name generated by the system.
Under Schedule, do one of the following:
Select Immediately to run the job immediately after it has been submitted.
Select Later to run the job at a later time as specified by selecting or entering 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.
Optionally, transport the export file to another system using the mechanism of choice (such as Oracle Data Pump or a database link).
Before a SQL Tuning Set can be imported, you must first export a SQL Tuning Set from another system and transport it to your current system. For more information, see "Exporting a SQL Tuning Set".
To import a SQL Tuning Set:
On the Database Performance page, under Additional Monitoring Links, click SQL Tuning Sets.
The SQL Tuning Sets page appears.
Click Import.
The Import SQL Tuning Set page appears.
In Directory Object, select a directory where the import file is stored.
The directory should contain the export file that was transported to your current system. For example, if the file resides in the Data Pump directory, then select DATA_PUMP_DIR. The Directory Name field refreshes automatically to indicate the selected directory.
In the Import File field, enter the name of the dump file that will be imported.
In the Log File field, enter a name for the log file for the import operation.
To replace an existing SQL Tuning Set with the one that you are importing, select Replace the existing SQL tuning set if one exists.
Select a tablespace to temporarily store the data for the import operation.
By default, SYSAUX is used.
Under Job Parameters, in the Job Name field, enter a name for the job.
Alternatively, you can accept the name generated by the system.
Under Schedule, do one of the following:
Select Immediately to run the job immediately after it has been submitted.
Select Later to run the job at a later time as specified by selecting or entering 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. If the job is scheduled to run immediately, then the imported SQL Tuning Set will be displayed on this page. You may need to refresh the page.
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 create 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, then 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, then you should enable it again to avoid performance degradation. 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 Performance page, click Top Activity.
The Top Activity page appears.
Under Top SQL, click the SQL ID link of the SQL statement that is using a SQL profile.
The SQL Details page appears.
Click the Tuning Information tab.
A list of SQL profiles is displayed under SQL Profiles and Outlines.
Select the SQL profile you want to manage. Do one of the following:
To enable a SQL profile that is disabled, click Disable/Enable.
To disable a SQL profile that is enabled, Disable/Enable.
To remove a SQL profile, click Delete.
A confirmation page appears.
Click Yes to continue, or No to cancel the action.
SQL plan management records and evaluates the execution plans of SQL statements over time. This mechanism builds SQL plan baselines composed of a set of existing plans known to be efficient. If the same SQL statement is run repeatedly, and if the optimizer generates a new plan that differs from the baseline, then the database compares the new plan with the baseline and chooses the best one.
Some events can cause changes to SQL execution plans, such as new optimizer statistics, changes to initialization parameter values, a database upgrade that causes a change to the optimizer, and so on. These changes can cause regressions in SQL performance, which can be difficult and time-consuming to fix manually. As a preventative measure, you can fix SQL plan baselines to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.
To load SQL execution plans:
From the Database Home page, click Server.
The Server subpage appears.
Under Query Optimizer, click SQL Plan Control.
The SQL Profile subpage of the SQL Plan Control page appears.
Click SQL Plan Baseline.
The SQL Plan Baseline subpage appears.
Under Settings, click the link next to Capture SQL Plan Baselines.
The Initialization Parameters page appears.
In the Value column of the table, select TRUE and then click OK.
You are returned to the SQL Plan Baseline subpage, which now shows Capture SQL Baselines set to TRUE.
Because you configured baselines to be captured, the database automatically keeps a history of execution plans for all SQL statements executed more than once.
Click Load.
The SQL Plan Control page appears.
Select the SQL plan baselines to be loaded. Complete the following steps:
Under Load SQL Plan Baselines, select Load plans from SQL Tuning Set (STS).
In this example, load plans from the SQL Tuning Set that you created in "Creating a SQL Tuning Set".
In Job Name, enter a name for the job. In this example, enter SPM_LOAD_TEST.
Under Schedule, select Immediately.
Click OK.
The SQL Profile subpage of the SQL Plan Control page appears. The table displays a list of SQL plans that are stored as SQL plan baselines.
Optionally, fix the execution plan of a baseline so that the database will not use any alternative SQL plan baseline. Complete the following steps:
Select a SQL plan baseline that is not fixed.
Select Fixed - Yes from the list preceding the baseline table.
Click Go.
The table is refreshed to show the SQL execution plan with the value YES in the Fixed column of the table.
See Also:
Oracle Database Performance Tuning Guide to learn how to use SQL plan management
