SQL> SELECT DBMS_SQLPA.report_analysis_task(:v_task) 2 FROM dual; General Information --------------------------------------------------------------------------------------------- Task Information: Workload Information: --------------------------------------------- --------------------------------------------- Task Name : TASK_122 SQL Tuning Set Name : spa_test_sqlset Task Owner : SYS SQL Tuning Set Owner : SYS Description : Total SQL Statement Count : 5 Execution Information: --------------------------------------------------------------------------------------------- Execution Name : EXEC_122_2 Started : 10/31/2007 07:03:47 Execution Type : COMPARE PERFORMANCE Last Updated : 10/31/2007 07:03:47 Description : Global Time Limit : UNLIMITED Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED Status : COMPLETED Number of Errors : 0 Analysis Information: --------------------------------------------------------------------------------------------- Comparison Metric: ELAPSED_TIME ------------------ Workload Impact Threshold: 1% -------------------------- SQL Impact Threshold: 1% ---------------------- Before Change Execution: After Change Execution: --------------------------------------------- --------------------------------------------- Execution Name : before_change Execution Name : after_change Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE Description : Description : Scope : COMPREHENSIVE Scope : COMPREHENSIVE Status : COMPLETED Status : COMPLETED Started : 10/31/2007 06:54:01 Started : 10/31/2007 06:55:35 Last Updated : 10/31/2007 06:54:01 Last Updated : 10/31/2007 06:55:40 Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED Number of Errors : 0 Number of Errors : 0 Report Summary --------------------------------------------------------------------------------------------- Projected Workload Change Impact: ------------------------------------------- Overall Impact : 100% Improvement Impact : 100% Regression Impact : 0% SQL Statement Count ------------------------------------------- SQL Category SQL Count Plan Change Count Overall 5 5 Improved 5 5 Projected Workload Performance Distribution -------------------------------------------------- --------------------------------------------------------------- | | Cumulative Perf. | | Cumulative Perf. | | | Bucket | Before Change | (%) | After Change | (%) | --------------------------------------------------------------- | < = 1 | 0 | 0% | 0 | 0% | | < = 16 | 35 | 37.63% | 0 | 0% | | < = 32 | 17 | 18.28% | 0 | 0% | | < = 64 | 41 | 44.09% | 0 | 0% | --------------------------------------------------------------- Single SQL Statement Execution Count Distribution ------------------------------------------------------- ------------------------------------------------------ | | SQL Count | | SQL Count | | | Bucket | Before Change | (%) | After Change | (%) | ------------------------------------------------------ | < = 1 | 0 | 0% | 5 | 100% | | < = 16 | 3 | 60% | 0 | 0% | | < = 32 | 1 | 20% | 0 | 0% | | < = 64 | 1 | 20% | 0 | 0% | ------------------------------------------------------ SQL Statements Sorted by their Absolute Value of Change Impact on the Workload -------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------- | | | Impact on | Metric | Metric | Impact | % Workload | % Workload | Plan | | object_id | sql_id | Workload | Before | After | on SQL | Before | After | Change | ------------------------------------------------------------------------------------------------------- | 37 | 7k24y5pdhurxx | 44.09% | 41 | 0 | 100% | 44.09% | 0% | y | | 38 | fbsgyfwt1q29p | 18.28% | 17 | 0 | 100% | 18.28% | 0% | y | | 39 | 2j3dk2hcs8jq5 | 12.9% | 12 | 0 | 100% | 12.9% | 0% | y | | 41 | 7m2wr4twzmrkm | 12.9% | 12 | 0 | 100% | 12.9% | 0% | y | | 40 | 7vbu3057r64r0 | 11.83% | 11 | 0 | 100% | 11.83% | 0% | y | ------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------- SQL> SPOOL OFF