0

I am using OEM to view the tuning adviser report which runs as a part of daily maintenance job, for a specific query. Now my requirement is to get a single file (text/html) which will have the tuning adviser report for top-10/20 of AWR report. Can some one help me getting the report as requested.

DB version: 12.1.0.2

srinivas
  • 5
  • 2
  • 7

1 Answers1

2

You need a “task” from a SQL Tuning Advisor (STA) run to get a STA report. The code to do this for a single task follows:

SELECT DBMS_SQLTUNE.report_tuning_task(:task_name) AS recommendations FROM dual;

It should not be so difficult to get the STA report for multiple tasks, for example using SQL*Plus:

-- Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.
set linesize 210 
set pagesize 9999
set long 10000000
set time on
set timing on
SET SERVEROUTPUT ON
-- width of output should not need more than 200
column recommendations format a200

spool c:\temp\STA_reports.txt
SELECT DBMS_SQLTUNE.report_tuning_task('68vm8dtty867d_1340348426_AWR') AS recommendations FROM dual ;
prompt =================================================================
SELECT DBMS_SQLTUNE.report_tuning_task('69w2tux85a9x7_814599999_AWR') AS recommendations FROM dual ;
rem ...
spool off

So, basically, you’ll need to run the STA for the top-SQL then generate the reports as one file.

I don’t regularly use OEM as I’ve found this tool to not cover the use cases that I frequently encounter, rather, I’ll generate a Top-SQL listing from DBA_HIST_SQLSTAT directly*. One reason for this is I can subset out the SQL that does not apply to a reported performance problem; also, I can order the Top-SQL by a variety of metrics (e.g. elapsed time, cpu time, buffered gets, …). Once I have the SQL_ID’s for the SQL of interest I run them all through the SQL Tuning Advisor. Rather than examining the SQL Tuning Advisor Reports I aggregate the persisted results directly from the DBA_ADVISOR% tables. With these queries, I extract the code to implement the recommendations (e.g. SQL for gather stats, create indexes, accept SQL Profiles) directly from the DBA_ADVISOR% tables. I have presented on this at many Oracle conferences, but that solution goes far beyond the direct answer to the question.

*DBA_HIST_SQLSTAT script can be found in the answer to question "how much CPU a session consuming at a given time in oracle"

Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7
  • Thanks for your suggestion. Let me try building a script with top sqls from DBA_HIST_SQLSTAT manually and concatenate all reports. – srinivas Jan 15 '20 at 10:35
  • for a DBA_HIST_SQLSTAT script reference the question "how much CPU a session consuming at a given time in oracle": https://stackoverflow.com/questions/58820965/how-much-cpu-a-session-consuming-at-a-given-time-in-oracle/59738695?noredirect=1#comment105645877_59738695 – Roger Cornejo Jan 15 '20 at 13:29