The Result Summary section summarizes the results of the SQL Performance Analyzer task. The Result Summary section is divided into the following subsections:
The Overall Performance Statistics subsection displays statistics about the overall performance of the entire SQL workload. This section is a very important part of the SQL Performance Analyzer analysis because it shows the impact of the system change on the overall performance of the SQL workload. Use the information in this section to understand the change of the workload performance, and determine whether the workload performance will improve or degrade after making the system change.
Example 6-2 shows the Overall Performance Statistics subsection of a sample report.
This example indicates that the overall performance of the SQL workload improved by 47.94%, even though regressions had a negative impact of -10.08%. This means that if all of the regressions are fixed in this example, the overall change impact will be 58.02%. After the system change, 2 of the 101 SQL statements ran faster, while 1 ran slower. Performance of 98 statements remained unchanged.
Example 6-2 Overall Performance Statistics
Report Summary --------------------------------------------------------------------------------------------- Projected Workload Change Impact: ------------------------------------------- Overall Impact : 47.94% Improvement Impact : 58.02% Regression Impact : -10.08% SQL Statement Count ------------------------------------------- SQL Category SQL Count Plan Change Count Overall 101 6 Improved 2 2 Regressed 1 1 Unchanged 98 3 . . . ---------------------------------------------------------------------------------------------
The Performance Statistics subsection highlights the SQL statements that are the most impacted by the system change. The pre-change and post-change performance data for each SQL statement in the workload are compared based on the following criteria:
Execution frequency, or importance, of each SQL statement
Impact of the system change on each SQL statement relative to the entire SQL workload
Impact of the system change on each SQL statement
Whether the structure of the execution plan for each SQL statement has changed
Example 6-3 shows the Performance Statistics of SQL Statements subsection of a sample report. The report has been altered slightly to fit on the page.
The SQL statements are sorted in descending order by the absolute value of the net impact on the SQL workload, that is, the sort order does not depend on whether the impact was positive or negative.
Example 6-3 Performance Statistics of SQL Statements
SQL Statements Sorted by their Absolute Value of Change Impact on the Workload --------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- | | | Impact on | Execution | Metric | Metric | Impact | Plan | | object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change | --------------------------------------------------------------------------------------------- | 205 | 73s2sgy2svfrw | 29.01% | 100000 | 1681683 | 220590 | 86.88% | y | | 206 | gq2a407mv2hsy | 29.01% | 949141 | 1681683 | 220590 | 86.88% | y | | 204 | 2wtgxbjz6u2by | -10.08% | 478254 | 1653012 | 2160529 | -30.7% | y | ---------------------------------------------------------------------------------------------
The Errors subsection reports all errors that occurred during an execution. An error may be reported at the SQL tuning set level if it is common to all executions in the SQL tuning set, or at the execution level if it is specific to a SQL statement or execution plan.
Example 6-4 shows an example of the Errors subsection of a SQL Performance Analyzer report.
----------------------------------------------------------------------------------
SQL STATEMENTS WITH ERRORS
----------------------------------------------------------------------------------
SQL ID Error
------------- --------------------------------------------------------------------
47bjmcdtw6htn ORA-00942: table or view does not exist
br61bjp4tnf7y ORA-00920: invalid relational operator
----------------------------------------------------------------------------------