SQL Performance Analyzer Active Report

SQL Performance Analyzer (SPA), a key feature of the Real Application Testing option introduced in Oracle Database 11g, can predict and quantify SQL performance improvements and regressions caused by system changes such as upgrading a database, adding new indexes, changing database parameters, collecting optimizer statistics, etc. SQL Performance Analyzer provides a granular view of the impact of changes on SQL execution plans and execution statistics by running the SQL statements on a production or test system in isolation before and after a change. SPA compares the SQL execution result, before and after the change, and generates a report outlining the net benefit on the SQL workload due the change as well as the set of regressed SQL statements. For regressed SQL statements, appropriate execution plan details along with recommendations to remedy them are provided.

SQL Performance Analyzer active report is a new type of interactive report powered by Enterprise Manager UI technology hosted on OTN. Other active reports are SQL Monitor and SQL Details.

Answers

How can I generate SQL Performance Analyzer (SPA) active reports?

SPA active reports can be generated directly from EM live UI while viewing SPA comparison report. There (see save/send e-mail buttons on the top right of that page), the SPA detail page can be either saved or sent by e-mail as an active report. Alternatively, the active report can be directly produced using command line by invoking the PL/SQL procedure dbms_sqlpa.report_analysis_task() using "active" as the report type. For example, the following SQL*Plus script shows how to generate an active report for a SPA task:

set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
spool spa_active.html
select dbms_sqlpa.report_analysis_task(task_name => 'my_spa_task', 
type=>'active', section => 'all') from dual;
spool off

The resulting file spa_active.html must be edited to remove the header (first line in the file) and the last line (the spool off). The resulting html file can then be viewed in any browser. The browser must have connectivity to OTN to load the active report code.