Oracle DBMS_AUTO_SQLTUNE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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_ADVISOR
DBMS_SQLTUNE DBMS_SQLTUNE_LIB PRVT_SQLADV_INFRA
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

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-2014 Daniel A. Morgan All Rights Reserved