Viewing Automatic SQL Tuning Advisor Tasks in Oracle SQL Developer

I got a question from our internal mailing list…

I would like to know how I can view historical SQL tuning recommendations in SQL Developer 4. I don’t want to run the sql worksheet manually but view the automatic recommendations as the query has been running already for several days now.

I’m guessing their query hasn’t been running for several days, but has been executing multiple times over several days, so if the Automatic SQL Tuning Advisor was gonna have some advice on it, it’d be there by now.

So, we don’t have an interface for this.

What we let you do with the GUI, is create an ad-hoc SQL Tuning Advisor task for a specific query in the SQL Worksheet…

So what’s a SQL Developer user to do, other than to ask pretty-please to the product team to build an interface for the automatic reports?

Build Your Own Report

First, let’s get the advisor findings from DBA_ADVISOR_FINDINGS. That’s going to be the ‘master’ report.

Now, we’re going to feed the TASK_NAME into a child report of type PL/SQL DBMS Output using this code:

DECLARE
report clob;
 
BEGIN
 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:TASK_NAME) INTO report FROM DUAL;
  DBMS_OUTPUT.put_line('#pre#' || report || '#/pre#');
END;

In the code sample above I have the HTML PRE tags with #’s instead of < and > pairs so it would render OK here…replace those before you try to use them in your report.

It’s pretty simple. We declare a CLOB variable, fetch the report into it using the task name, and then we print it. And since we use HTML to render the DBMS_OUTPUT on this style of report, I pre and post fix an HTML PRE tag.

The report definition:

And it looks like this –

I’ve almost got it perfect, except the REPORT_TUNING_TASK() function gets upset if you send it a task name that’s not from YOUR user. So if I want to look at the SYS tasks, I have to run the report as SYS. Or if I want to see DEMO’s report, I need to login as DEMO.

I’ll be at UKOUG this week with @SQLMaria, so I’m sure she can help me figure this out, and I’ll share that with y’all here. Or feel free to fix my code for me in the comments below!

I’m writing this at 1:43 AM Eastern Time / 6:43 AM Irish Time, and I’ve had maybe an hour’s sleep…Kudos to Dublin Airport for free and easy Wi-Fi!