Oracle DBMS_AUTO_SQLTUNE
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Interface to SQL Tuning Advisor (DBMS_SQLTUNE) when run within the Autotask framework. The database creates the automated system task SYS_AUTO_SQL_TUNING_TASK as part of the catalog scripts. This task automatically chooses a set of high-load SQL from AWR and runs the SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.
AUTHID CURRENT_USER
Constants
Name Data Type Value
Report Level
LEVEL_ALL VARCHAR2(3) dbms_sqltune.LEVEL_ALL
LEVEL_BASIC VARCHAR2(5) dbms_sqltune.LEVEL_BASIC
LEVEL_TYPICAL VARCHAR2(7) dbms_sqltune.LEVEL_TYPICAL
Report Section
SECTION_ALL VARCHAR2(3) dbms_sqltune.SECTION_ALL
SECTION_ERRORS VARCHAR2(6) dbms_sqltune.SECTION_ERRORS
SECTION_FINDINGS VARCHAR2(8) dbms_sqltune.SECTION_FINDINGS
SECTION_INFORMATION VARCHAR2(11) dbms_sqltune.SECTION_INFORMATION
SECTION_PLANS VARCHAR2(5) dbms_sqltune.SECTION_PLANS
SECTION_SUMMARY VARCHAR2(7) dbms_sqltune.SECTION_SUMMARY
Report Type
TYPE_TEXT VARCHAR2(4) dbms_sqltune.TYPE_TEXT
Dependencies
DBMS_ADVISOR DBMS_SQLTUNE_INTERNAL PRVT_SQLADV_INFRA
DBMS_SQLTUNE PRVT_ADVISOR  
Documented Yes
First Available 11gR2
Security Model Owned by SYS with EXECUTE granted to DBA and EM_EXPRESS_ALL roles
Source {$ORACLE_HOME}/rdbms/admin/dbmssqlt.sql
Subprograms
 
EXECUTE_AUTO_TUNING_TASK
Called to execute SYS_AUTO_SQL_TUNING_TASK manually. The behavior will be the same as in automatic executions.Only SYS can call this API.

Overload 1
dbms_auto_sqltune(
execution_name   IN VARCHAR2             := NULL,
execution_params IN dbms_advisor.arglist := NULL,
execution_desc   IN VARCHAR2             := NULL)
RETURN VARCHAR2;
set serveroutput on

DECLARE
 retVal VARCHAR2(64);
BEGIN
  retVal := dbms_auto_sqltune;
  dbms_output.put_line(retVal);
END;
/
Overload 2 dbms_auto_sqltune(
execution_name   IN VARCHAR2             := NULL,
execution_params IN dbms_advisor.arglist := NULL,
execution_desc   IN VARCHAR2             := NULL);
exec dbms_auto_sqltune;
 
REPORT_AUTO_TUNING_TASK
Retrieves a report from the automatic tuning task. This differs from the report_tuning_task API in that it takes a range of subtasks to report on. This API also exists in the DBMS_SQLTUNE package. All users with access to the views can see a report. dbms_auto_sqltune(
begin_exec   IN VARCHAR2 := NULL,
end_exec     IN VARCHAR2 := NULL,
type         IN VARCHAR2 := TYPE_TEXT,
level        IN VARCHAR2 := TYPE_TYPICAL,
section      IN VARCHAR2 := TYPE_ALL,
object_id    IN NUMBER   := NULL,
result_limit IN NUMBER   := NULL)
RETURN CLOB;
set serveroutput on

DECLARE
 retVal CLOB;
BEGIN
  retVal := dbms_auto_sqltune.report_auto_tuning_task(type => dbms_sqltune.type_text, level => dbms_sqltune.level_all, result_limit=>20);
  dbms_output.put_line(retVal);
END;
/

GENERAL INFORMATION SECTION
-------------------------------------------------------------
Tuning Task Name                   : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                  : SYS
Tuning Task ID                     : 1
Workload Type                      : Automatic High-Load SQL Workload
Execution Count                    : 8
Current Execution                  : EXEC_110
Execution Type                     : TUNE SQL
Scope                              : COMPREHENSIVE
Global Time Limit(seconds)         : 3600
Per-SQL Time Limit(seconds)        : 1200
Completion Status                  : COMPLETED
Started at                         : 09/28/2013 06:00:02
Completed at                       : 09/28/2013 06:00:06
Number of Candidate SQLs           : 0
Cumulative Elapsed Time of SQL (s) : 0
-------------------------------------------------------------

PL/SQL procedure successfully completed.
 
SET_AUTO_TUNING_TASK_PARAMETER
Similar to set_tuning_task_parameter, but used for the reserved auto tuning task. Only SYS can set them.
Overload 1
dbms_auto_sqltune.set_auto_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);
exec dbms_auto_sqltune.set_auto_tuning_task_parameter('TEST_EXECUTE', 'FULL);
Overload 2 dbms_auto_sqltune.set_auto_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN NUMBER);
exec dbms_auto_sqltune.set_auto_tuning_task_parameter('DAYS_TO_EXPIRE', 90);

Related Topics
Autotrace
Explain Plan
DBMS_ADVISOR
DBMS_HPROF
DBMS_PROFILER
DBMS_SPM
DBMS_SPM_INTERNAL
DBMS_SQLTUNE
DBMS_XPLAN
Outlines
Packages
Tuning
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved