Engineering, Egypt Engineers, EgyEng, IT, Jobs, Certification, Programming,مهندسين مصر, Network, Microsoft, Cisco, VMware, HyperV,مهندسى مصر. Welcome to the Database Engine Tuning Advisor tutorial. Database Engine Tuning Advisor examines how queries are processed in the databases you specify, and then recommends how you can improve query processing performance by modifying database structures such as. Sep 11, 2011 The SQL Tuning Advisor configuration has default settings in your database instance. Which recommendation is automatically implemented after the SQL Tuning Advisor is run as part of the automatic maintenance task? Statistics recommendations. SQL Profile recommendations. Index-related recommendations D. Restructuring of SQL recommendations. In 1Z0-053 1Z0-053, is run as part of the automatic maintenance task, recommendation auto implemented by sql tuning advisor, restructuring of sql recommendations, SQL Profile recommendations aiotestking, SQL Tuning Advisor configuration has default settings, SQL Tuning Advisorrestructuring of SQL recommendations, the sql tuning advisor. The 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.
-->This topic describes how to start and use Database Engine Tuning Advisor in SQL Server 2019 (15.x). For information about how to view and work with the results after you tune a database, see View and Work with the Output from the Database Engine Tuning Advisor.
Initialize the Database Engine Tuning Advisor
On first use, a user who is member of the sysadmin fixed server role must initialize the Database Engine Tuning Advisor. This is because several system tables must be created in the msdb database to support tuning operations. Initialization also enables users that are members of the db_owner fixed database role to tune workloads on tables in databases that they own.
A user that has system administrator permissions must perform either of the following actions:
- Use the Database Engine Tuning Advisor graphical user interface to connect to an instance of SQL Server 2019 (15.x). For more information, see Start the Database Engine Tuning Advisor later in this topic.
- Use the dta utility to tune the first workload. For more information, see Use the dta Utility later in this topic.
Start the Database Engine Tuning Advisor
You can start the Database Engine Tuning Advisor graphical user interface (GUI) in several different ways to support database tuning in a variety of scenarios. The different ways to start Database Engine Tuning Advisor include: from the Start menu, from the Tools menu in SQL Server Management Studio, from the Query Editor in SQL Server Management Studio, and from the Tools menu in SQL Server Profiler. When you first start Database Engine Tuning Advisor, the application displays a Connect to Server dialog box where you can specify the SQL Server instance to which you want to connect.
Warning
Do not start Database Engine Tuning Advisor when SQL Server is running in single-user mode. If you attempt to start it while the server is in single-user mode, an error will be returned and Database Engine Tuning Advisor will not start. For more information about single-user mode, see Start SQL Server in Single-User Mode.
To start Database Engine Tuning Advisor from the Windows Start menu
- On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Performance Tools, and then click Database Engine Tuning Advisor.
To start the Database Engine Tuning Advisor in SQL Server Management Studio
- On the SQL Server Management Studio Tools menu, click Database Engine Tuning Advisor.
To start the Database Engine Tuning Advisor from the SQL Server Management Studio Query Editor
- Open a Transact-SQL script file in SQL Server Management Studio. For more information, see Query and Text Editors (SQL Server Management Studio).
- Select a query in the Transact-SQL script, or select the entire script, right-click the selection, and choose Analyze Query in Database Engine Tuning Advisor. The Database Engine Tuning Advisor GUI opens and imports the script as an XML file workload. You can specify a session name and tuning options to tune the selected Transact-SQL queries as your workload.
To start the Database Engine Tuning Advisor in SQL Server Profiler
- On the SQL Server Profiler Tools menu, click Database Engine Tuning Advisor.
Create a Workload
A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor analyzes these workloads to recommend indexes or partitioning strategies that will improve your server's query performance.
You can create a workload by using one of the following methods.
- Use the Query Store as a workload. By doing this, you can avoid having to manually create a workload. For more information, see Tuning Database Using Workload From Query Store.
Applies to: SQL Server 2016 (13.x) and later. - Use the plan cache as a workload. By doing this, you can avoid having to manually create a workload. For more information, see Tune a Database later in this topic.
- Use the Query Editor in SQL Server Management Studio or your favorite text editor to manually create Transact-SQL script workloads.
- Use SQL Server Profiler to create trace file or trace table workloadsNoteWhen using a trace table as a workload, that table must exist on the same server where Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server where Database Engine Tuning Advisor is tuning.
- Workloads can also be embedded in an XML input file, where you can also specify a weight for each event. For more information about specifying embedded workloads, see Create an XML Input File later in this topic.
To create Transact-SQL script workloads
- Launch the Query Editor in SQL Server Management Studio. For more information, see Query and Text Editors (SQL Server Management Studio).
- Type your Transact-SQL script into the Query Editor. This script should contain a set of Transact-SQL statements that execute against the database or databases that you want to tune.
- Save the file with a .sql extension. The Database Engine Tuning Advisor GUI and the command-line dta utility can use this Transact-SQL script as a workload.
To create trace file and trace table workloads
- Launch SQL Server Profiler by using one of the following methods:
- On the Start menu, point to All Programs, Microsoft SQL Server, Performance Tools, and then click SQL Server Profiler.
- In SQL Server Management Studio, click the Tools menu, and then click SQL Server Profiler.
- Create a trace file or table as described in the following procedures that uses the SQL Server Profiler Tuning template:
- Database Engine Tuning Advisor assumes that the workload trace file is a rollover file. For more information about rollover files, see Limit Trace File and Table Sizes.
- Make sure that tracing has stopped before using a trace table as a workload.
We recommend that you use the SQL Server Profiler Tuning template for capturing workloads for Database Engine Tuning Advisor.
If you want to use your own template, ensure that the following trace events are captured:
- RPC:Completed
- SQL:BatchCompleted
- SP:StmtCompleted
You can also use the Starting versions of these trace events. For example, SQL:BatchStarting. However, the Completed versions of these trace events include the Duration column, which allows Database Engine Tuning Advisor to more effectively tune the workload. Database Engine Tuning Advisor does not tune other types of trace events. For more information about these trace events, see Stored Procedures Event Category and TSQL Event Category. For information about using the SQL Trace stored procedures to create a trace file workload, see Create a Trace (Transact-SQL).
Trace File or Trace Table Workloads That Contain the LoginName Data Column
Database Engine Tuning Advisor submits Showplan requests as part of the tuning process. When a trace table or file that contains the LoginName data column is consumed as a workload, Database Engine Tuning Advisor impersonates the user specified in LoginName. If this user has not been granted the SHOWPLAN permission, which enables the user to execute and produce Showplans for the statements contained in the trace, Database Engine Tuning Advisor will not tune those statements.
To avoid granting the SHOWPLAN permission to each user specified in the LoginName column of the trace
- Tune the trace file or table workload. For more information, see Tune a Database later in this topic.
- Check the tuning log for statements that were not tuned due to inadequate permissions. For more information, see View and Work with the Output from the Database Engine Tuning Advisor.
- Create a new workload by deleting the LoginName column from the events that were not tuned, and then save only the untuned events in a new trace file or table. For more information about deleting data columns from a trace, see Specify Events and Data Columns for a Trace File (SQL Server Profiler) or Modify an Existing Trace (Transact-SQL).
- Resubmit the new workload without the LoginName column to Database Engine Tuning Advisor.
Database Engine Tuning Advisor will tune the new workload because login information is not specified in the trace. If the LoginName does not exist for a statement, Database Engine Tuning Advisor tunes that statement by impersonating the user who started the tuning session (a member of either the sysadmin fixed server role or the db_owner fixed database role).
Tune a Database
To tune a database, you can use the Database Engine Tuning Advisor GUI or the dta utility.
Note
Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload.
Use the Database Engine Tuning Advisor Graphical User Interface
On the Database Engine Tuning Advisor GUI, you can tune a database by using the plan cache, workload files, or workload tables. You can use the Database Engine Tuning Advisor GUI to easily view the results of your current tuning session and results of previous tuning sessions. For information about user interface options, see User Interface Descriptions later in this topic. For more information about working with the output after you tune a database, see View and Work with the Output from the Database Engine Tuning Advisor.
To tune a database by using the Query Store
See Tuning Database Using Workload from Query Store for more information.
To tune a database by using the plan cache
- Launch Database Engine Tuning Advisor, and log into an instance of SQL Server. For more information, see Start the Database Engine Tuning Advisor earlier in this topic.
- On the General tab, type a name in Session name to create a new tuning session. You must configure the fields in the General tab before starting a tuning session. It is not necessary to modify the settings of the Tuning Options tab before starting a tuning session.
- Select Plan Cache as the workload option. Database Engine Tuning Advisor selects the top 1,000 events from the plan cache to use for analysis.
- Select the database or databases that you want to tune, and optionally from Selected Tables, choose one or more tables from each database. To include cache entries for all databases, from Tuning Options, click Advanced Options and then check Include plan cache events from all databases.
- Check Save tuning log to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.You can view the tuning log after analysis by opening the session and selecting the Progress tab.
- Click the Tuning Options tab and select from the options listed there.
- Click Start Analysis.If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:
- Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
- Stop Analysis stops the tuning session without generating any recommendations.
Note
Pausing Database Engine Tuning Advisor is not supported. If you click the Start Analysis toolbar button after clicking either the Stop Analysis or Stop Analysis (With Recommendations) toolbar buttons, Database Engine Tuning Advisor starts a new tuning session.
To tune a database using a workload file or table as input
- Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
- Create a workload. For more information, see Create a Workload earlier in this topic.
- Launch Database Engine Tuning Advisor, and log into an instance of Microsoft SQL Server. For more information, see Start the Database Engine Tuning Advisor earlier in this topic.
- On the General tab, type a name in Session name to create a new tuning session.
- Choose either a Workload File or Table and type either the path to the file, or the name of the table in the adjacent text box.The format for specifying a table isTo search for a workload file or table, click Browse. Database Engine Tuning Advisor assumes that workload files are rollover files. For more information about rollover files, see Limit Trace File and Table Sizes.When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server that Database Engine Tuning Advisor is tuning before using it as your workload.
- Select the databases and tables against which you wish to run the workload that you selected in step 5. To select the tables, click the Selected Tables arrow.
- Check Save tuning log to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.You can view the tuning log after analysis by opening the session and selecting the Progress tab.
- Click the Tuning Options tab and select from the options listed there.
- Click the Start Analysis button in the toolbar.If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:
- Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
- Stop Analysis stops the tuning session without generating any recommendations.
Note
Pausing Database Engine Tuning Advisor is not supported. If you click the Start Analysis toolbar button after clicking either the Stop Analysis or Stop Analysis (With Recommendations) toolbar buttons, Database Engine Tuning Advisor starts a new tuning session.
Use the dta Utility
The dta utility provides a command prompt executable file that you can use to tune databases. It enables you to use Database Engine Tuning Advisor functionality in batch files and scripts. The dta utility takes plan cache entries, trace files, trace tables, and Transact-SQL scripts as workloads. It also takes XML input that conforms to the Database Engine Tuning Advisor XML schema, which is available at this Microsoft Web site.
Consider the following before you begin tuning a workload with the dta utility:
- When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning.
- Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload.
- If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to stop the tuning session and generate recommendations based on the analysis dta has completed up to this point. You will be prompted to decide whether you want to generate recommendations or not. Press CTRL+C again to stop the tuning session without generating recommendations.
For more information about dta utility syntax and examples, see dta Utility.
To tune a database by using the plan cache
- Specify the -ip option. The top 1,000 plan cache events for the selected databases are analyzed.From a command prompt, enter the following:
- To modify the number of events to use for analysis, specify the -n option. The following example increases the number of cache entries to 2,000.
- To analyze events for all databases in the instance, specify the -ipf option.
To tune a database by using a workload and dta utility default settings
- Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
- Create a workload. For more information, see Create a Workload earlier in this topic.
- From a command prompt, enter the following:where
-E
specifies that your tuning session uses a trusted connection (instead of a login ID and password),-D
specifies the name of the database you want to tune. By default, the utility connects to the default instance of SQL Server on the local computer. (Use the-S
option to specify a remote database as shown in the following procedure, or to specify a named instance.) The-if
option specifies the name and path to a workload file (which can be a Transact-SQL script or a trace file), and-s
specifies a name for your tuning session.The four options shown here (database name, workload, connection type, and session name) are mandatory.
To tune a remote database or a named instance for a specific duration
Sql Tuning Advisor Oracle
- Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
- Create a workload. For more information, see Create a Workload earlier in this topic.
- From a command prompt, enter the following:where
-S
specifies a remote server name and instance (or a named instance on the local server) and-D
specifies the name of the database you want to tune. The-it
option specifies the name of the workload table,-U
and-P
specify the login ID and password to the remote database,-s
specifies the tuning session name, and-A
specifies the tuning session duration in minutes. By default, the dta utility uses an 8-hour tuning duration. If you would like Database Engine Tuning Advisor to tune a workload for an unlimited amount of time, specify 0 (zero) with the-A
option.
To tune a database using an XML input file
- Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
- Create a workload. For more information, see Create a Workload earlier in this topic.
- Create an XML input file. For more information, see Create XML Input Files later in this topic.
- From a command prompt, enter the following:where
-E
specifies a trusted connection,-S
specifies a remote server and instance, or a named instance on the local server,-s
specifies a tuning session name, and-ix
specifies the XML input file to use for the tuning session. - After the utility finishes tuning the workload, you can view the results of tuning sessions with the Database Engine Tuning Advisor GUI. As an alternative, you can also specify that the tuning recommendations be written to an XML file with the -ox option. For more information, see dta Utility.
Create an XML Input File
If you are an experienced XML developer, you can create XML-formatted files that Database Engine Tuning Advisor can use to tune workloads. To create these XML files, use your favorite XML tools to edit a sample file or to generate an instance from the Database Engine Tuning Advisor XML schema.
The Database Engine Tuning Advisor XML schema is available in your Microsoft SQL Server installation in the following location:
C:Program FilesMicrosoft SQL Server100ToolsBinnschemassqlserver200407dtadtaschema.xsd
The Database Engine Tuning Advisor XML schema is also available online at this Microsoft Web site.
This URL takes you to a page where many SQL Server XML schemas are available. Scroll down the page until you reach the row for Database Engine Tuning Advisor.
To create an XML input file to tune workloads
- Create a workload. You can use a trace file or table by using the tuning template in SQL Server Profiler, or create a Transact-SQL script that reproduces a representative workload for SQL Server. For more information, see Create a Workload earlier in this topic.
- Create an XML input file by one of the following methods:
- Copy and paste one of the XML Input File Samples (DTA) into your favorite XML editor. Change the values to specify the appropriate arguments for your SQL Server installation, and save the XML file.
- Using your favorite XML tool, generate an instance from the Database Engine Tuning Advisor XML schema.
- After creating the XML input file, use it as input to the dta command-line utility to tune the workload. For information about using XML input files with this utility, see the section Use the dta Utililty earlier in this topic.
Note
If you want to use an inline workload, which is a workload that is specified directly in the XML input file, use the sample XML Input File Sample with Inline Workload (DTA).
User Interface Descriptions
Tools Menu/Options Page
Use this dialog box to specify general configuration parameters for the Database Engine Tuning Advisor.
On startup
Specify what Database Engine Tuning Advisor should do when it is started: open without a database connection, show a New Connection dialog box, show a new session, or load the last loaded session.
Specify what Database Engine Tuning Advisor should do when it is started: open without a database connection, show a New Connection dialog box, show a new session, or load the last loaded session.
Change font
Specify the display font used by Database Engine Tuning Advisor tables.
Specify the display font used by Database Engine Tuning Advisor tables.
Number of items in most recently used lists
Specify the number of sessions or files to display under Recent Sessions or Recent Files in the File menu.
Specify the number of sessions or files to display under Recent Sessions or Recent Files in the File menu.
Remember my last tuning options
Retain tuning options between sessions. Selected by default. Clear this check box to always start with the Database Engine Tuning Advisor defaults.
Retain tuning options between sessions. Selected by default. Clear this check box to always start with the Database Engine Tuning Advisor defaults.
Ask before permanently deleting sessions
Display a confirmation dialog box before deleting sessions.
Display a confirmation dialog box before deleting sessions.
Ask before stopping session analysis
Display a confirmation dialog box before stopping analysis of a workload.
Display a confirmation dialog box before stopping analysis of a workload.
General Tab Options
You must configure the fields in the General tab before starting a tuning session. You do not have to modify the settings of the Tuning Options tab before starting a tuning session.
Session name
Specify a name for the session. The session name associates a name with a tuning session. You can refer to this name to review the tuning session later.
Specify a name for the session. The session name associates a name with a tuning session. You can refer to this name to review the tuning session later.
File
Specify a .sql script or trace file for a workload. Specify the path and filename in the associated text box. Database Engine Tuning Advisor assumes that the workload trace file is a rollover file. For more information about rollover files, see Limit Trace File and Table Sizes.
Specify a .sql script or trace file for a workload. Specify the path and filename in the associated text box. Database Engine Tuning Advisor assumes that the workload trace file is a rollover file. For more information about rollover files, see Limit Trace File and Table Sizes.
Table
Specify a trace table for a workload. Specify the fully qualified name of the trace table in the associated text box as follows:
Specify a trace table for a workload. Specify the fully qualified name of the trace table in the associated text box as follows:
- Make sure that tracing has stopped before using a trace table as a workload.
- The trace table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning.
Plan Cache
Specify the plan cache as a workload. By doing this, you can avoid having to manually create a workload. Database Engine Tuning Advisor selects the top 1,000 events to use for analysis.
Specify the plan cache as a workload. By doing this, you can avoid having to manually create a workload. Database Engine Tuning Advisor selects the top 1,000 events to use for analysis.
Xml
This does not appear unless you import a workload query from SQL Server Management Studio.
This does not appear unless you import a workload query from SQL Server Management Studio.
To import a workload query from SQL Server Management Studio:
- Type a query into Query Editor and highlight it.
- Right-click the highlighted query and click Analyze Query in Database Engine Tuning Advisor.
Browse for a workload [file or table]
When File or Table is selected as the workload source, use this browse button to select the target.
When File or Table is selected as the workload source, use this browse button to select the target.
Preview the XML workload
View an XML-formatted workload that has been imported from SQL Server Management Studio.
View an XML-formatted workload that has been imported from SQL Server Management Studio.
Database for workload analysis
Specify the first database to which Database Engine Tuning Advisor connects when tuning a workload. After tuning begins, Database Engine Tuning Advisor connects to the databases specified by the
Specify the first database to which Database Engine Tuning Advisor connects when tuning a workload. After tuning begins, Database Engine Tuning Advisor connects to the databases specified by the
USE DATABASE
statements contained in the workload.Select databases and tables to tune
Specify the databases and tables to be tuned. To specify all of the databases, select the check box in the Name column heading. To specify certain databases, select the check box next to the database name. By default, all of the tables for selected databases are automatically included in the tuning session. To exclude tables, click the arrow in the Selected Tables column, and then clear the check boxes next to the tables that you do not want to tune.
Specify the databases and tables to be tuned. To specify all of the databases, select the check box in the Name column heading. To specify certain databases, select the check box next to the database name. By default, all of the tables for selected databases are automatically included in the tuning session. To exclude tables, click the arrow in the Selected Tables column, and then clear the check boxes next to the tables that you do not want to tune.
Selected Tables down arrow
Expand the tables list to allow selecting individual tables for tuning.
Expand the tables list to allow selecting individual tables for tuning.
Save tuning log
Create a log and record errors during the session.
Create a log and record errors during the session.
Note
Database Engine Tuning Advisor does not automatically update the rows information for the tables displayed on the General tab. Instead it relies upon the metadata in the database. If you suspect that the rows information is outdated, run the DBCC UPDATEUSAGE command for the relevant objects.
Tuning Tab Options
Use the Tuning Options tab to modify default settings of general tuning options. You do not have to modify the settings of the Tuning Options tab before starting a tuning session.
![Sql tuning advisor Sql tuning advisor](/uploads/1/2/6/1/126191285/533441585.jpg)
Limit tuning time
Limits the time for the current tuning session. Providing more time for turning improves the quality of the recommendations. To ensure the best recommendations, do not select this option.
Limits the time for the current tuning session. Providing more time for turning improves the quality of the recommendations. To ensure the best recommendations, do not select this option.
Note
Database Engine Tuning Advisor consumes system resources during analysis. Use Limit tuning time to stop tuning before periods of anticipated heavy workload on the server being tuned.
Advanced Options
Use the Advanced Tuning Options dialog box to configure the maximum space, maximum key columns, and online index recommendations.
Use the Advanced Tuning Options dialog box to configure the maximum space, maximum key columns, and online index recommendations.
Define max. space for recommendations (MB)
Type the maximum amount of space to be used by physical design structures recommended by Database Engine Tuning Advisor.
Type the maximum amount of space to be used by physical design structures recommended by Database Engine Tuning Advisor.
If no value is entered here, Database Engine Tuning Advisor assumes the smaller of the following space limits:
- Three times the current raw data size, which includes the total size of heaps and clustered indexes on tables in the database.
- The free space on all attached disk drives plus the raw data size.
Include plan cache events from all databases
Specify that plan cache events from all databases are analyzed.
Specify that plan cache events from all databases are analyzed.
Max. columns per index
Specify the maximum number of columns to include in any index. The default is 1023.
Specify the maximum number of columns to include in any index. The default is 1023.
All recommendations are offline
Generate the best recommendations possible, but do not recommend that any physical design structures be created online.
Generate the best recommendations possible, but do not recommend that any physical design structures be created online.
Generate online recommendations where possible
When creating Transact-SQL statements to implement the recommendations, choose methods that can be implemented with the server online, even if a faster offline method is available.
When creating Transact-SQL statements to implement the recommendations, choose methods that can be implemented with the server online, even if a faster offline method is available.
Generate only online recommendations
Only make recommendations that allow the server to stay online.
Only make recommendations that allow the server to stay online.
Stop at
Provide the date and time when Database Engine Tuning Advisor should stop.
Provide the date and time when Database Engine Tuning Advisor should stop.
Indexes and indexed views
Check this box to include recommendations for adding clustered indexes, nonclustered indexes, and indexed views.
Check this box to include recommendations for adding clustered indexes, nonclustered indexes, and indexed views.
Indexed views
Only include recommendations for adding indexed views. Clustered and nonclustered indexes will not be recommended.
Only include recommendations for adding indexed views. Clustered and nonclustered indexes will not be recommended.
Include filtered indexes
Include recommendations for adding filtered indexes. This option is available if you select one of these physical design structures: Indexes and indexed views, Indexes, or Nonclustered indexes.
Include recommendations for adding filtered indexes. This option is available if you select one of these physical design structures: Indexes and indexed views, Indexes, or Nonclustered indexes.
Indexes
Only include recommendations for adding clustered and nonclustered indexes. Indexed views will not be recommended.
Only include recommendations for adding clustered and nonclustered indexes. Indexed views will not be recommended.
Nonclustered indexes
Include recommendations for only nonclustered indexes. Clustered indexes and indexed views will not be recommended.
Include recommendations for only nonclustered indexes. Clustered indexes and indexed views will not be recommended.
Evaluate utilization of existing PDS only
Evaluate the effectiveness of the current indexes but do not recommend additional indexes or indexed views.
Evaluate the effectiveness of the current indexes but do not recommend additional indexes or indexed views.
No partitioning
Do not recommend partitioning.
Do not recommend partitioning.
Full partitioning
Include recommendations for partitioning.
Include recommendations for partitioning.
Aligned partitioning
New recommended partitions will be aligned to make partitions easy to maintain.
New recommended partitions will be aligned to make partitions easy to maintain.
Do not keep any existing PDS
Recommend dropping unnecessary existing indexes, views, and partitioning. If an existing physical design structure (PDS) is useful to the workload, Database Engine Tuning Advisor does not recommend dropping it.
Recommend dropping unnecessary existing indexes, views, and partitioning. If an existing physical design structure (PDS) is useful to the workload, Database Engine Tuning Advisor does not recommend dropping it.
Sql Tuning Advisor In Oracle
Keep indexes only
Keep all existing indexes but recommend dropping unnecessary indexed views, and partitioning.
Keep all existing indexes but recommend dropping unnecessary indexed views, and partitioning.
Keep all existing PDS
Keep all existing indexes, indexed views, and partitioning.
Keep all existing indexes, indexed views, and partitioning.
Keep clustered indexes only
Keep all existing clustered indexes but recommend dropping unnecessary indexed views, partitions, and nonclustered indexes.
Keep all existing clustered indexes but recommend dropping unnecessary indexed views, partitions, and nonclustered indexes.
Keep aligned partitioning
Keep partitioning structures that are currently aligned, but recommend dropping unnecessary indexed views, indexes, and non-aligned partitioning. Any additional partitioning recommended will align with the current partitioning scheme.
Keep partitioning structures that are currently aligned, but recommend dropping unnecessary indexed views, indexes, and non-aligned partitioning. Any additional partitioning recommended will align with the current partitioning scheme.
Progress Tab Options
The Progress tab of Database Engine Tuning Advisor appears after Database Engine Tuning Advisor begins analyzing a workload.
If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:
- Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
- Stop Analysis stops the tuning session without generating any recommendations.
Tuning Progress
Indicates the current status of the progress. Contains the number of actions performed, and the number of error, success, and warning messages received.
Indicates the current status of the progress. Contains the number of actions performed, and the number of error, success, and warning messages received.
Details
Contains an icon indicating status.
Contains an icon indicating status.
Action
Displays the steps being performed.
Displays the steps being performed.
Status
Displays the status of the action step.
Displays the status of the action step.
Message
Contains any messages returned by the action steps.
Contains any messages returned by the action steps.
Tuning Log
Contains information regarding this tuning session. To print this log, right-click the log, and then click Print.
Contains information regarding this tuning session. To print this log, right-click the log, and then click Print.
See Also
View and Work with the Output from the Database Engine Tuning Advisor
dta Utility
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:
- See Also:Oracle Database 2 Day + Performance Tuning Guide for information about using the automatic SQL tuning features of Oracle Database with Oracle Enterprise Manager
17.1 Automatic Tuning Optimizer
When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query optimizer operates in two modes: a normal mode and a tuning mode.
In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.
In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.
Under tuning mode, the optimizer can take several minutes to tune a single statement. It is both time and resource intensive to invoke the Automatic Tuning Optimizer every time a query has to be hard-parsed. The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning. See Chapter 6, 'Automatic Performance Diagnostics'. The automatic SQL tuning feature of Oracle Database also automatically identifies problematic SQL statements and implements tuning recommendations during system maintenance windows as an automated maintenance task.
The Automatic Tuning Optimizer performs four types of tuning analysis:
17.1.1 Statistics Analysis
The query optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, 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:
- Recommendations to gather relevant statistics for objects with stale or no statistics.Because optimizer statistics are automatically collected and refreshed, this problem may be encountered only when automatic optimizer statistics collection has been turned off. See 'Automatic Optimizer Statistics Collection'.
- Auxiliary information in the form of statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics.
This auxiliary information is stored in an object called a SQL Profile.
17.1.2 SQL Profiling
The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.
Automatic SQL tuning deals with this problem with its SQL profiling capability. The Automatic Tuning Optimizer creates a profile of the SQL statement called a SQL Profile, consisting of auxiliary statistics specific to that statement. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates.
During SQL Profiling, the Automatic Tuning Optimizer also uses execution history information of the SQL statement to appropriately set optimizer parameter settings, such as changing the
OPTIMIZER_MODE
initialization parameter setting from ALL_ROWS
to FIRST_ROWS
for that SQL statement.The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code.
The scope of a SQL Profile can be controlled by the
CATEGORY
profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY
attribute for a SQL Profile in CATEGORY
column of the DBA_SQL_PROFILES
view. By default, all profiles are created in the DEFAULT
category. This means that all user sessions where 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 the creation of a profile. For example, by setting the category of a SQL Profile to
DEV
, only those users sessions where the SQLTUNE_CATEGORY
initialization parameter is set to DEV
can use the profile. All 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 SQL Profile in a restricted environment before making it available to other user sessions.See Also:
Oracle Database Reference for information on the SQLTUNE_CATEGORY
initialization parameterIt is important to note that the SQL Profile does not freeze the execution plan of a SQL statement, as done by stored outlines. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile. The information stored in it continues to be relevant, even as the data distribution or access path of the corresponding statement change. In general, it is not necessary to refresh the SQL profiles. However, over a long period of time, its content can become outdated and may need to be regenerated. This can be done by running the SQL Tuning Advisor again on the same statement to regenerate the SQL Profile.
SQL Profiles apply to the following statement types:
SELECT
statementsUPDATE
statementsINSERT
statements (only with aSELECT
clause)DELETE
statementsCREATE
TABLE
statements (only with theAS
SELECT
clause)MERGE
statements (the update or insert operations)
A complete set of functions are provided for management of SQL Profiles. See 'SQL Profiles'.
17.1.3 Access Path Analysis
Indexes can tremendously enhance performance of a SQL statement by reducing the need for full table scans on large tables. Effective indexing is a common tuning technique. The Automatic Tuning Optimizer also explores whether a new index can significantly enhance the performance of a query. If such an index is identified, it recommends its creation.
Because the Automatic Tuning Optimizer does not analyze how its index recommendation can affect the entire SQL workload, it also recommends running the SQL Access Advisor utility on the SQL statement along with a representative SQL workload. The SQL Access Advisor looks at the impact of creating an index on the entire SQL workload before making any recommendations. See 'Automatic SQL Tuning Features'.
17.1.4 SQL Structure Analysis
The Automatic Tuning Optimizer identifies common problems with structure of SQL statements that can lead to poor performance. These could be syntactic, semantic, or design problems with the statement. In each of these cases the Automatic Tuning Optimizer makes relevant suggestions to restructure the SQL statements. The alternative suggested is similar, but not equivalent, to the original statement.
For example, the optimizer may suggest to replace
UNION
operator with UNION
ALL
or to replace NOT
IN
with NOT
EXISTS
. An application developer can then determine if the advice is applicable to their situation or not. For instance, if the schema design is such that there is no possibility of producing duplicates, 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.2 SQL Tuning Advisor
The 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 of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to 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.
Oracle Database can automatically tune SQL statements by identifying problematic SQL statements and implementing tuning recommendations using the SQL Tuning Advisor during system maintenance windows. You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.
17.3 Automatic SQL Tuning Advisor
Oracle Database automatically runs the 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. You can customize attributes of the maintenance windows, including start and end time, frequency, and days of the week.
See Also:
Oracle Database Administrator's Guide for information about automated maintenance taskOnce automatic SQL tuning begins, which by default runs for at most one hour during a maintenance window, the following steps are performed:
- Identify SQL candidates in the AWR for tuning.Oracle Database analyzes statistics in the AWR and generates a list of potential SQL statements that are eligible for tuning. These statements include repeating high-load statements that have a significant impact on the system. Only SQL statements that have an execution plan with a high potential for improvement will be tuned. Recursive SQL and statements that have been tuned recently (in the last month) are ignored, as are parallel queries, DMLs, DDLs, and SQL statements with performance problems that are caused by concurrency issues. The SQL statements that are selected as candidates are then ordered based on their performance impact. The performance impact of a SQL statement is calculated by summing the CPU time and the I/O times captured in the AWR for that SQL statement in the past week.
- Tune each SQL statement individually by calling the SQL Tuning Advisor.During the tuning process, all recommendation types are considered and reported, but only SQL profiles can be implemented automatically.
- Test SQL profiles by executing the SQL statement.If a SQL profile is recommended, test the new SQL profile by executing the SQL statement both with and without the SQL profile. If the performance improvement improves at least threefold, the SQL profile will be accepted (when the
ACCEPT_SQL_PROFILES
task parameter is set toTRUE
). Otherwise, only the recommendation to create a SQL profile will be reported in the automatic SQL tuning reports. - Optionally implement the SQL profiles provided they meet the criteria of threefold performance improvement.Note that other factors are considered when deciding whether or not to implement the SQL profile. For example, a SQL profile is not implemented if the objects referenced in the SQL statement have stale optimizer statistics. You can identify which SQL profiles have been implemented automatically as their type will be set to
AUTO
in theDBA_SQL_PROFILES
view.If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created. As a result, the new and improved SQL execution plan will be used by the optimizer immediately after the SQL profile is created. For information about SQL plan management, see Chapter 15, 'Using SQL Plan Management'.
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 illustrates the steps performed by Oracle Database during the automatic SQL tuning process.
Figure 17-1 Automatic SQL Tuning
Description of 'Figure 17-1 Automatic SQL Tuning'
This section contains the following topics:
17.3.1 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
will disable automatic statistics gathering by the AWR and, as a result, also disable automatic SQL tuning.See Also:
- Oracle Database Administrator's Guide for information about the AutoTask infrastructure
- Oracle Database PL/SQL Packages and Types Reference for information about the
DBMS_AUTO_TASK_ADMIN
package
17.3.2 Configuring Automatic SQL Tuning
The behavior of the automatic SQL tuning task can be configured using the
DBMS_SQLTUNE
package. To use the APIs, the user needs at least the ADVISOR
privilege.In addition to configuring the standard behavior of the SQL Tuning Advisor, the
DBMS_SQLTUNE
package enables you to configure automatic SQL tuning by specifying the task parameters using the SET_TUNING_TASK_PARAMETER
procedure. Because the automatic tuning task is owned by SYS, only the SYS user can set the task parameters.Table 17-2 lists the parameters that are specific to automatic SQL tuning which can be configured.
Table 17-1 SET_TUNING_TASK_PARAMETER Automatic SQL Tuning Parameters
Parameter | Description |
---|---|
ACCEPT_SQL_PROFILE | Specifies whether to accept SQL profiles automatically. |
MAX_SQL_PROFILES_PER_EXEC | Specifies the limit of SQL profiles that are accepted for each automatic SQL tuning task. Consider setting the limit of SQL profiles that are accepted for each automatic SQL tuning task based on the acceptable level of changes that can be made to the system on a daily basis. |
MAX_AUTO_SQL_PROFILES | Specifies the limit of SQL profiles that are accepted in total. |
EXECUTION_DAYS_TO_EXPIRE | Specifies the number of days for which to save the task history in the advisor framework schema. By default, the task history is saved for 30 days before it expires. |
To configure automatic SQL tuning, run the
SET_TUNING_TASK_PARAMETER
procedure in the DBMS_SQLTUNE
package:In this example, the automatic SQL tuning task is configured to automatically accept SQL profiles recommended by the SQL Tuning Advisor.
See Also:
- 'Configuring a SQL Tuning Task' for information about other parameters that can be configured for a SQL tuning task
- Oracle Database PL/SQL Packages and Types Reference for information about the
DBMS_SQLTUNE
package
17.3.3 Viewing Automatic SQL Tuning Reports
The automatic SQL tuning report is generated using the
DBMS_SQLTUNE
.REPORT_AUTO_TUNING_TASK
function and contains information about all executions of the automatic SQL tuning task. To run this report, you need the ADVISOR
privilege and SELECT privileges on the DBA_ADVISOR
views. Unlike the standard SQL tuning report generated using the DBMS_SQLTUNE
.REPORT_TUNING_TASK
function, which only contains information about a single task execution of the SQL Tuning Advisor, the automatic SQL tuning report contains information about multiple executions of the automatic SQL tuning task.To view the automatic SQL tuning report, run the
REPORT_AUTO_TUNING_TASK
function in the DBMS_SQLTUNE
package:In this example, a text report is generated to display all SQL statements that were analyzed in the most recent execution, including recommendations that were not implemented, and all sections of the report are included.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
packageDepending on the sections that were included in the report, you can view information about the automatic SQL tuning task in the following sections of the report:
- General informationThe general information section provides a high-level description of the automatic SQL tuning task, including information about the inputs given for the report, the number of SQL statements tuned during the maintenance, and the number of SQL profiles that were created
- SummaryThe summary section lists the SQL statements (by their SQL identifiers) that were tuned during the maintenance window and the estimated benefit of each SQL profile, or their actual execution statistics after test executing the SQL statement with the SQL profile
- Tuning findingsThis section contains the following information about each SQL statement analyzed by the SQL Tuning Advisor:
- All findings associated with each SQL statement
- Whether the profile was accepted on the system, and why
- Whether the SQL profile is currently enabled on the system
- Detailed execution statistics captured when testing the SQL profile
- Explain plansThis section shows the old and new explain plans used by each SQL statement analyzed by the SQL Tuning Advisor.
- ErrorsThis section lists all errors encountered by the automatic SQL tuning task.
17.4Reactive Tuning Using the SQL Tuning Advisor
The SQL Tuning Advisor can be invoked manually for on-demand tuning of one or more SQL statements. To tune multiple statements, you need to 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 Oracle Enterprise Manager. See 'SQL Tuning Sets'.
This section contains the following topics:
17.4.1 Input Sources
The input for the SQL Tuning Advisor can come from several sources. These input sources include:
- Automatic Database Diagnostic MonitorThe primary input source is the Automatic Database Diagnostic Monitor (ADDM). By default, ADDM runs proactively once every hour and analyzes key statistics gathered by the Automatic Workload Repository (AWR) over the last hour to identify any performance problems including high-load SQL statements. If a high-load SQL is identified, ADDM recommends running SQL Tuning Advisor on the SQL. See 'Overview of the Automatic Database Diagnostic Monitor'.
- Automatic Workload RepositoryThe second most important input source is the Automatic Workload Repository (AWR). The AWR takes regular snapshots of the system activity, including high-load SQL statements ranked by relevant statistics, such as CPU consumption and wait time.You can view the AWR and manually identify high-load SQL statements and run the SQL Tuning Advisor on them, though this is done automatically by Oracle Database as part of the automatic SQL tuning process. By default, the AWR retains data for the last eight days. Any high-load SQL that ran within the retention period of the AWR can be located and tuned using this method. See 'Overview of the Automatic Workload Repository'.
- Cursor cacheThe third likely source of input is the cursor cache. This source is used for tuning recent SQL statements that are yet to be captured in the AWR. The cursor cache and AWR together provide the capability to identify and tune high-load SQL statements from the current time going as far back as the AWR retention allows, which by default is at least 8 days.
- SQL Tuning SetAnother possible input source for the SQL Tuning Advisor is the SQL Tuning Set. A SQL Tuning Set (STS) is a database object that stores SQL statements along with their execution context. An STS can include SQL statements that are yet to be deployed, with the goal of measuring their individual performance, or identifying the ones whose performance falls short of expectation. When a set of SQL statements are used as input, a SQL Tuning Set (STS) has to be first constructed and stored. See 'SQL Tuning Sets'.
17.4.2 Tuning Options
The SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive.
- If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis. SQL Profile recommendations are not generated.
- If the comprehensive option is selected, the SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL Profiling. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes.
17.4.3 Advisor Output
After analyzing the SQL statements, the 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 simply have to choose whether or not to accept the recommendations to optimize the SQL statements.
17.4.4Running the SQL Tuning Advisor
The recommended interface for running the SQL Tuning Advisor is the Oracle Enterprise Manager. Whenever possible, you should run the SQL Tuning Advisor using Oracle Enterprise Manager, as described in the Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can run the SQL Tuning Advisor using procedures in the
DBMS_SQLTUNE
package. To use the APIs, the user must be granted specific privileges.See Also:
Oracle Database PL/SQL Packages and Types Reference for information on the security model for the DBMS_SQLTUNE
packageRunning SQL Tuning Advisor using
DBMS_SQLTUNE
package is a multi-step process:- Create a SQL Tuning Set (if tuning multiple SQL statements)
- Create a SQL tuning task
- Execute a SQL tuning task
- Display the results of a SQL tuning task
- Implement recommendations as appropriate
A SQL tuning task can be created 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. An STS can be created manually using command line APIs or automatically using Oracle Enterprise Manager. See 'SQL Tuning Sets'.
Figure 17-2 shows the steps involved when running the 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:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
package17.4.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 cursor cache, or a SQL statement selected by SQL identifier from the Automatic Workload Repository.
For example, to use the SQL Tuning Advisor to optimize a specified SQL statement text, you need to 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 employees
table in the HR schema.In this 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 have provided or generates a unique task name. You can use the task name to specify this task when using other APIs. To view the task names associated with a specific owner, you can run the following:17.4.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 this example, the maximum time that the SQL tuning task can run is changed to 300 seconds.
Table 17-2 lists the parameters that can be configured using the
SET_TUNING_TASK_PARAMETER
procedure.Table 17-2 SET_TUNING_TASK_PARAMETER Procedure Parameters
Parameter | Description |
---|---|
MODE | Specifies the scope of the tuning task:
|
USERNAME | Username under which the SQL statement will be parsed |
DAYS_TO_EXPIRE | Number of days before the task is deleted |
DEFAULT_EXECUTION_TYPE | Default execution type if not specified by the EXECUTE_TUNING_TASK function when the task is executed |
TIME_LIMIT | Time limit (in number of seconds) before the task times out |
LOCAL_TIME_LIMIT | Time limit (in number of seconds) for each SQL statement |
TEST_EXECUTE | Determines if the SQL Tuning Advisor will test execute the SQL statements to verify the recommendation benefit:
|
BASIC_FILTER | Basic filter used for SQL tuning set |
OBJECT_FILTER | Object filter used for SQL tuning set |
PLAN_FILTER | Plan filter used for SQL tuning set |
RANK_MEASURE1 | First ranking measure used for SQL tuning set |
RANK_MEASURE2 | Second ranking measure used for SQL tuning set |
RANK_MEASURE3 | Third ranking measure used for SQL tuning set |
RESUME_FILTER | Extra filter used for SQL tuning set (besides BASIC_FILTER ) |
SQL_LIMIT | Maximum number of SQL statements to tune |
SQL_PERCENTAGE | Percentage filter of statements from SQL tuning set |
17.4.4.3 Executing a SQL Tuning Task
After you have created a tuning task, you need to execute the task and start the tuning process. For example:
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. The SQL Tuning Advisor will perform 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.4.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:17.4.4.5Checking the Progress of the SQL Tuning Advisor
You can check the execution progress of the SQL Tuning Advisor in the
V$ADVISOR_PROGRESS
view. For example:See Also:
Oracle Database Reference for information on the V$ADVISOR_PROGRESS view17.4.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 the SQL Tuning Advisor. For each proposed recommendation, the rationale and benefit is provided along with the SQL commands needed to implement the recommendation.
Additional information about tuning tasks and results can be found in DBA views. See 'SQL Tuning Information Views'.
17.4.4.7 Additional Operations on a SQL Tuning Task
You can use the following APIs for managing SQL tuning tasks:
INTERRUPT_TUNING_TASK
to interrupt a task while executing, causing a normal exit with intermediate resultsRESUME_TUNING_TASK
to resume a previously interrupted taskCANCEL_TUNING_TASK
to cancel a task while executing, removing all results from the taskRESET_TUNING_TASK
to reset a task while executing, removing all results from the task and returning the task to its initial state- Maserati vx1 vst free download.
DROP_TUNING_TASK
to drop a task, removing all results associated with the task
17.5SQL 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. The SQL statements can be loaded into a SQL Tuning Set from different SQL sources, such as the Automatic Workload Repository, the cursor cache, or custom SQL provided by the user. An STS 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, the SQL statements can be ranked based on any combination of execution statistics.
A SQL Tuning Set can be used as input to the SQL Tuning Advisor, which performs automatic tuning of the SQL statements based on other input parameters specified by the user. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning. When poorly performing SQL statements are encountered on a production system, it may not be desirable for developers to perform their investigation and tuning activities on the production system directly. This feature allows the DBA to transport the problematic SQL statements to a test system where the developers can safely analyze and tune them. To transport SQL Tuning Sets, use the
DBMS_SQLTUNE
package procedures.The recommended interface for managing SQL tuning sets is the Oracle Enterprise Manager. Whenever possible, you should manage SQL tuning sets using Oracle Enterprise Manager, as described in the Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can manage SQL tuning sets using the
DBMS_SQLTUNE
package procedures. Typically you would use the STS operations in the following sequence:- Create a new STS
- Load the STS
- Select the STS to review the contents
- Update the STS if necessary
- Create a tuning task with the STS as input
- Transporting the STS to another system if necessary
- Drop the STS when finished
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.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:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
package17.5.1 Creating a SQL Tuning Set
The
CREATE_SQLSET
procedure is used to create an empty STS object in the database. For example, the following procedure creates an STS object that could be used to tune I/O intensive SQL statements during a specific period of time:where
my_sql_tuning_set
is the name of the STS in the database and 'I/O intensive workload'
is the description assigned to the STS.17.5.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 cursor cache. For both the workload repository and STS, predefined table functions can be used to select columns from the source to populate a new STS.In the following example, procedure calls are used to 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.5.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.5.4 Modifying a SQL Tuning Set
SQL statements can be updated and deleted from a SQL Tuning Set 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.5.5 Transporting a SQL Tuning Set
SQL Tuning Sets can be transported to another system by first exporting the STS from one system to a staging table, then importing the STS from the staging table into another system.
To transport a SQL Tuning Set:
- Use the
CREATE_STGTAB_SQLSET
procedure to create a staging table where the SQL Tuning Sets will be exported.The following example shows how to create a staging table namedstaging_table
. Table names are case-sensitive. - Use the
PACK_STGTAB_SQLSET
procedure to export SQL Tuning Sets into the staging table.The following example shows how to export a SQL Tuning Set namedmy_sts
to the staging table. - Move the staging table to the system where the SQL Tuning Sets will be imported using the mechanism of choice (such as datapump or database link).
- On the system where the SQL Tuning Sets will be imported, use the
UNPACK_STGTAB_SQLSET
procedure to import SQL Tuning Sets from the staging table.The following example shows how to import SQL Tuning Sets contained in the staging table.
17.5.6 Dropping a SQL Tuning Set
The
DROP_SQLSET
procedure is used to drop an STS that is no longer needed. For example:17.5.7 Additional Operations on SQL Tuning Sets
You can use the following APIs to manage an STS:
- Updating the attributes of SQL statements in an STSThe
UPDATE_SQLSET
procedure updates the attributes of SQL statements (such asPRIORITY
orOTHER
) in an existing STS identified by STS name and SQL identifier. - Capturing the full system workloadThe
CAPTURE_CURSOR_CACHE_SQLSET
function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using theSELECT_CURSOR_CACHE
andLOAD_SQLSET
procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as opposed to the AWR—which only captures the workload of high-load SQL statements—or theLOAD_SQLSET
procedure, which accesses the data source only once. - Adding and removing a reference to an STSThe
ADD_SQLSET_REFERENCE
function adds a new reference to an existing STS to indicate its use by a client. The function returns the identifier of the added reference. TheREMOVE_SQLSET_REFERENCE
procedure is used to deactivate an STS to indicate it is no longer used by the client.
17.6SQL Profiles
While SQL Profiles are usually handled by Oracle Enterprise Manager as part of the Automatic SQL tuning process, SQL Profiles can be managed through the
DBMS_SQLTUNE
package. To use the SQL Profiles APIs, you need the ADMINISTER SQL MANAGEMENT OBJECT
privilege.Figure 17-4 shows the steps involved when using SQL Profiles APIs.
Figure 17-4 SQL Profiles APIs
Description of 'Figure 17-4 SQL Profiles APIs'
This section covers the following topics:
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SQLTUNE
package17.6.1 Accepting a SQL Profile
When the SQL Tuning Advisor recommends that a SQL Profile be used, you should accept the SQL Profile that is recommended. In cases where the SQL Tuning Advisor recommends that an index and a SQL Profile be used, both should be used. You can use the
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE
procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database. For example:In this example, my_
sql_tuning_task
is the name of the SQL tuning task and my_sql_profile
is the name of the SQL Profile that you want to accept.Typically, an accepted SQL Profile is associated with the SQL statement through a special SQL signature that is generated using a hash function. This hash function normalizes the SQL statement for case (changes the entire SQL statement to upper case) and white spaces (removes all extra whites spaces) before generating the signature. The same SQL Profile thus will work for all SQL statements that are essentially the same, where the only difference is in case usage and white spaces. However, by setting
force_match
to true, the SQL Profile will additionally target all SQL statements that have the same text after normalizing literal values to bind variables. This may be useful for applications that use literal values rather than bind variables, since this will allow SQL with text differing only in its literal values to share a SQL Profile. If both literal values and bind variables are used in the SQL text, or if this parameter is set to false (the default value), literal values will not be normalized.If SQL plan management is used and there is already an existing plan baseline for the SQL statement, a new plan baseline will be added when a SQL profile is created. If SQL plan management is not used, a new plan baseline will not be added when a SQL profile is created. There is no strict relationship between the SQL profile and the plan baseline. When hard parsing a SQL statement, the optimizer will use the SQL profile to select the best plan baseline from the ones available. In different conditions, the SQL profile may cause the optimizer to select different plan baselines. For information about SQL plan management, see Chapter 15, 'Using SQL Plan Management'.
You can view information about a SQL Profile in the
DBA_SQL_PROFILES
view.17.6.2 Altering a SQL Profile
You can alter the
STATUS
, NAME
, DESCRIPTION
, and CATEGORY
attributes of an existing SQL Profile with the ALTER_SQL_PROFILE
procedure. For example:In this example,
my_sql_profile
is the name of the SQL Profile that you want to alter. The status attribute is changed to disabled, which means the SQL Profile is not used during SQL compilation.17.6.3 Dropping a SQL Profile
You can drop a SQL Profile with the
DROP_SQL_PROFILE
procedure. For example:In this example,
my_sql_profile
is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE
is accepted.17.7 SQL Tuning Information Views
This section summarizes the views that you can display to review information that has been gathered for tuning the SQL statements. You need DBA privileges to access these views.
- Advisor information views, such as
DBA_ADVISOR_TASKS
,DBA_ADVISOR_EXECUTIONS
,DBA_ADVISOR_FINDINGS
,DBA_ADVISOR_RECOMMENDATIONS
, andDBA_ADVISOR_RATIONALE
views. - SQL tuning information views, such as
DBA_SQLTUNE_STATISTICS
,DBA_SQLTUNE_BINDS
, andDBA_SQLTUNE_PLANS
views. - SQL Tuning Set views, such as
DBA_SQLSET
,DBA_SQLSET_BINDS
,DBA_SQLSET_STATEMENTS
, andDBA_SQLSET_REFERENCES
views. - Information on captured execution plans for statements in SQL Tuning Sets are displayed in the
DBA_SQLSET_PLANS
andUSER_SQLSET_PLANS
views. - SQL Profile information is displayed in the
DBA_SQL_PROFILES
view.TheTYPE
parameter shows if the SQL profile was created manually by the SQL Tuning Advisor (ifTYPE
=MANUAL
) or automatically by automatic SQL tuning (ifTYPE
=AUTO
). - Advisor execution progress information is displayed in the
V$ADVISOR_PROGRESS
view. - Dynamic views containing information relevant to the SQL tuning, such as
V$SQL
,V$SQLAREA
,V$SQLSTATS
, andV$SQL_BINDS
views.See Also:Oracle Database Reference for information on static data dictionary and dynamic views