Feb 17, 2012 DBA-Village contains news, tips, scripts and much more information for Oracle Database Administrators. Oracle Database - Enterprise Edition - Version 11.2.0.2 and later: Automatic SQL Tuning SYSAUTOSQLTUNINGTASK Fails With ORA-13605 or ORA-13602. Mar 11, 2015 ORA-07445 for SYSAUTOSQLTUNINGTASK Automatic SQL Tuning in Oracle Database 11g. As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows.
8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Home » Articles » 11g » Here
Oracle Database 11g Release 2 (11.2.0.2 onward) has some minor changes to the Automatic SQL Tuning feature introduced on Oracle 11g Release 1. In the previous release, reports and amendments to the automatic tuning task parameters was performed using the
DBMS_SQLTUNE
package. From 11.2.0.2 onward, this should be done using the DBMS_AUTO_SQLTUNE
package, which requires the DBA role.Sys_auto_sql_tuning_task Stop
The top-level enabling and disabling of the admin task is still done using the
DBMS_AUTO_TASK_ADMIN
package, described here.Related articles.
EXECUTE_AUTO_TUNING_TASK
TheEXECUTE_AUTO_TUNING_TASK
procedure and function manually initiate the SYS_AUTO_SQL_TUNING_TASK
task. The only difference between the two is the function returns the task name, which is useful if you don't specify one, as it returns the system generated name. ![Sys_auto_sql_tuning_task Job Sys_auto_sql_tuning_task Job](/uploads/1/2/5/7/125760025/360457844.jpg)
The
EXECUTE_AUTO_TUNING_TASK
procedure and function can only be called when logged on as SYS.SET_AUTO_TUNING_TASK_PARAMETER
The
SET_AUTO_TUNING_TASK_PARAMETER
procedures are overloads that accept both string and numeric parameter values. These replace DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER
as the preferred way to amend the parameters for the SYS_AUTO_SQL_TUNING_TASK
task. The parameters that can be amended are listed here.The following example shows both the original (
DBMS_SQLTUNE
) and preferred method (DBMS_AUTO_SQLTUNE
) of changing the parameters for the SYS_AUTO_SQL_TUNING_TASK
task.REPORT_AUTO_TUNING_TASK
The
REPORT_AUTO_TUNING_TASK
function replaces DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK
as the preferred way to get the results of the SYS_AUTO_SQL_TUNING_TASK
task. Calling it with no parameters shows the results for the latest execution of the task.For more information see:
Hope this helps. Regards Tim...
Oracle 12c offers a useful utility called the SQL Tuning Advisor. You can use this built-in tool to provide suggestions or recommendations about certain SQL statements. Although it may not always give perfect advice, just like anything else, having it in your toolbox of tuning techniques is beneficial.
- Use PL/SQL and the internal package DBMS_SQL_TUNE to create a tuning task. Type this:You should see the following:In the preceding command, note the TIME_LIMIT of 60. That limits the processing time to 60 seconds. You may not always want to run something like this for long periods in your database, because it incurs system overhead.
- Execute the tuning advisor with your task by typing this:Because of the limit of 60 seconds provided in the task creation, this step may take up to 60 seconds to complete. During this time, your prompt won’t come back.When it completes, you should see this:If you’ve set a longer time and are getting impatient, you can open another SQL window to make sure that the task is still executing by typingYou see something like the following:
- When the execution is complete, you can view the results by running the BMS_SQLTUNE.report_tuning_task procedure. Type the following:For the sake of space, we’ve snipped some sections from the output that follows, but you see something like this:The latter part of the report shows the before and after execution plans. In this case, you’ve seen the before when you were generating execution plans. Go ahead and add the index, regenerate the execution plan, and see whether you’ve made an improvement.Before you add the index, note that the recommendations give the SQL to add the index:Also note that Oracle gives a warning:
- Add the index with your own name by typing this:You should see something like the following:
- Take a look at the execution plan. Type the following:And then typeYou should see output like this:
Now that you’ve added the index, a few things are evident:
- The cost of the plan dropped from 40336 to 5.
- There are now six steps.
- The full table scan is gone. Instead you see the use of your new index.
Often one of the tough parts about tuning a database is having a solid understanding of the application and the data. The issue might not always be obvious. Sometimes engaging other application and data experts helps.
Explain to them your findings and what you propose. They may be able to help you come to a conclusion. Also, if the data is part of a packaged third-party application, sometimes opening a ticket with the vendor is the way to go.