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
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"