Result Details

The Result Details section represents a drill-down into the performance of SQL statements that appears in the Result Summary section of the report. Use the information in this section to investigate why the performance of a particular SQL statement regressed.

This section will contain an entry of every SQL statement processed in the SQL performance impact analysis. Each entry is organized into the following subsections:

SQL Details

This section of the report summarizes the SQL statement, listing its information and execution details.

Example 6-5 shows the SQL Details subsection of a sample report.

In Example 6-5, the report summarizes the regressed SQL statement whose ID is 2wtgxbjz6u2by and corresponding object ID is 204.

Example 6-5 SQL Details

SQL Details:
-----------------------------
 Object ID            : 204
 Schema Name          : APPS
 SQL ID               : 2wtgxbjz6u2by
 Execution Frequency  : 1
 SQL Text             : SELECT /* my_query_14_scott */ /*+ ORDERED INDEX(t1)
                        USE_HASH(t1) */ 'B' || t2.pg_featurevalue_05_id
                        pg_featurevalue_05_id, 'r' || t4.elementrange_id
                        pg_featurevalue_15_id, 'G' || t5.elementgroup_id
                        pg_featurevalue_01_id, 'r' || t6.elementrange_id . . .
.
.
.
---------------------------------------------------------------------------------------------

Execution Statistics

The Execution Statistics subsection compares execution statistics of the SQL statement from the pre-change and post-change executions and then summarizes the findings.

Example 6-6 shows the Execution Statistics subsection of a sample report.

Example 6-6 Execution Statistics

Execution Statistics:
-----------------------------
---------------------------------------------------------------------------------------
|              | Impact on | Value   | Value   | Impact     | % Workload | % Workload |
| Stat Name    | Workload  | Before  | After   | on SQL     | Before     | After      |
---------------------------------------------------------------------------------------
| elapsed_time |    -95.54%|  36.484 | 143.161 |   -292.39% |     32.68% |     94.73% |
| parse_time   |    -12.37%|    .004 |    .062 |     -1450% |       .85% |     11.79% |
| exec_elapsed |    -95.89%|   36.48 | 143.099 |   -292.27% |     32.81% |     95.02% |
| exec_cpu     |    -19.73%|  36.467 |  58.345 |    -59.99% |     32.89% |     88.58% |
| buffer_gets  |    -10.08%| 1653012 | 2160529 |     -30.7% |     32.82% |     82.48% |
| cost         |     12.17%|   11224 |    2771 |     75.31% |     16.16% |      4.66% |
| reads        |  -1825.72%|    4091 |  455280 | -11028.82% |     16.55% |     96.66% |
| writes       |     -1500%|       0 |      15 |     -1500% |         0% |       100% |
| rows         |           |     135 |     135 |            |            |            |
---------------------------------------------------------------------------------------

Notes:
-----------------------------
Before Change:
1. The statement was first executed to warm the buffer cache.
2. Statistics shown were averaged over next 9 executions.

After Change:
1. The statement was first executed to warm the buffer cache.
2. Statistics shown were averaged over next 9 executions.

Findings (2):
-----------------------------
1. The performance of this SQL has regressed.
2. The structure of the SQL execution plan has changed.
---------------------------------------------------------------------------------------------

Execution Plans

The Execution Plans subsection displays the pre-change and post-change execution plans for the SQL statement. In cases when the performance regressed, this section also contains findings on root causes and symptoms.

Example 6-7 shows the Execution Plans subsection of a sample report.

Example 6-7 Execution Plans

Execution Plan Before Change:
-----------------------------
 Plan Id          : 1
 Plan Hash Value  : 3412943215
 
----------------------------------------------------------------------------------------------------------
| Id   | Operation                           | Name                | Rows   | Bytes   | Cost  | Time     |
----------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                    |                     |      1 |     126 | 11224 | 00:02:15 |
|    1 |   HASH GROUP BY                     |                     |      1 |     126 | 11224 | 00:02:15 |
|    2 |    NESTED LOOPS                     |                     |      1 |     126 | 11223 | 00:02:15 |
|  * 3 |     HASH JOIN                       |                     |      1 |     111 | 11175 | 00:02:15 |
|  * 4 |      TABLE ACCESS FULL              | LU_ELEMENTGROUP_REL |      1 |      11 |   162 | 00:00:02 |
|  * 5 |      HASH JOIN                      |                     |    487 |   48700 | 11012 | 00:02:13 |
|    6 |       MERGE JOIN                    |                     |     14 |     924 |  1068 | 00:00:13 |
|    7 |        SORT JOIN                    |                     |   5391 |  274941 |  1033 | 00:00:13 |
|  * 8 |         HASH JOIN                   |                     |   5391 |  274941 |   904 | 00:00:11 |
|  * 9 |          TABLE ACCESS FULL          | LU_ELEMENTGROUP_REL |    123 |    1353 |   175 | 00:00:03 |
| * 10 |          HASH JOIN                  |                     |   5352 |  214080 |   729 | 00:00:09 |
| * 11 |           TABLE ACCESS FULL         | LU_ITEM_293         |   5355 |  128520 |    56 | 00:00:01 |
| * 12 |           TABLE ACCESS FULL         | ADM_PG_FEATUREVALUE |   1629 |   26064 |   649 | 00:00:08 |
| * 13 |        FILTER                       |                     |        |         |       |          |
| * 14 |         SORT JOIN                   |                     |      1 |      15 |    36 | 00:00:01 |
| * 15 |          TABLE ACCESS FULL          | LU_ELEMENTRANGE_REL |      1 |      15 |    35 | 00:00:01 |
|   16 |       INLIST ITERATOR               |                     |        |         |       |          |
| * 17 |        TABLE ACCESS BY INDEX ROWID  | FACT_PD_OUT_ITM_293 | 191837 | 6522458 |  9927 | 00:02:00 |
|   18 |         BITMAP CONVERSION TO ROWIDS |                     |        |         |       |          |
| * 19 |          BITMAP INDEX SINGLE VALUE  | FACT_274_PER_IDX    |        |         |       |          |
| * 20 |     TABLE ACCESS FULL               | LU_ELEMENTRANGE_REL |      1 |      15 |    49 | 00:00:01 |
----------------------------------------------------------------------------------------------------------
.
.
.

Execution Plan After Change:
-----------------------------
 Plan Id          : 102
 Plan Hash Value  : 1923145679
 
------------------------------------------------------------------------------------------------------
| Id   | Operation                           | Name                | Rows | Bytes  | Cost | Time     |
------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                    |                     |    1 |    126 | 2771 | 00:00:34 |
|    1 |   HASH GROUP BY                     |                     |    1 |    126 | 2771 | 00:00:34 |
|    2 |    NESTED LOOPS                     |                     |    1 |    126 | 2770 | 00:00:34 |
|  * 3 |     HASH JOIN                       |                     |    1 |    111 | 2722 | 00:00:33 |
|  * 4 |      HASH JOIN                      |                     |    1 |    100 | 2547 | 00:00:31 |
|  * 5 |       TABLE ACCESS FULL             | LU_ELEMENTGROUP_REL |    1 |     11 |  162 | 00:00:02 |
|    6 |       NESTED LOOPS                  |                     |      |        |      |          |
|    7 |        NESTED LOOPS                 |                     |  484 |  43076 | 2384 | 00:00:29 |
|  * 8 |         HASH JOIN                   |                     |   14 |    770 |  741 | 00:00:09 |
|    9 |          NESTED LOOPS               |                     |    4 |    124 |  683 | 00:00:09 |
| * 10 |           TABLE ACCESS FULL         | LU_ELEMENTRANGE_REL |    1 |     15 |   35 | 00:00:01 |
| * 11 |           TABLE ACCESS FULL         | ADM_PG_FEATUREVALUE |    4 |     64 |  649 | 00:00:08 |
| * 12 |          TABLE ACCESS FULL          | LU_ITEM_293         | 5355 | 128520 |   56 | 00:00:01 |
|   13 |         BITMAP CONVERSION TO ROWIDS |                     |      |        |      |          |
| * 14 |          BITMAP INDEX SINGLE VALUE  | FACT_274_ITEM_IDX   |      |        |      |          |
| * 15 |        TABLE ACCESS BY INDEX ROWID  | FACT_PD_OUT_ITM_293 |   36 |   1224 | 2384 | 00:00:29 |
| * 16 |      TABLE ACCESS FULL              | LU_ELEMENTGROUP_REL |  123 |   1353 |  175 | 00:00:03 |
| * 17 |     TABLE ACCESS FULL               | LU_ELEMENTRANGE_REL |    1 |     15 |   49 | 00:00:01 |
------------------------------------------------------------------------------------------------------