=== Lab wall-clock comparison ===

RUN_LABEL  SQL_ID        PLAN_HASH_VALUE EXECUTIONS ELAPSED_SECONDS BEGIN_SNAP   END_SNAP
---------- ------------- --------------- ---------- --------------- ---------- ----------
BAD        bk3p70gc73zat      2392565234         75           3.700          3          4
GOOD       dmqkpmc08m1jv      3093251051         75           0.020          5          6

2 rows selected.


=== AWR per-execution comparison ===

RUN_LABEL  SQL_ID        PLAN_HASH_VALUE EXECUTIONS AVG_ELAPSED_MS AVG_CPU_MS AVG_BUFFER_GETS AVG_DISK_READS
---------- ------------- --------------- ---------- -------------- ---------- --------------- --------------
BAD        bk3p70gc73zat      2392565234         75         49.148      44.97            9688              0
GOOD       dmqkpmc08m1jv      3093251051         75           .168       .189               3              0

2 rows selected.


=== Current cursor statistics ===

RUN_LABEL  SQL_ID        PLAN_HASH_VALUE EXECUTIONS AVG_ELAPSED_MS AVG_BUFFER_GETS AVG_DISK_READS ROWS_PROCESSED
---------- ------------- --------------- ---------- -------------- --------------- -------------- --------------
BAD        bk3p70gc73zat      2392565234         75         49.148            9688              0             75
GOOD       dmqkpmc08m1jv      3093251051         75           .168               3              0             75

2 rows selected.





1 row selected.





1 row selected.


=== Original FULL plan with actual row-source statistics ===
old   4:            '&&bad_sql_id', null, 'ALLSTATS LAST +PEEKED_BINDS'
new   4:            'bk3p70gc73zat', null, 'ALLSTATS LAST +PEEKED_BINDS'

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bk3p70gc73zat, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics monitor full(o)
qb_name(DBACORNER_LAB_BAD) */ COUNT(*) FROM DBC_LAB_ORDERS O WHERE
O.CUSTOMER_ID = 4242 AND O.STATUS = 'COMPLETE'

Plan hash value: 2392565234

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |      1 |        |      1 |00:00:00.05 |    9688 |
|   1 |  SORT AGGREGATE    |                |      1 |      1 |      1 |00:00:00.05 |    9688 |
|*  2 |   TABLE ACCESS FULL| DBC_LAB_ORDERS |      1 |     30 |     48 |00:00:00.04 |    9688 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("O"."CUSTOMER_ID"=4242 AND "O"."STATUS"='COMPLETE'))


21 rows selected.


=== Improved INDEX plan with actual row-source statistics ===
old   4:            '&&good_sql_id', null, 'ALLSTATS LAST +PEEKED_BINDS'
new   4:            'dmqkpmc08m1jv', null, 'ALLSTATS LAST +PEEKED_BINDS'

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dmqkpmc08m1jv, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics monitor
qb_name(DBACORNER_LAB_GOOD) */ COUNT(*) FROM DBC_LAB_ORDERS O WHERE
O.CUSTOMER_ID = 4242 AND O.STATUS = 'COMPLETE'

Plan hash value: 3093251051

--------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                          |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE   |                          |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN| DBC_LAB_ORDERS_CUST_ST_I |      1 |     46 |     48 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("O"."CUSTOMER_ID"=4242 AND "O"."STATUS"='COMPLETE')


21 rows selected.


Use each run's BEGIN_SNAP and END_SNAP with awrsqrpt.sql.

RUN_LABEL  NEXT_STEP
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------------
BAD        @?/rdbms/admin/awrsqrpt.sql  -- SQL_ID=bk3p70gc73zat, begin_snap=3, end_snap=4
GOOD       @?/rdbms/admin/awrsqrpt.sql  -- SQL_ID=dmqkpmc08m1jv, begin_snap=5, end_snap=6

2 rows selected.

