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.
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.
-------------------------------------------------------------------------------
