Oracle DBMS_AUTO_SQLTUNE
Version 19.3

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
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 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                        : 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 18cR3
What's New In 19cR3

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