You can use the SQL Tuning Advisor to improve poorly performing SQL statements. The SQL Tuning Advisor provides the following to help you tune bad SQL statements:
• Advice on improving the execution plan
• Reasons for the SQL improvement recommendations
• Benefits you can expect by following the Advisor’s advice
• Details of the commands to tune the misbehaving SQL statements
Using the SQL Tuning Advisor
The SQL Tuning Advisor can use the following sources:
• New SQL statements. When working with a development database, this may be your best
source of SQL statements.
• High-load SQL statements.
• SQL statements from the AWR.
• SQL statements from the database cursor cache.
The Advisor can tune sets of SQL statements called SQL Tuning Sets. An STS is a set of SQL statements combined with execution information, which includes the average elapsed time. An STS has the advantage of capturing the information about a database’s workload as well as allowing you to tune several large SQL statements at once.
How the SQL Tuning Advisor Works
As mentioned previously, the optimizer will try to find the optimal execution plan for each statement you provide. However, this process happens under production conditions, so the optimizer can only devote a short amount of time to working out a solution. The optimizer uses heuristics to generate an estimate of the best solution. This is called the normal mode of the optimizer.
You can also run the optimizer in tuning mode, which means that the optimizer carries out in-depth analysis to come up with ways to optimize execution plans. While in this mode, the optimizer can take several minutes and produces recommendations instead of the best SQL execution plan.
You, in turn, use these recommendations to optimize the SQL statements’ execution plans. You get the added advantage of advice that details the rationale behind the recommendations and what you will gain from implementing them. The Oracle optimizer running in tuning mode is called the Automatic Tuning Optimizer (ATO). The ATO does the following tasks:
• Statistics analysis
• SQL profiling
• Access path analysis
• SQL structure analysis
These tasks are described in the following sections, along with the types of recommendations that the SQL Tuning Advisor makes.
Statistics Analysis
The ATO makes sure that there are representative, up-to-date statistics for all the objects in the SQL statement, which you need for efficient execution plans. If the ATO finds any statistics that are missing or stale, it suggests that you collect new statistics for the objects in question. During this process, the ATO collects other information that it can use to fill in any missing statistics. It can also correct stale Statistics.
SQL Profiling
At this stage the ATO tries to verify the validity of its estimates of factors such as column selectivity and cardinality of database objects. It can use three methods to verify its estimates:
• Dynamic data sampling: The ATO can use a data sample to check its estimates. The ATO can apply correction factors if the data-sampling process shows its estimates to be significantly wrong.
• Partial execution: The ATO can carry out the partial execution of a SQL statement. This process allows it to check whether its estimates are close to what really happens. It does not check whether its estimates are correct, but rather it checks whether a plan derived from those statistics is the best possible plan.
• Past execution history statistics: The ATO can use the SQL statement’s execution history to
help with its work. If there’s enough information from statistics analysis or SQL profiling, the ATO suggests you create a SQL profile, which is supplementary information about a SQL statement. If you accept this advice and are running the optimizer in tuning mode, Oracle will store the SQL profile in the data dictionary. Once you have done this, the optimizer uses it to produce optimal execution plans, even when it is running in normal mode.
■Tip Remember that a SQL profile is not the same thing as a stored execution plan.
The SQL profile will continue to apply if you make small changes to your database and allow
your objects to grow normally. One of the big advantages of SQL profiles is the ability to tune packaged applications. These are hard to tune because you can’t easily access and modify the code. Because SQL profiles are saved in the data dictionary, you can use them to tune packaged applications.
Analyzing Access Paths
The ATO analyzes how using an improved access method, such as working with an index, will affect queries. These are important considerations, because adding an index can substantially increase the speed of a query. However, adding new indexes can adversely affect other SQL statements; the SQL Advisor knows this and makes its recommendations as follows:
• If an index is effective, it will advise you to create it.
• It can advise you to run the SQL Access Advisor (see Chapter 7 for details) to analyze the
wisdom of adding the new index.
SQL Structure Analysis
The ATO can make recommendations to modify the structure (both the syntax and semantics) of poorly performing SQL statements. The ATO considers issues such as the following:
• Design mistakes; for example, performing full table scans because you didn’t create indexes.
• Using inefficient SQL; for example, the NOT IN construct, which is known to be much slower than the NOT EXISTS construct in general.
■Note The ATO only identifies poorly written SQL, but it won’t rewrite it for you. You will know your application better than the ATO, so Oracle only provides advice, which you can implement or not.
Recommendations
Here are some recommendations that the SQL Tuning Advisor will give you:
• Creating indexes will speed up access paths.
• Using SQL profiles will allow you to generate a better execution plan.
• Gathering optimizer statistics for objects that do not have any, or renewing stale statistics,
will be of benefit.
• Rewriting SQL as advised will improve its performance.
The SQL Tuning Advisor in Practice
You can use the SQL Tuning Advisor through packages or through the web interface of the OEM Database Control.
Using the DBMS_SQLTUNE Package to Run the SQL Tuning Advisor
The main SQL package for tuning SQL statements is DBMS_SQLTUNE. The first example will be creating and managing tasks that tune SQL statements.
■Note You must have the ADVISOR privilege to use the DBMS_SQLTUNE package. Ensure that you do before running any of the following examples.
Performing Automatic SQL Tuning
Here’s how to tune SQL statements using the DBMS_SQLTUNE package:
1. Create a task: The CREATE_TUNING_TASK procedure creates a task to tune a single statement or several statements (a SQL tuning set or STS). You can also use a SQL statement (using the SQL identifier) from the AWR or from the cursor cache. In the following example, I show how o create a task using a single SQL statement as input. First, I pass the SQL statement as a CLOB argument, as shown here:
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ *
FROM employees e, locations l, departments d
WHERE e.department_id = d.department_id AND
l.location_id = d.location_id AND
e.employee_id < :bnd';
Next, I create the following tuning task:
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(90)),
user_name => 'HR',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'my_sql_tuning_task',
description => 'Task to tune a query on a specified employee');
END;
/ In the preceding task, sql_text refers to the single SQL statement that I’m tuning. The bind_list shows that 90 is the value of the bind variable bnd. The tuning task’s scope is comprehensive, meaning that it analyzes the SQL Profile, and the task_limit parameter sets a limit of 60 seconds on the total time for analysis.
2. Execute the task: To execute the task, run the EXECUTE_TUNING_TASK procedure:
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/ 3. Get the tuning report: You can view the tuning process with the REPORT_TUNING_TASK
procedure:
SQL> SET LONG 1000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 100
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
FROM DUAL; The report consists of findings and recommendations. The Tuning Advisor provides the
rationale and the expected benefits for each recommendation. It also provides you with the
SQL to implement the recommendations.
You can use the following views to manage your automatic SQL tuning efforts:
• DBA_ADVISOR_TASKS
• DBA_ADVISOR_FINDINGS
• DBA_ADVISOR_RECOMMENDATIONS
• DBA_ADVISOR_RATIONALE
• DBA_SQLTUNE_STATISTICS
• DBA_SQLTUNE_PLANS
Managing SQL Profiles
Once the ATO has made its recommendations, you can accept its findings and run the DBMS_ SQLTUNE.ACCEPT_SQL_PROFILE procedure to create an appropriate SQL profile, though you must ensure you have the CREATE_ANY_PROFILE privilege first.
The preceding may seem to say that a SQL profile is an inevitable consequence of an ATO
process, but it will only recommend that you create a SQL profile if it has built one as a result of its scan. However, it will only do this if it collected auxiliary information while analyzing statistics and profiling SQL (as detailed previously). Oracle will apply the new profile to the SQL statement when you execute it.
Managing SQL Tuning Categories
You may find that you have a number of different SQL profiles for a single SQL statement. Oracle has to manage them in some way, so it assigns each one to a SQL tuning category. The same process occurs when a user logs in, meaning that Oracle will assign a user to a tuning category. The category is selected according to the SQLTUNE_CATEGORY initialization parameter.
If you do not change it, SQLTUNE_CATEGORY takes the value DEFAULT. This means that any SQL profiles belonging to the default category apply to everyone who logs in. You can alter the SQL tuning category for every user with the ALTER SYSTEM command. You can also alter a session’s tuning category with the ALTER SESSION command. For example, take the PROD and DEV categories. To change the SQL tuning category for every user, do the following:
SQL> ALTER SYSTEM SET SQLTUNE_CATEGORY = PROD;
If you wanted to change a session’s tuning category, you could do this:
SQL> ALTER SESSION SET SQLTUNE_CATEGORY = DEV; ■Note You may also use the DBMS_SQLTUNE.ALTER_SQL_PROFILE procedure to alter the SQL tuning category.
Using the OEM to Run the SQL Tuning Advisor
To use the OEM to run the Advisor, click Related Links ➤ Advisor Central ➤ SQL Tuning Advisor.
This is the SQL Tuning Advisor page. Here you can specify the SQL statements that the SQL Advisor will analyze, which can be one of two kinds:
• Top SQL: These SQL statements could be top SQL from the cursor cache or saved high-load SQL statements that have come from the AWR.
• SQL Tuning Sets: You can create an STS from any set of SQL statements.
Choosing one of the four links on the SQL Tuning Advisor page will take you to your selected data source. You can now launch the SQL Tuning Advisor if you wish.
The Automatic SQL Tuning Advisor
I explained the Automatic Tuning Optimizer earlier in this chapter. This is what Oracle calls the optimizer when it runs in tuning mode. The Automatic Tuning Optimizer performs the following types of analysis for high-load SQL statements, with a goal of isolating poorly written SQL statements and making recommendations to improve them: statistics analysis, SQL profiling, access path analysis, and SQL structure analysis. When you execute a SQL Tuning Advisor session, it invokes the Automatic Tuning Optimizer to tune the SQL statements. The SQL Tuning Advisor provides recommendations, but can’t implement them for you.
The Automatic Tuning Optimizer also runs regularly as an automated maintenance task called the (Automatic) SQL Tuning Advisor task.
The advisor can identify poorly running SQL statements by picking them from the AWR, make recommendations to improve them, and also implement any recommendations that invoke the use of SQL profiles. The SQL Tuning Advisor task conducts statistics analysis, SQL profiling, access path analysis, and SQL structure analysis.
The Automatic SQL Tuning process consists of the identification of candidates for tuning, tuning the statements and making recommendations, testing the recommendations, and automatic implementation of the SQL profile recommendations. I describe each of the steps in the following sections.
Identifying SQL Tuning Candidates
The Automatic SQL Tuning Advisor uses the AWR Top SQL identification process for selecting candidates for automatic tuning. The database takes into account the CPU time and I/O time utilized by SQL statements to select these candidates. The goal is to select statements that offer a large potential for improvement. The advisor prepares a list of candidate statements by organizing the top SQL queries in the past week into the following “buckets”:
• Top for the week
• Top for any day in the week
• Top for any hour during the week
• Highest average single execution
By assigning weights to each of the buckets, the SQL Tuning Advisor combines the four buckets into a single group of statements and ranks the statements according to their impact on performance.
Subsequently, during the maintenance window, the advisor automatically tunes each of the candidate SQL statements selected by it.
A SQL profile consists of addition statistics beyond those collected by the optimizer, to help
evolve better execution plans. The additional information gathered via SQL profiles may include customized optimizer settings, adjustments to compensate for missing or stale statistics, and adjustments for estimation errors in optimization statistics. Since you don’t need to change the SQL query when you implement a SQL profile, they are ideal for use in packaged applications. Implementation of a SQL profile would normally lead to the generation of more efficient execution plans for SQL statements.
Tuning and Making Recommendations
The SQL Tuning Advisor tunes statements in the order of their performance impact. If the advisor finds stale or missing statistics, it lets the GATHER_STATS_JOB know about this fact, so it can collect statistics for it when the database collects statistics the next time.
The advisor makes different types of recommendations to improve the poorly performing SQL statements, including the creation of indexes, refreshing of the optimizer statistics, restructuring SQL statements, and creation of SQL profiles. The advisor can automatically implement only the SQL profile creation recommendations. The advisor creates and tests the SQL profiles it recommends before implementing them. You can decide whether to retain the new SQL profiles that are automatically implemented by the advisor or not, based on an analysis of the SQL Tuning Advisor report.
Testing the Recommendations for New SQL Profiles
For any SQL profile recommendation it makes, the SQL Tuning Advisor runs the statement with and without the profile and compares the performance. The advisor will recommend adopting a profile only if implementing the profile leads to at least a threefold increase in improvement in performance, as shown by a reduction in the sum of the CPU and I/O usage.
Implementing the SQL Profiles
The setting of the ACCEPT_SQL_PROFILES attribute of the SET_TUNING_TASK_PARAMETERS view determines whether the database automatically accepts the SQL profile recommendations made by the Automatic SQL Tuning Advisor. The DBA_SQL_PROFILES view shows all the automatically implemented SQL profiles. If a SQL profile was automatically implemented, it’ll have a value of AUTO in the TYPE column.
Limitations
You can’t tune the following types of statements with the Automatic SQL Tuning Advisor:
• Parallel queries
• Ad hoc queries
• Recursive statements
• SQL statements that use the INSERT and DELETE statements
• SQL statements that use DDL statements such as CREATE TABLE AS SELECT
If a query takes a long time to execute after implementing a SQL profile, the advisor will reject the implementation of the SQL profile, since it can’t test-execute the query. Note that with the exception of ad hoc statements, you can manually tune all the preceding types of statements with a manual invocation of the SQL Tuning Advisor.
Configuring Automatic SQL Tuning
Use the DBMS_SQLTUNE package to configure and manage the automatic SQL tuning task. Manage the SYS_AUTO_TUNING_TASK, which controls the automatic SQL tuning job, with the following procedures:
• SET_TUNING_TASK_PARAMETERS: Use this procedure to test task parameters controlling
items such as whether to automatically implement SQL profiles.
• EXECUTE_TUNING_TASK: Use this procedure to run the tuning task in the foreground.
• EXPORT_TUNING_TASK: This procedure helps produce a task execution report.
The Automatic SQL Tuning Advisor job runs for a maximum of one hour by default, but you can change the execution time limit by executing the SET_TUNING_TASK_PARAMETERS procedure, as shown here:
SQL> exec dbms_sqltune.set_tuning_task_parameter
('SYS_AUTO _SQL_TUNING_TASK', 'TIME_LIMIT', 14400); The previous example shows how to raise the maximum run time for the SQL tuning task to four hours, from its default value of one hour.
The SET_TUNING_TASK_PARAMETERS procedure enables you to configure the tuning task by specifying the following parameters:
• ACCEPT_SQL_PROFILES determines whether the database must automatically accept a SQL profile.
• REPLACE_USER_SQL_PROFILES determines whether the task should replace the SQL profiles created by the user.
• MAX_SQL_PROFILES_PER_EXEC specifies the maximum number of SQL profiles that can be accepted for a single automatic SQL tuning task.
• MAX_AUTO_SQL_PROFILES determines the total number of SQL profiles that are accepted by the database.
• EXECUTION_DAYS_TO_EXPIRE specifies the maximum number of days for which the database saves the task history. The default is 30 days.
The following example shows how to configure a SQL tuning task that’ll automatically accept all SQL profile recommendations:
SQL> begin
2 dbms_sqltune.set_tuning_task_parameters(
3 task_name => 'SYS_AUTO_SQL_TUNING_PROG',
4 parameter => 'accept_sql_profiles', value => 'true');
5* end;
SQL> / The previous example sets the value for the ACCEPT_SQL_PROFILES parameter to TRUE, which makes the advisor automatically accept SQL profile recommendations.
The SYS_AUTO_SQL_TUNING_TASK procedure runs the automatic SQL tuning job every night during the maintenance window of the Oracle Scheduler. It tunes SQL statements according to the priority ranking in the SQL candidates. It creates necessary SQL profiles for a statement and tests them before tuning the next statement in the candidate list.
Managing Automatic SQL Tuning
Use the DBMS_AUTO_TASK_ADMIN package to enable and disable the Automatic SQL Tuning Advisor job during the Scheduler maintenance window. The ENABLE procedure helps you enable the Automatic SQL Tuning Advisor task:
begin
dbms_auto_task_admin.enable (
client_name => 'sql tuning advisor',
operation => 'NULL',
window_name='NULL');
end; The value NULL for the WINDOW_NAME parameter will enable the task in all maintenance windows.
To specify the task in a specific maintenance window, specify a window name, as shown here:
begin
dbms_auto_task_admin.enable (
client_name => 'sql tuning advisor',
operation => 'NULL',
window_name='monday_night_window');
end; To disable the Automatic SQL Tuning Advisor task, execute the DISABLE procedure, as shown here:
begin
dbms_auto_task_admin.disable (
client_name => 'sql tuning advisor',
operation => 'NULL',
window_name='NULL');
end;
The previous code will disable the automatic SQL tuning tasks in all maintenance windows,
since I didn’t specify a value for the WINDOW_NAME parameter.
■Tip By setting the TEST_EXECUTE parameter when you execute the SET_TUNING_TASK_PARAMETER procedure, you can run the SQL Tuning Advisor in test execute mode to save time.
You can also configure all Automatic SQL Tuning parameters easily through the Database control (or the Grid Control). Go to the Automatic SQL Tuning Settings page, accessible by clicking the Configure button in the Automated Maintenance Tasks page. You can configure all automated tasks from the Automated Maintenance Tasks configuration page. Here’s a simple example that shows how to get recommendations for fixing a SQL statement:
1. Click the finding with the highest impact on database time in the Database Home page.
2. Click Schedule SQL Tuning Advisor on the SQL Details page.
3. Click Submit on the Scheduler Advisor page.
4. Click Implement if you want to adopt the advisor’s recommendations.
5. Click Yes on the Confirmation page, and the database creates a new SQL profile.
6. View the tuning benefits by going to the Performance page after the database executes the tuned statement again.
Go to the Automated Maintenance Task page to view information about the latest executions of the Automatic SQL Tuning Advisor.
Click the Server tab in the Database Control home page first.
Click the Automated Maintenance Tasks link under the Tasks section in the Server page, and then click the most recent execution icon or the Automatic SQL Tuning task link to view the Automatic SQL Tuning Result Summary page.
Interpreting Automatic SQL Tuning Reports
You can get a report of the Automatic SQL Tuning Advisor tasks by executing the
REPORT_AUTO_TUNING_TASK function, as shown here:
SQL> begin
2 :test_report :=dbms_sqltune. report_auto_tuning_task (
3 type => 'text',
4 level => 'typical',
5 section => 'all');
6* end;
SQL> /
PL/SQL procedure successfully completed.
SQL>
print :test_report The report produced by the previous code contains information about all the statements analyzed by the advisor in its most recent execution and includes both the implemented and unimplemented advisor recommendations. The report also contains EXPLAIN PLANs before and after implementing the tuning recommendations.
You can use the following views to get information about the Automatic SQL Tuning Advisor jobs:
• DBA_ADVISOR_EXECUTIONS: Shows metadata information for each task
• DBA_ADVISOR_SQLSTATS: Shows a list of all SQL compilation and execution statistics
• DBA_ADVISOR_SQLPLANS: Shows a list of all SQL execution plans
The following code, for example, provides information about all Automatic SQL Tuning
Advisor tasks:
SQL> SELECT execution_name, status, execution_start, execution_end
FROM dba_advisor_executions
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK';
Using Other GUI Tools
The EXPLAIN PLAN and SQL Trace utilities aren’t the only tools you have available to tune SQL statements. Several GUI-based tools provide the same information much more quickly. Just make sure that statistics collection is turned on in the initialization file before you use these tools.
One of the well-known third-party tools is the free version of TOAD software, which is marketed by Quest Software (http://www.quest.com). From this tool you get not only the execution plan, but also memory usage, parse calls, I/O usage, and a lot of other useful information, which will help you tune your queries. The use of GUI tools helps you avoid most of the drudgery involved in producing and reading EXPLAIN PLANs. Note that whether you use GUI tools or manual methods, the dynamic performance views that you use are the same. How you access them and use the data makes the difference in the kind of tool you use.
Comments