Skip to content

Get Auto SQL Tuning Task Report

  • by

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.

Manually Get SQL Tuning Report

To get the most recent report of SYS_AUTO_SQL_TUNING_TASK, you need to login as SYS. 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.

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.

Data Grid HugeClob - Toad for Oracle

Data Grid HugeClob - Toad for Oracle

Just double click it to view the whole report.

Leave a Reply

Your email address will not be published. Required fields are marked *