Sometimes, you don't have to run SQL tuning report for a specific statement, because the automatic SQL tuning job (task name: SYS_AUTO_SQL_TUNING_TASK) could have identified long-running statements and aleady run SQL tuning report for you.
The best way to view the automatic SQL tuning advisor's report is to use Oracle Enterprise Manager Cloud Control. However, if you don't have such tool, you can also retrieve the report manually by simple queries introduced in this post.
There're 2 use cases to get the report.
Newest Report
There're 2 ways to access the newest report of SYS_AUTO_SQL_TUNING_TASK.
Using DBMS_AUTO_SQLTUNE
To get the most recent report of SYS_AUTO_SQL_TUNING_TASK, you need to login as SYS and use DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK to get it.
Please note that, if this is a multitenant database, the container should be CDB$ROOT.
Also, I recommend that you use GUI tools like SQL developer, Toad for Oracle or PLSQL developer to get the full report.
Using DBMS_SQLTUNE
Using DBMS_SQLTUNE.REPORT_TUNING_TASK to get the auto SQL tuning report is also working as long as you specify the task name.
Since the returned text is a CLOB, text-based tools like SQL*Plus may not be good enough to display the report. That's why I recommend GUI tools to display it.
For example, "HUGECLOB" is displayed in the data grid if you were using Toad for Oracle.
Just double click it to view the whole report.
Historical Report
Historical SQL tuning reports can make us to focus on a specific period of time. To retrieve previously executed SQL tuning reports, we should list and know the execution names of task SYS_AUTO_SQL_TUNING_TASK in the history first.
Query DBA_ADVISOR_EXECUTIONS
To know the execution history of task SYS_AUTO_SQL_TUNING_TASK, we query the view, DBA_ADVISOR_EXECUTIONS in the root container.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> column execution_name format a15;
SQL> select execution_name, execution_start, execution_end from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and execution_start is not null and execution_end is not null order by execution_end desc nulls last;
EXECUTION_NAME EXECUTION_START EXECUTION_END
--------------- ------------------- -------------------
EXEC_34525 2024-07-21 06:00:05 2024-07-21 07:00:07
EXEC_34460 2024-07-20 06:00:00 2024-07-20 07:00:05
EXEC_34431 2024-07-19 22:00:05 2024-07-19 23:00:18
EXEC_34365 2024-07-18 22:00:05 2024-07-18 23:00:06
EXEC_34296 2024-07-17 22:00:06 2024-07-17 23:00:14
EXEC_34028 2024-07-14 06:00:00 2024-07-14 07:02:39
EXEC_33947 2024-07-13 06:00:05 2024-07-13 07:10:14
EXEC_33774 2024-07-10 22:00:05 2024-07-10 23:00:52
EXEC_33690 2024-07-09 22:00:00 2024-07-09 23:03:16
EXEC_33625 2024-07-08 22:00:05 2024-07-08 23:00:56
EXEC_33492 2024-07-07 06:00:05 2024-07-07 07:00:06
...
The result shows all recent executions of auto SQL tuning, so we can choose the interval we're interested.
Get Report for a Period of Time
Same as above, we can use DBMS_AUTO_SQLTUNE package to get the report, except that we need to specify 2 parameters, BEGIN_EXEC and END_EXEC.
From a Previous Execution To the Most Recent One
In this case, we retrieve an interval from a specific execution to the most recent one by specifying only one parameter which is BEGIN_EXEC. Without specifying the second parameter END_EXEC, it defaults to the most recent execution.
select dbms_auto_sqltune.report_auto_tuning_task('EXEC_34431') AUTO_TUNING_REPORT from dual;
All executions will output only one report for you.
By the way, if the interval spans too many executions, it may take a while to output the report. So, I would suggest that you get specific execution only.
Multiple Consecutive Executions
As you may have noticed in the above statement, a report that covers consecutive period of time with multiple executions can be retrieved in the same way:
select dbms_auto_sqltune.report_auto_tuning_task('EXEC_34296', 'EXEC_34431') AUTO_TUNING_REPORT from dual;
As long as BEGIN_EXEC is earlier than END_EXEC, you can get the right report. Otherwise, you see ORA-13665: Execution names are not correctly ordered.
Get Report for a Single Execution
In order to get the report of a specific execution, we need to specify both parameters (BEGIN_EXEC and END_EXEC) with the same execution name.
select dbms_auto_sqltune.report_auto_tuning_task('EXEC_34431', 'EXEC_34431') AUTO_TUNING_REPORT from dual;
Using DBMS_SQLTUNE.REPORT_TUNING_TASK can only get the report from a single execution in the history. For example.
select dbms_sqltune.report_tuning_task('SYS_AUTO_SQL_TUNING_TASK', execution_name => 'EXEC_34431') report from dual;
Those are how we get the report from SYS_AUTO_SQL_TUNING_TASK task.