General Information

Task Information:
Workload Information:
Task Name : gark_spa_task
Task Owner : SCOTT
Description :
SQL Tuning Set Name : gark_sts
SQL Tuning Set Owner : SCOTT
Total SQL Statement Count : 1

Execution Information:
Execution Name : gark_spa_tark_compare
Execution Type : COMPARE PERFORMANCE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 08/15/2007 07:15:13
Last Updated : 08/15/2007 07:15:14
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Analysis Information:
Comparison Metric: BUFFER_GETS
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%
Before Change Execution:
After Change Execution:
Execution Name : gark_spa_task_before
Execution Type : TEST EXECUTE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 08/15/2007 06:39:14
Last Updated : 08/15/2007 06:39:16
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0
Execution Name : gark_spa_task_after
Execution Type : TEST EXECUTE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 08/15/2007 07:08:39
Last Updated : 08/15/2007 07:08:39
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Report Summary

Projected Workload Change Impact:
Overall Impact : 91.3%
Improvement Impact : 91.3%
Regression Impact : 0%

SQL Statement Count
SQL Category SQL Count Plan Change Count
Overall 1 1
Improved 1 1

Projected Workload Performance Distribution

Bucket 		Cumulative Perf.
Before Change
(%) 		Cumulative Perf.
After Change
(%)
< = 4 0 0% 4 100%
< = 64 46 100% 0 0%


Single SQL Statement Execution Count Distribution

Bucket 		SQL Count
Before Change
(%) 		SQL Count
After Change
(%)
< = 4 0 0% 1 100%
< = 64 1 100% 0 0%

SQL Statements Sorted by their Absolute Value of Change Impact on the Workload

object_id
sql_id 		Impact on
Workload 		Metric
Before 		Metric
After 		Impact
on SQL 		% Workload
Before 		% Workload
After 		Plan
Change
4 683kdkrs2dmrk 91.3% 46 4 91.3% 100% 100% y


Report Details: SQL Statements Sorted by their Absolute Value of Change Impact on the Workload

SQL Details:
Object ID : 4
Schema Name : SCOTT
SQL ID : 683kdkrs2dmrk
Execution Frequency : 1
SQL Text : select a.id, b.id from gark a, gark b where a.id=b.id and b.id=500

Execution Statistics:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL 		% Workload
Before 		% Workload
After
elapsed_time 100% .002 0 100% 100% 0%
parse_time 100% .066 0 100% 100% 0%
cpu_time 0% 0 0 0% 0% 0%
buffer_gets 91.3% 46 4 91.3% 100% 100%
cost 93.33% 15 1 93.33% 100% 100%
reads -100% 0 1 -100% 0% 100%
writes 0% 0 0 0% 0% 0%
rows % 1 1 % % %

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


Execution Plan Before Change:
Plan Id : 1
Plan Hash Value : 2625395012

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 26 15 00:00:01
* 1 . HASH JOIN 1 26 15 00:00:01
* 2 .. TABLE ACCESS FULL GARK 1 13 7 00:00:01
* 3 .. TABLE ACCESS FULL GARK 1 13 7 00:00:01

Predicate Information (identified by operation id):

Note
- dynamic sampling used for this statement

Execution Plan After Change:
Plan Id : 2
Plan Hash Value : 4244727581

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1 26 1 00:00:01
1 . NESTED LOOPS 1 26 1 00:00:01
* 2 .. INDEX UNIQUE SCAN GARK_IDX 1 13 1 00:00:01
* 3 .. INDEX UNIQUE SCAN GARK_IDX 1 13 0 00:00:01

Predicate Information (identified by operation id):