Generating SQL Performance Analyzer Reports Using APIs

This section describes how to generate a SQL Performance Analyzer report using the DBMS_WORKLOAD_REPLAY package.

To generate a SQL Performance Analyzer report, use the DBMS_WORKLOAD_REPLAY.COMPARE_SQLSET_REPORT procedure:

BEGIN
  DBMS_WORKLOAD_REPLAY.COMPARE_SQLSET_REPORT (
                           replay_id1 => 12,
                           replay_id2 => 17,
                           format => DBMS_WORKLOAD_CAPTURE.TYPE_HTML,
                           result => :report_bind);
END;
/

In this example, the COMPARE_SQLSET_REPORT procedure generates a SQL Performance Analyzer report in HTML format that compares a SQL tuning set captured during the workload replay with a replay ID of 12 to a SQL tuning set captured during workload replay with an ID of 17.

The COMPARE_SQLSET_REPORT procedure in this example uses the following parameters:

  • The replay_id1 parameter specifies the numerical identifier of the workload replay after change for which the reported will be generated. This parameter is required.

  • The replay_id2 parameter specifies the numerical identifier of the workload replay after change for which the reported will be generated. If unspecified, the comparison will be performed with the workload capture.

  • The format parameter specifies the report format. Valid values include DBMS_WORKLOAD_CAPTURE.TYPE_HTML for HTML, DBMS_WORKLOAD_CAPTURE.TYPE_XML for XML, and DBMS_WORKLOAD_CAPTURE.TYPE_TEXT for text. This parameter is required.

  • The result parameter specifies the output of the report.

For information about how to interpret the SQL Performance Analyzer report, see "Reviewing the SQL Performance Analyzer Report in Command-Line" .

See Also: