Download Oracle Database Real Application Testing User's Guide

Comparing SQL Trials Using APIs
| plan_count
|
|
3 |
2 |
|
--------------------------------------------------------Findings (2):
----------------------------1. The performance of this SQL has improved.
2. The structure of the SQL execution plan has changed.
Plan Execution Statistics (average):
-----------------------------------------------------------------------------------------------------------------------| Statistic Name | Plans Before Change
| Plans After Change
|
---------------------------------------------------------------------------------| plan hash value | 440231712 571903972 3634526668 | 571903972 3634526668
|
| --------------- | --------- --------- ---------- | --------- ---------|
| schema name
| APPS1
APPS2
APPS2
| APPS2
APPS2
|
| executions
| 7
5
10
| 10
10
|
| cost
| 2
1
2
| 1
2
|
| elapsed_time
| .108429
.000937
.00491
| .000503
.003195
|
| cpu_time
| .00957
.0012
.0032
| .0005
.0032
|
| buffer_gets
| 18
0
5
| 0
5
|
| reads
| 0
0
0
| 0
0
|
| writes
| 0
0
0
| 0
0
|
| rows
| 0
0
0
| 0
0
|
---------------------------------------------------------------------------------Execution Plans Before Change:
----------------------------Plan Hash Value : 440231712
--------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost | Time
|
--------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
|
|
2 |
|
| 1 |
PX COORDINATOR
|
|
|
|
|
|
| 2 |
PX SEND QC (RANDOM) | :TQ10000 |
1 |
87 |
2 | 00:00:01 |
| 3 |
PX BLOCK ITERATOR |
|
1 |
87 |
2 | 00:00:01 |
| 4 |
TABLE ACCESS FULL | EMP
|
1 |
87 |
2 | 00:00:01 |
--------------------------------------------------------------------------Note
----- dynamic sampling used for this statement
Plan Hash Value : 571903972
---------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost | Time
|
---------------------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
|
|
1 |
|
| 1 |
TABLE ACCESS BY INDEX ROWID | EMP
|
1 |
87 |
1 |00:00:01|
| 2 |
INDEX UNIQUE SCAN
| MY_EMP_IDX |
1 |
|
0 |
|
---------------------------------------------------------------------------------Plan Hash Value : 3634526668
-------------------------------------------------------------------| Id | Operation
| Name | Rows | Bytes | Cost | Time
|
-------------------------------------------------------------------| 0 | SELECT STATEMENT
|
|
|
|
2 |
|
| 1 |
TABLE ACCESS FULL | EMP |
1 |
87 |
2 | 00:00:01 |
-------------------------------------------------------------------Note
----- dynamic sampling used for this statement
Executions Plan After Change:
-----------------------------
Comparing SQL Trials 6-21