Oracle DBMS_AUTO_SQLTUNE
Version 19.3

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 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.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                        : 23
Current Execution                      : EXEC_427
Execution Type                         : TUNE SQL
Scope                                  : COMPREHENSIVE
Global Time Limit(seconds)             : 3600
Per-SQL Time Limit(seconds)            : 1200
Completion Status                      : COMPLETED
Started at                             : 12/04/2018 17:21:21
Completed at                           : 12/04/2018 17:21:22
Number of Candidate SQLs               : 11
Cumulative Elapsed Time of SQL (s)     : 5

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

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

-------------------------------------------------------------------------------
Statements WITHOUT Results Ordered by Object ID
-------------------------------------------------------------------------------
Object ID                              : 109
Schema Name                            : UWCLASS
Container Name                         : PDBDEV
SQL ID                                 : cuucmj3xj2pzq
SQL Text       : INSERT INTO topfreq
                 SELECT level AS recid,
                 CASE WHEN level <= 9990 THEN TRUNC(DBMS_RANDOM.value(1,10))
                 ELSE level
                 END AS rec_type, 'A'
                 FROM dual
                 CONNECT BY level <= 10000

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

-------------------------------------------------------------------------------
Object ID                              : 110
Schema Name                            : UWCLASS
Container Name                         : PDBDEV
SQL ID                                 : 1pka557u4cahz
SQL Text       : INSERT INTO topfreq
                 SELECT level AS recid,
                 CASE WHEN level <= 9990 THEN TRUNC(dbms_random.value(1,10))
                 ELSE level
                 END AS rec_type, dbms_random.string('x', 30)
                 FROM dual
                 CONNECT BY level <= 10000

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_345" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID                              : 111
Schema Name                            : C##SH
Container Name                         : CDB$ROOT
SQL ID                                 : f7n9r4s6af9w6
SQL Text       : INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "SH_TEST" ("PROD_ID",
                 "CUST_ID", "TIME_ID", "CHANNEL_ID", "PROMO_ID",
                 "QUANTITY_SOLD", "AMOUNT_SOLD") VALUES (:1, :2, :3, :4, :5, :6, :7)

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_345" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID                              : 112
Schema Name                            : MDSYS
Container Name                         : CDB$ROOT
SQL ID                                 : 6ahabs25q8dj3
SQL Text       : select column_name from sys.dba_tab_columns
                 where owner=:1 and table_name=:2

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently. See
task execution "EXEC_406" for the most recent tuning results.
-------------------------------------------------------------------------------
Object ID                              : 113
Schema Name                            : C##SH
Container Name                         : CDB$ROOT
SQL ID                                 : 6z21qxb25yarb
SQL Text       : INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "COSTS" ("PROD_ID",
                 "TIME_ID", "PROMO_ID", "CHANNEL_ID", "UNIT_COST",
                 "UNIT_PRICE") VALUES (:1, :2, :3, :4, :5, :6)

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

-------------------------------------------------------------------------------
Object ID                              : 114
Schema Name                            : C##OE
Container Name                         : CDB$ROOT
SQL ID                                 : 017znv6cah5q4
SQL Text       : SELECT COUNT(*) FROM SYS.EXU8FUL WHERE ROLE= 'IMP_FULL_DATABASE'

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

-------------------------------------------------------------------------------
Object ID                              : 115
Schema Name                            : C##OE
Container Name                         : CDB$ROOT
SQL ID                                 : 1xzd0ptunv1m6
SQL Text       : SELECT TVOID,HASH,STATUS,TYPEID,ROOTTOID FROM SYS.IMP9TVOID
                 WHERE TOWNER = :1 AND TNAME = :2

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

-------------------------------------------------------------------------------
Object ID                              : 116
Schema Name                            : MDSYS
Container Name                         : CDB$ROOT
SQL ID                                 : 9m6wj5cgx5sdg
SQL Text       : select sdo_geor_def.listAllGeoRasterFieldsStr(:1,:2)
                 from SYS.DUAL

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

-------------------------------------------------------------------------------
Object ID                              : 117
Schema Name                            : C##SH
Container Name                         : CDB$ROOT
SQL ID                                 : f7mu06dsf6ksc
SQL Text      : SELECT country, prod, year, s
                FROM sales_view_ref
                MODEL
                PARTITION BY (country)
                DIMENSION BY (prod, year)
                MEASURES (sale s)
                IGNORE NAV
                UNIQUE DIMENSION
                RULES UPSERT SEQUENTIAL ORDER ( s['Mouse Pad', 2002] =
                PRESENTNNV(s['Mouse Pad', 2002], s['Mouse Pad', 2002], 10))
                ORDER BY country, prod, year

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

-------------------------------------------------------------------------------
Object ID                              : 118
Schema Name                            : SYS
Container Name                         : CDB$ROOT
SQL ID                                 : fy3ha8grgxvw8
SQL Text       : SELECT dbms_auto_report.report_repository_list_xml(SYSDATE-1)
                 FROM dual

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

-------------------------------------------------------------------------------
Object ID                              : 119
Schema Name                            : UWCLASS
Container Name                         : PDBDEV
SQL ID                                 : 3n65rx3bqffd7
SQL Text       : SELECT * FROM TABLE(dbms_xplan.display)

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- This statement was skipped because it has already been tuned recently.
See task execution "EXEC_278" for the most recent tuning results.
-------------------------------------------------------------------------------
 
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 18c
What's New In 19c

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