Oracle DBMS_AUTO_SQLTUNE
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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_HTML VARCHAR2(4) dbms_sqltune.TYPE_HTML
TYPE_TEXT VARCHAR2(4) dbms_sqltune.TYPE_TEXT
TYPE_XML VARCHAR2(3) dbms_sqltune.TYPE_XML
Dependencies
DBMS_ADVISOR DBMS_SQLTUNE_INTERNAL PRVT_SQLADV_INFRA
DBMS_SQLTUNE PRVT_ADVISOR  
Documented Yes
First Available 11.2
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.execute_auto_tuning_task;
  dbms_output.put_line(retVal);
END;
/
EXEC_426
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.execute_auto_tuning_task;
 
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: 30
Current Execution : EXEC_1601
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 3600
Per-SQL Time Limit(seconds) : 1200
Completion Status : COMPLETED
Started at : 02/10/2021 22:00:02
Completed at : 02/10/2021 22:00:11
Number of Candidate SQLs : 3
Cumulative Elapsed Time of SQL (s) : 135

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 3
Number of SQLs in the Report : 3

-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
There are no recommendations to improve the statements in the workload.

-------------------------------------------------------------------------------
Statements WITHOUT Results Ordered by Object ID
-------------------------------------------------------------------------------
Object ID : 545
Schema Name : DS$ADMIN
Container Name: TEST21P1
SQL ID :
500a9mdu4m4pt
SQL Text : SELECT * FROM AUDSYS.UNIFIED_AUDIT_TRAIL WHERE
"EVENT_TIMESTAMP"<=:1 AND "EVENT_TIMESTAMP">:2

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_1081" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 546
Schema Name :
SYS
Container Name: CDB$ROOT
SQL ID : 1c3vua9uvt9y1
SQL Text : select * from
pview

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_1143" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID : 547
Schema Name :
SYS
Container Name: CDB$ROOT
SQL ID : 0xsrr0bbhajrg
SQL Text : select position, argument_name, in_out, data_type,
type_owner, type_name, type_subname

from dba_arguments
where package_name = 'DBMS_AQ'
and object_name = 'SEEK'
order by 1

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_1481" for the most recent tuning results.
-------------------------------------------------------------------------------

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
Built-in Functions
Built-in Packages
Explain Plan
DBMS_ADVISOR
DBMS_HPROF
DBMS_PROFILER
DBMS_SPM
DBMS_SPM_INTERNAL
DBMS_SQLTUNE
DBMS_XPLAN
Outlines
Tuning
What's New In 19c
What's New In 20c-21c

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