SYS_AUTO_SQL_TUNING_TASK Report

SYS_AUTO_SQL_TUNING_TASK

Sometimes, you don't have to run SQL tuning report for a specific statement, because the automatic SQL tuning job (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 manully by a simple query.

There're 2 ways to access the report of SYS_AUTO_SQL_TUNING_TASK.

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.

select dbms_auto_sqltune.report_auto_tuning_task from dual;

Also, I recommend that you use GUI tools like SQL developer, Toad for Oracle or PLSQL developer to get the full report.

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.

select dbms_sqltune.report_tuning_task( 'SYS_AUTO_SQL_TUNING_TASK' ) report from dual;

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.

SYS_AUTO_SQL_TUNING_TASK Report