ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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.
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;
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.
-------------------------------------------------------------------------------
