View and Work with the Output from the Database Engine Tuning Advisor (DTA); 23 minutes to read; In this article. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse When the Database Engine Tuning Advisor tunes databases, it creates summaries, recommendations, reports, and tuning logs. SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. You can submit one or more SQL statements as input to the advisor and receive advice or recommendations for how to tune the statements, along with a rationale and expected benefit.
APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse
When the Database Engine Tuning Advisor tunes databases, it creates summaries, recommendations, reports, and tuning logs. You can use the tuning log output to troubleshoot Database Engine Tuning Advisor tuning sessions. You can use the summaries, recommendations, and reports to determine whether you want to implement tuning recommendations or continue tuning until you achieve the query performance improvements that you need for your Microsoft SQL Server installation. For information about how to use the Database Tuning Advisor to create workloads and tune a database, see Start and Use the Database Engine Tuning Advisor.
View Tuning Output
The following procedures describe how to view tuning recommendations, summaries, reports, and tuning logs by using the Database Engine Tuning Advisor GUI. For information about user interface options, see User Interface Descriptions later in this topic.
You can also use the GUI to view tuning output that is generated by the dta command-line utility.
Note
If you use the dta command-line utility and specify that output be written to an XML file by using the -ox argument, you can open and view the XML output file by clicking Open File on the File menu of SQL Server Management Studio. For more information, see Use SQL Server Management Studio. For information about the dta command-line utility, see dta Utility.
To view tuning recommendations with the Database Engine Tuning Advisor GUI
To view the tuning summary and reports with the Database Engine Tuning Advisor GUI
To view tuning logs with the Database Engine Tuning Advisor GUI
Implement Tuning Recommendations
You can implement the Database Engine Tuning Advisor recommendations manually or automatically as part of the tuning session. If you want to examine the tuning results first before implementing them, use the Database Engine Tuning Advisor GUI. You can then use SQL Server Management Studio to manually run the Transact-SQL scripts that Database Engine Tuning Advisor generates as a result of analyzing a workload to implement the recommendations. If you do not need to examine the results before implementing them, you can use the -a option with the dta command prompt utility. This causes the utility to automatically implement the tuning recommendations after it analyzes your workload. The following procedures explain how to use both Database Engine Tuning Advisor interfaces to implement tuning recommendations.
To manually implement tuning recommendations with the Database Engine Tuning Advisor GUI
To automatically implement tuning recommendations using the dta command prompt utility
Perform Exploratory Analysis
The user-specified configuration feature of Database Engine Tuning Advisor enables database administrators to perform exploratory analysis. Using this feature, database administrators specify a desired physical database design to Database Engine Tuning Advisor, and then they can evaluate the performance effects of that design without implementing it. User-specified configuration is supported by both the Database Engine Tuning Advisor graphical user interface (GUI) and the command-line utility. However, the command-line utility provides the greatest flexibility.
If you use the Database Engine Tuning Advisor GUI, you can evaluate the effects of implementing a subset of a Database Engine Tuning Advisor tuning recommendation, but you cannot add hypothetical physical design structures for Database Engine Tuning Advisor to evaluate.
The following procedures explain how to use the user-specified configuration feature with both tool interfaces.
Using Database Engine Tuning Advisor GUI to Evaluate Tuning Recommendations
The following procedure describes how to evaluate a recommendation that is generated by Database Engine Tuning Advisor, but the GUI does not enable you to specify new physical design structures for evaluation.
To evaluate tuning recommendations with the Database Engine Tuning Advisor GUI
Using Database Engine Tuning Advisor GUI to Export Tuning Session Results for 'What-if' Tuning Analysis
The following procedure describes how to export Database Engine Tuning Advisor tuning session results to an XML file, which you can edit, and then tune it with the dta command-line utility. This enables you to perform tuning analysis on hypothetical new physical design structures without incurring the overhead of implementing them in your database before you find out whether they produce the performance improvements that you need. Using the Database Engine Tuning Advisor GUI to initially tune your database and then exporting the tuning results to an .xml file is a good way for users who are new to XML to use the flexibility of the Database Engine Tuning Advisor XML schema to perform 'what-if' analysis.
To export tuning session results from the Database Engine Tuning Advisor GUI for 'what-if' analysis with the dta command-line utility
Using the User-specified Configuration Feature with the dta Command Line Utility
If you are an experienced XML developer, you can create a Database Engine Tuning Advisor XML input file in which you can specify a workload and a hypothetical configuration of physical database design structures, such as indexes, indexed views, or partitioning. Then you can use the dta command-line utility to analyze the effects this hypothetical configuration has on query performance for your database. The following procedure explains this process step by step:
To use the user-specified configuration feature with the dta command line utility
Review, Evaluate, and Clone Tuning Sessions
The Database Engine Tuning Advisor creates a new tuning session each time that you start analyzing the effects of a workload on your database or databases. You can use the Session Monitor in the Database Engine Tuning Advisor GUI to view or reload all tuning sessions that have run on a given instance of Microsoft SQL Server. Having all the existing tuning sessions available to review makes it easy to: clone sessions based on existing ones, edit existing tuning recommendations and then use Database Engine Tuning Advisor to evaluate the edited session, or perform tuning at regular intervals to monitor the physical design of your databases. For example, you may decide to tune database on a monthly schedule.
Before you can review any tuning sessions for an instance of SQL Server, you must create tuning sessions on the server instance by tuning workloads with the Database Engine Tuning Advisor. For more information, see Start and Use the Database Engine Tuning Advisor.
Review Existing Tuning Sessions
Use the following steps to browse the existing tuning sessions on a given instance of SQL Server.
To review existing tuning sessions
Evaluate Existing Tuning Sessions As Hypothetical Configurations
Use the following steps to evaluate an existing tuning session. Evaluating an existing tuning session involves viewing and editing its recommendations, and then re-tuning. For example, you decide that you want to only create indexes on table1, so you delete the creation of indexed views and partitioning from an existing tuning recommendation. Then Database Engine Tuning Advisor creates a new tuning session and tunes the workload against your databases using the edited recommendations as a hypothetical configuration. This means that Database Engine Tuning Advisor tunes the workload against the databases as if the edited recommendations have been implemented, enabling you to perform limited 'what-if' analysis. It is limited what-if analysis because you can only choose a subset of an existing recommendation when you use the Database Engine Tuning Advisor GUI. To perform full what-if analysis, specifying a completely new hypothetical configuration that is not a subset of any previous tuning session, you must use the Database Engine Tuning Advisor XML input file with the dta command-line utility.
To evaluate an existing tuning session
Clone Existing Tuning Sessions
You can create new tuning sessions based on existing sessions by choosing the cloning option in Database Engine Tuning Advisor. When you use the cloning option, you base a new tuning session on an existing session. Then you can change the tuning options for the new session as needed. When you evaluate an existing session as described in the previous procedure, Database Engine Tuning Advisor also creates a new tuning session, but you cannot change the tuning options.
To create new tuning sessions by cloning existing sessions
User Interface DescriptionsSessions Monitor
Session Monitor displays information about sessions that are opened in the Database Engine Tuning Advisor. To display information about the session in the property window, select a session name in Session Monitor.
Recommendations Tab
The Recommendations tab appears after Database Engine Tuning Advisor completes analysis of a workload. This grid contains the recommendations for each object considered. Partition recommendations, if any, are presented in the upper grid, and the index recommendations are presented in the lower grid. The grids do not appear if there are no recommendations.
The Definition column contains the definition of the recommended partition or index as a hyperlink. This column is usually too narrow to see the entire definition. Click the hyperlink to display a dialog box containing the full definition and the Copy to Clipboard button.
Partition Recommendations
Database Name
The database containing the objects recommended to be modified.
Recommendation
The action recommended to improve performance. Possible values are Create and Drop.
Target of Recommendation
The partition function or scheme affected by the recommendation. The icon in this column reflects the recommendation to drop or add the Target of Recommendation and whether it is a partition function or scheme.
Details
A description of the Target of Recommendation. Possible values include a range for partition functions, or blank for partition schemes.
No. of Partitions
The number of partitions defined by the recommended partitioning functions. When this function is used with a scheme and then applied to a table, the data in the table is divided into that many partitions.
Definition
The definition of the Target of Recommendation. Click the column to open the SQL Script Preview dialog box, with a script for the recommended action. Index Recommendations
Database Name
The database containing the objects recommended to be modified.
Object Name
The table related to the recommendation.
Recommendation
The action recommended to improve performance. Possible values are Create and Drop.
Target of Recommendation
The index or view affected by the recommendation. The icon in this column reflects the recommendation to drop or add the Target of Recommendation.
Details
A description of the Target of Recommendation. Possible values include clustered, indexed view, or blank indicating a nonclustered index. Also indicates whether the index is unique.
Partition Scheme
The partition scheme is provided in this column if partitioning is recommended.
Size (KB)
The expected size of the new object that is being recommended. If this column is blank, click See Reports for sizes of existing objects.
Definition
The definition of the Target of Recommendation. Click the column to open the SQL Script Preview dialog box, with a script for the recommended action.
Show existing objects
Select to show all existing objects in the grid, even if no recommendations related to the objects are made by Database Engine Tuning Advisor.
See Reports for sizes of existing objects
Select to view reports that provide the size of existing objects in the recommendations grid. Actions Menu/Apply Recommendations Options
After a workload has been analyzed and recommendations have been presented, on the Actions menu, click Apply Recommendations to open the Apply Recommendations dialog box.
Apply now
Generate a script for the recommendations, and runs the script to implement the recommendations.
Schedule for later
Generate a script for the recommendations, and save the actions as a SQL Server Agent job.
Date
Specify the date you want to run the SQL Server Agent job to apply the recommendations.
Time
Specify the time you want to run the SQL Server Agent job to apply the recommendations. Reports Tab Options
The Reports tab appears after Database Engine Tuning Advisor completes analysis of a workload.
Tuning Summary
Displays a summary of the Database Engine Tuning Advisor recommendations.
Date
The date that Database Engine Tuning Advisor created the report.
Time
The time that Database Engine Tuning Advisor created the report.
Server
The server that was the target of the Database Engine Tuning Advisor workload.
Databases to tune
The database affected by Database Engine Tuning Advisor recommendations.
Workload file
Appears when the workload is a file.
Workload table
Appears when the workload is a SQL Server table.
Workload
Appears when the workload has been imported from the Query Editor in SQL Server Management Studio.
Maximum tuning time
The maximum time configured to be available for the Database Engine Tuning Advisor analysis.
Time taken for tuning
The time actually used by Database Engine Tuning Advisor to analyze the workload.
Expected percentage improvement
The percentage improvement expected with the target workload if all Database Engine Tuning Advisor recommendations are implemented.
Maximum space for recommendation (MB)
The maximum space considered for the recommendations. This value is configured before the analysis is made, using the Advanced Options button, on the Tuning Options tab.
Space used currently (MB)
The space currently used by indexes in the database analyzed.
Space used by recommendation (MB)
The approximate space expected to be used by indexes if all the Database Engine Tuning Advisor recommendations are implemented.
Number of events in workload
Number of events contained in the workload.
Number of events tuned
Number of events in the workload that were tuned. If an event cannot be tuned, it is listed in the tuning log, which is available on the Progress tab.
Number of statements tuned
Number of statements in the workload that were tuned. If a statement cannot be tuned, it is listed in the tuning log which is available on the Progress tab.
Percent SELECT statements in the tuned set
Percentage of tuned statements that are SELECT statements. Only appears if there are SELECT statements that have been tuned.
Percent UPDATE statements in the tuned set
Percentage of tuned statements that are UPDATE statements. Only appears if there are UPDATE statements that have been tuned.
Number of indexes recommended to be [created | dropped]
Recommended number of indexes to be created or dropped on the tuned database. Only appears if indexes are part of the recommendation.
Number of indexes on views recommended to be [created | dropped]
Recommended number of indexes on views to be created or dropped on the tuned database. Only appears if indexes on views are part of the recommendation.
Number of statistics recommended to be created
Recommended number of statistics to be created on the tuned database. Only appears if statistics are recommended.
Select Report
See the details of the selected report. The columns in the grid vary with each report. See Also
Start and Use the Database Engine Tuning Advisor
dta Utility
This chapter discusses the automatic SQL tuning features of Oracle Database. Automatic SQL tuning automates the manual process, which is complex, repetitive, and time-consuming.
This chapter contains the following sections:
17.1 Overview of the Automatic Tuning Optimizer
Oracle Database uses the optimizer to generate the execution plans for submitted SQL statements. The optimizer operates in the following modes:
Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke Automatic Tuning Optimizer every time a query must be hard-parsed. Automatic Tuning Optimizer is meant for complex and high-load SQL statements that have nontrivial impact on the database.
Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements that are good candidates for SQL tuning (see Chapter 6, 'Automatic Performance Diagnostics'). The automatic SQL tuning feature also automatically identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task.
The Automatic Tuning Optimizer performs the following types of tuning analysis:
17.1.1 Statistics Analysis
The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output:
The database stores this auxiliary information in an object called a SQL profile.
17.1.2 SQL Profiling
A SQL profile is a set of auxiliary information specific to a SQL statement. Conceptually, a SQL profile is to a SQL statement what statistics are to a table or index. The database can use the auxiliary information to improve execution plans.
17.1.3 Access Path Analysis
An access path is the means by which data is retrieved from a database. For example, a query using an index and a query using a full table scan use different access paths.
Indexes can tremendously enhance performance of a SQL statement by reducing the need for full scans of large tables. Effective indexing is a common tuning technique. Automatic Tuning Optimizer explores whether a new index can significantly enhance query performance. If so, then the advisor recommends index creation.
Because the Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload, it also recommends running SQL Access Advisor on the SQL statement along with a representative SQL workload. SQL Access Advisor looks at the impact of creating an index on the entire SQL workload before making recommendations. See 'Automatic SQL Tuning Features'.
17.1.4 SQL Structure Analysis
Automatic Tuning Optimizer identifies common problems with the structure of SQL statements that can lead to poor performance. These could be syntactic, semantic, or design problems. In each case, Automatic Tuning Optimizer makes relevant suggestions to restructure the statements. The suggested alternative is similar, but not equivalent, to the original statement.
For example, the optimizer may suggest replacing the
UNION operator with UNION ALL or NOT IN with NOT EXISTS . You can then determine if the advice is applicable to your situation. For example, if the schema design is such that duplicates are not possible, then the UNION ALL operator is much more efficient than the UNION operator. These changes require a good understanding of the data properties and should be implemented only after careful consideration.
17.1.5 Alternative Plan Analysis
While tuning a SQL statement, SQL Tuning Advisor searches real-time and historical performance data for alternative execution plans for the statement. If plans other than the original plan exist, then SQL Tuning Advisor reports an alternative plan finding.
SQL Tuning Advisor validates the alternative execution plans and notes any plans that are not reproducible. When reproducible alternative plans are found, you can create a SQL plan baseline to instruct the optimizer to choose these plans in the future.
Example 17-1 shows an alternative plan finding for a
SELECT statement.
Example 17-1 shows that SQL Tuning Advisor found two plans, one in the shared SQL area and one in a SQL tuning set. The plan in the shared SQL area is the same as the original plan.
SQL Tuning Advisor only recommends an alternative plan if the elapsed time of the original plan is worse than alternative plans. In this case, SQL Tuning Advisor recommends that users create a SQL plan baseline on the plan with the best performance. In Example 17-1, the alternative plan did not perform as well as the original plan, so SQL Tuning Advisor did not recommend using the alternative plan.
In Example 17-2, the alternative plans section of the SQL Tuning Advisor output includes both the original and alternative plans and summarizes their performance. The most important statistic is elapsed time. The original plan used an index, whereas the alternative plan used a full table scan, increasing elapsed time by .002 seconds.
Example 17-2 Alternative Plans Section
To adopt an alternative plan regardless of whether SQL Tuning Advisor recommends it, call
DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE . You can use this procedure to create a SQL plan baseline on any existing reproducible plan.
17.2 Managing the Automatic SQL Tuning Advisor
SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output takes the form of advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to a collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
The database can automatically tune SQL statements by identifying problematic statements and implementing recommendations using SQL Tuning Advisor during system maintenance windows. When run automatically, SQL Tuning Advisor is known as the Automatic SQL Tuning Advisor.
This section explains how to manage the Automatic SQL Tuning Advisor:
See Also:
Oracle Database Administrator's Guide for information about automated maintenance tasks
17.2.1 How Automatic SQL Tuning Works
Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week.
After automatic SQL tuning begins, the database performs the following steps:
At any time during or after the automatic SQL tuning process, you can view the results using the automatic SQL tuning report. This report describes in detail all the SQL statements that were analyzed, the recommendations generated, and the SQL profiles that were automatically implemented.
Figure 17-1 shows the steps performed by the database during automatic SQL tuning.
Figure 17-1 Automatic SQL Tuning
Description of 'Figure 17-1 Automatic SQL Tuning' 17.2.2 Enabling and Disabling Automatic SQL Tuning
Automatic SQL tuning runs as part of the automated maintenance tasks infrastructure.
To enable automatic SQL tuning, use the
ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
To disable automatic SQL tuning, use the
DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:
You can pass a specific window name using the
window_name parameter to enable or disable the task in certain maintenance windows only.
Setting the
STATISTICS_LEVEL parameter to BASIC disables automatic statistics gathering by the AWR and, as a result, also disables automatic SQL tuning.
See Also:
17.2.3 Configuring Automatic SQL Tuning
Starting with Oracle Database 11g Release 2 (11.2.0.2), you can use the
DBMS_AUTO_SQLTUNE package to configure the behavior of the automatic SQL tuning task. For previous releases, use DBMS_SQLTUNE instead.
Table 17-2 lists the configurable parameters specific to automatic SQL tuning.
Table 17-1 SET_AUTO_TUNING_TASK_PARAMETER Automatic SQL Tuning Parameters
To use the
DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS .
To configure automatic SQL tuning:
See Also:
17.2.4 Viewing Automatic SQL Tuning Reports
Starting with Oracle Database 11g Release 2 (11.2.0.2), you can use the
DBMS_AUTO_SQLTUNE .REPORT_AUTO_TUNING_TASK function to generate the automatic SQL tuning report. For previous releases, use the DBMS_SQLTUNE package instead.
The report contains information about multiple executions of the Automatic SQL Tuning task. Depending on the sections that were included in the report, you can view information about the automatic SQL tuning task in the following sections:
To view the automatic SQL tuning report using DBMS_AUTO_SQLTUNE:
See Also:
17.3 Tuning Reactively with SQL Tuning Advisor
You can invoke SQL Tuning Advisor manually for on-demand tuning of one or more SQL statements. To tune multiple statements, you must create a SQL tuning set (STS). A SQL tuning set is a database object that stores SQL statements along with their execution context. You can create a SQL tuning set using command line APIs or Enterprise Manager. See 'Managing SQL Tuning Sets'.
17.3.1 Input Sources
Input for SQL Tuning Advisor can come from several sources, including the following:
17.3.2 Tuning Options
SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. You can set the scope of a tuning task either of the following:
17.3.3 Advisor Output
After analyzing the SQL statements, SQL Tuning Advisor provides advice on optimizing the execution plan, the rationale for the proposed optimization, the estimated performance benefit, and the command to implement the advice. You choose whether to accept the recommendations to optimize the SQL statements.
17.3.4 Running SQL Tuning Advisor
The recommended interface for running SQL Tuning Advisor is Enterprise Manager. Whenever possible, run SQL Tuning Advisor using Enterprise Manager, as described in the Oracle Database 2 Day + Performance Tuning Guide. If Enterprise Manager is unavailable, then you can run SQL Tuning Advisor using procedures in the
DBMS_SQLTUNE package. To use the APIs, the user must be granted specific privileges.
Running SQL Tuning Advisor using
DBMS_SQLTUNE package is a multi-step process:
You can create a SQL tuning task for a single SQL statement. For tuning multiple statements, a SQL tuning set (STS) has to be first created. An STS is a database object that stores SQL statements along with their execution context. You can create an STS manually using command line APIs or automatically using Enterprise Manager. See 'Managing SQL Tuning Sets'.
Figure 17-2 shows the steps involved when running SQL Tuning Advisor using the
DBMS_SQLTUNE package.
Figure 17-2 SQL Tuning Advisor APIs
Description of 'Figure 17-2 SQL Tuning Advisor APIs'
This section covers the following topics:
See Also:
17.3.4.1 Creating a SQL Tuning Task
You can create tuning tasks from the text of a single SQL statement, a SQL tuning set containing multiple statements, a SQL statement selected by SQL identifier from the shared SQL area, or a SQL statement selected by SQL identifier from AWR.
For example, to use SQL Tuning Advisor to optimize a specified SQL statement text, create a tuning task with the SQL statement passed as a CLOB argument. For the following PL/SQL code, the user
hr has been granted the ADVISOR privilege, and the function is run as user hr on the hr.employees table.
In the preceding example,
100 is the value for bind variable :bnd passed as function argument of type SQL_BINDS , HR is the user under which the CREATE_TUNING_TASK function analyzes the SQL statement, the scope is set to COMPREHENSIVE which means that the advisor also performs SQL Profiling analysis, and 60 is the maximum time in seconds that the function can run. In addition, values for task name and description are provided.
The
CREATE_TUNING_TASK function returns the task name that you provided or generates a unique name. You can use the task name to specify this task when using other APIs. To view task names associated with an owner, run the following query:
17.3.4.2 Configuring a SQL Tuning Task
You can fine tune a SQL tuning task after it has been created by configuring its parameters using the
SET_TUNING_TASK_PARAMETER procedure in the DBMS_SQLTUNE package:
In the preceding example, the maximum time that the SQL tuning task can run is changed to 300 seconds.
Table 17-2 lists parameters that you can configure using the
SET_TUNING_TASK_PARAMETER procedure.
Table 17-2 SET_TUNING_TASK_PARAMETER Procedure Parameters
17.3.4.3 Executing a SQL Tuning Task
After you have created a tuning task, execute the task and start the tuning process. For example, run the following PL/SQL code:
Like any other SQL Tuning Advisor task, you can also execute the automatic tuning task
SYS_AUTO_SQL_TUNING_TASK using the EXECUTE_TUNING_TASK API. SQL Tuning Advisor performs the same analysis and actions as it would when run automatically. You can also pass an execution name to the API to name the new execution.
![]() 17.3.4.4 Checking the Status of a SQL Tuning Task
You can check the status of the task by reviewing the information in the
USER_ADVISOR_TASKS view or check execution progress of the task in the V$SESSION_LONGOPS view. For example, run the following query:
17.3.4.5 Checking the Progress of SQL Tuning Advisor
You can check the execution progress of SQL Tuning Advisor in the
V$ADVISOR_PROGRESS view. For example, run the following query:
See Also:
Oracle Database Reference to learn about the V$ADVISOR_PROGRESS view
17.3.4.6 Displaying the Results of a SQL Tuning Task
After a task has been executed, you display a report of the results with the
REPORT_TUNING_TASK function. For example:
The report contains all the findings and recommendations of SQL Tuning Advisor. For each proposed recommendation, the rationale and benefit is provided along with the SQL statements needed to implement the recommendation.
You can find additional information about tuning tasks and results in DBA views. See 'SQL Tuning Views'.
17.3.4.7 Additional Operations on a SQL Tuning Task
You can use the following APIs for managing SQL tuning tasks:
17.4 Managing SQL Tuning Sets
A SQL tuning set (STS) is a database object that includes one or more SQL statements along with their execution statistics and execution context, and could include a user priority ranking. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the shared SQL area, or customized SQL provided by the user. An STS includes:
You can filter SQL statements using the application module name and action, or any of the execution statistics. In addition, you can rank statements based on any combination of execution statistics.
You can use an STS as input to SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other user-specified input parameters. You can export SQL tuning sets from one database to another, enabling transfer of SQL workloads between databases for remote performance diagnostics and tuning. When poorly performing SQL statements occur on a production database, developers may not want investigate and tune directly on the production database. The DBA can transport the problematic SQL statements to a test database where the developers can safely analyze and tune them. To transport SQL tuning sets, use the
DBMS_SQLTUNE package.
Whenever possible, you should manage SQL tuning sets using Enterprise Manager, as described in the Oracle Database 2 Day + Performance Tuning Guide. If Enterprise Manager is unavailable, then you can manage SQL tuning sets using the
DBMS_SQLTUNE package procedures.
Typically, you use STS operations in the following sequence:
To use the APIs, you need the
ADMINISTER SQL TUNING SET system privilege to manage SQL tuning sets that you own, or the ADMINISTER ANY SQL TUNING SET system privilege to manage any SQL tuning sets. Auto tune 7 audioz software.
Figure 17-3 shows the steps involved when using SQL tuning sets APIs.
Figure 17-3 SQL Tuning Sets APIs
Description of 'Figure 17-3 SQL Tuning Sets APIs'
This section covers the following topics:
See Also:
17.4.1 Creating a SQL Tuning Set
The
CREATE_SQLSET procedure creates an empty STS object in the database. For example, the following procedure creates an STS object that you could use to tune I/O-intensive SQL statements during a specific period:
In the preceding example,
my_sql_tuning_set is the name of the STS in the database. 'I/O intensive workload' is the description assigned to the STS.
17.4.2 Loading a SQL Tuning Set
The
LOAD_SQLSET procedure populates the STS with selected SQL statements. The standard sources for populating an STS are the workload repository, another STS, or the shared SQL area. For both the workload repository and STS, predefined table functions can select columns from the source to populate a new STS.
In the following example, procedure calls load
my_sql_tuning_set from an AWR baseline called peak baseline . The data has been filtered to select only the top 30 SQL statements ordered by elapsed time. First a ref cursor is opened to select from the specified baseline. Next the statements and their statistics are loaded from the baseline into the STS.
17.4.3 Displaying the Contents of a SQL Tuning Set
The
SELECT_SQLSET table function reads the contents of the STS. After an STS has been created and populated, you can browse the SQL in the STS using different filtering criteria. The SELECT_SQLSET procedure is provided for this purpose.
In the following example, the SQL statements in the STS are displayed for statements with a disk-reads to buffer-gets ratio greater than or equal to 75%.
Additional details of the SQL tuning sets that have been created and loaded can also be displayed with DBA views, such as
DBA_SQLSET , DBA_SQLSET_STATEMENTS , and DBA_SQLSET_BINDS .
17.4.4 Modifying a SQL Tuning Set
You can update and delete SQL statements from an STS based on a search condition. In the following example, the
DELETE_SQLSET procedure deletes SQL statements from my_sql_tuning_set that have been executed less than fifty times.
17.4.5 Transporting a SQL Tuning Set
You can transport SQL tuning sets. This operation involves exporting the STS from one database to a staging table, and then importing the STS from the staging table into another database.
You can transport a SQL tuning set to any database created in Oracle Database 10g (Release 2) or later. This technique is useful when using SQL Performance Analyzer to tune regressions on a test database. For example, you can transport an STS in the following scenario:
To transport a SQL tuning set:
17.4.6 Dropping a SQL Tuning Set
The
DROP_SQLSET procedure drops an STS that is no longer needed. For example:
17.4.7 Additional Operations on SQL Tuning Sets
You can use the following APIs to manage an STS:
17.5 Managing SQL Profiles
A SQL profile is a set of auxiliary information specific to a SQL statement.
This section contains the following topics:
See Also:
Oracle Database 2 Day + Performance Tuning Guide to learn how to manage SQL profiles using Enterprise Manager
17.5.1 Overview of SQL Profiles
A SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.
The SQL profile does not contain information about individual execution plans. Rather, the optimizer has the following sources of information when choosing plans:
If the environment or SQL profile change, then the optimizer can create a new plan.
You can use SQL profiles with or without SQL plan management. If you use SQL plan management, then the plan chosen by the optimizer must be an enabled plan baseline. If the statement has multiple plans in the baseline, then the profile remains useful because it enables the optimizer to chose the lowest-cost plan in the baseline.
Figure 17-4 illustrates the relationship between a SQL statement and the SQL profile for this statement. The optimizer uses the profile and the environment to generate a query plan. In this example, the plan is in the SQL plan baseline for the statement.
Figure 17-4 SQL Profile
Description of 'Figure 17-4 SQL Profile'
SQL profiles provide the following benefits:
17.5.1.1 SQL Profile RecommendationsDbms_auto_task_admin.disable Sql Tuning Advisor How To Use
During SQL tuning, you select a statement for automatic tuning and run SQL Tuning Advisor. The database can profile the following types of statement:
SQL Tuning Advisor invokes Automatic Tuning Optimizer to generate recommendations. Recommendations to accept SQL profiles occur in a finding.
Example 17-3 shows that the database found a better plan for a
SELECT statement that uses several expensive joins. The recommendation is to run DBMS_SQLTUNE.ACCEPT_SQL_PROFILE to accept the profile, which should enable the statement to run 98.53% faster.
Sometimes SQL Tuning Advisor may recommend accepting a profile that uses the Automatic Degree of Parallelism (Auto DOP) feature. A parallel query profile is only recommended when the original plan is serial and when parallel execution can significantly reduce the response time for a long-running query. When it recommends a profile that uses Auto DOP, SQL Tuning Advisor gives details about the performance overhead of using parallel execution for the SQL statement in the report.
For parallel execution recommendations, SQL Tuning Advisor may provide two SQL profile recommendations, one using serial execution and one using parallel. In this case, the parallel profile is identical to the standard profile except for the directive to run in parallel.
Example 17-4 shows a parallel query recommendation. In this example, a degree of parallelism of 7 improves response time significantly at the cost of increasing resource consumption by almost 25%. You must decide whether the reduction in database throughput is worth the increase in response time.
Example 17-4 Parallel Query Recommendation
17.5.1.2 SQL Profile Creation
When you accept a profile, the database creates the profile and stores it persistently in the data dictionary. If a user issues a statement for which a profile has been built, then the query optimizer (in normal mode) uses both the environment and the SQL profile to build a well-tuned plan.
If the database uses SQL plan management, and if a SQL plan baseline exists for the SQL statement, then the database adds a new plan to the baseline when a SQL profile is created. Otherwise, the database does not add a new plan baseline.
No strict relationship exists between the SQL profile and the plan baseline. When hard parsing, the optimizer uses the SQL profile to select the best plan baseline from the available plans. In some conditions, the SQL profile may cause the optimizer to select different plan baselines.
17.5.1.3 SQL Profile APIs
While SQL profiles are usually handled by Enterprise Manager as part of Automatic SQL tuning, you can manage SQL profiles with the
DBMS_SQLTUNE package. To use the APIs, you must have the ADMINISTER SQL MANAGEMENT OBJECT privilege.
Table 17-3 shows the main procedures and functions for managing SQL profiles.
Table 17-3 DBMS_SQLTUNE APIs for SQL Profiles
Figure 17-5 shows the possible actions when using SQL profile APIs.
Figure 17-5 SQL Profile APIs
Disable Sql Tuning Advisor 11gDescription of 'Figure 17-5 SQL Profile APIs'
As tables grow or indexes are created or dropped, the plan for a profile can change. The profile continues to be relevant even if the data distribution or access path of the corresponding statement changes. In general, you do not need to refresh SQL profiles.
Over a long period, profile content can become outdated. In this case, the performance of the corresponding SQL statement may degrade. The poorly performing statement may appear as high-load or top SQL. In this situation, the Automatic SQL Tuning task again captures the statement as high-load SQL. You can create a new profile for the statement.
Dbms_auto_task_admin.disable Sql Tuning Advisor From Command Line
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE package
Sql Tuning Advisor Oracle17.5.2 Accepting a SQL Profile
You can use the
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE procedure or function to accept a SQL profile recommended by SQL Tuning Advisor. This procedure creates and stores a SQL profile in the database.
As a rule of thumb, accept a SQL profile recommended by SQL Tuning Advisor. If both an index and a SQL profile are recommended, then either use both or use the SQL profile only. If you create an index, then the optimizer may need the profile to pick the new index.
In some situations, SQL Tuning Advisor may find an improved serial plan in addition to an even better parallel plan. In this case, the advisor recommends both a standard and a parallel SQL profile, enabling you to choose between the best serial and best parallel plan for the statement. Accept a parallel plan only if the increase in response time is worth the decrease in throughput (see Example 17-4).
To accept a SQL profile:
17.5.3 Altering a SQL Profile
You can alter attributes of an existing SQL profile with the
ALTER_SQL_PROFILE procedure. Modifiable attributes are STATUS , NAME , DESCRIPTION , and CATEGORY .
The
CATEGORY attribute determines which sessions can apply a profile. https://episkyey.weebly.com/blog/cooking-academy-world-cuisine-free-download. You can view the CATEGORY attribute by querying DBA_SQL_PROFILES.CATEGORY . By default, all profiles are in the DEFAULT category, which means that all sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEFAULT can use the profile.
By altering the category of a SQL profile, you can determine which sessions are affected by profile creation. For example, by setting the category to
DEV , only sessions in which the SQLTUNE_CATEGORY initialization parameter is set to DEV can use the profile. Other sessions do not have access to the SQL profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a profile in a restricted environment before making it available to other sessions.
To alter a SQL profile:
See Also:
Oracle Database Reference
Dbms_auto_task_admin.disable Sql Tuning Advisor In Oracle Youtubeto learn about theSQLTUNE_CATEGORY initialization parameter
Oracle Disable Sql Tuning Advisor17.5.4 Dropping a SQL Profile
You can drop a SQL profile with the
DROP_SQL_PROFILE procedure. You can specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE is accepted
Disable Sql Tuning Advisor
To drop a SQL profile:
17.5.5 Transporting a SQL Profile
You can transport SQL profiles. This operation involves exporting the SQL profile from the
SYS schema in one database to a staging table, and then importing the SQL profile from the staging table into another database. You can transport a SQL profile to any Oracle database created in the same release or later.
To transport a SQL profile:
17.6 SQL Tuning Views
This section summarizes views that shows information gathered for tuning the SQL statements. You need DBA privileges to access these views.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |