Oracle 12cR2 DBMS_AUTO

Oracle DBMS_AUTO_SQLTUNE Version 12.2.0.1

General Information

Library Note

Morgan's Library Page Header

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. 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.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.

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

REPORT_AUTO_TUNING_TASK

SET_AUTO_TUNING_TASK_PARAMETER

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);