SQL Performance Analyzer

set timing on
--test tables
create table tmp1 as select d.* from dba_objects d, dba_objects d1 where rownum <= 1000000;
create index idx1_tmp1 on tmp1(object_id);
--test before
select * from tmp1 where object_id = 1;
--SQL Performance Analyzer
exec DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'sqlset_name_test');
DECLARE
   cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
   OPEN cur FOR
      SELECT VALUE(P)
        FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''TESTU'' ',
               NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
   DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'sqlset_name_test', populate_cursor => cur);
END;
/
VARIABLE tmp VARCHAR2(32767);
EXEC :tmp := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'sqlset_name_test',task_name => 'task_name_test');
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'task_name_test', execution_type => 'TEST EXECUTE', execution_name => 'execution_name_test_before');
--changes
alter index idx1_tmp1 invisible;
--test after
select * from tmp1 where object_id = 1;
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'task_name_test', execution_type => 'TEST EXECUTE', execution_name => 'execution_name_test_after');
--metrics
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'task_name_test', execution_type => 'COMPARE PERFORMANCE', execution_name => 'execution_name_test_compare', execution_params=>dbms_advisor.arglist('comparison_metric', 'elapsed_time', 'execution_name1', 'execution_name_test_before', 'execution_name2', 'execution_name_test_after'), execution_desc=> 'Compare Elapsed time');
rem SELECT metric_name FROM v$sqlpa_metric;
VAR v_clob   CLOB;
EXEC :v_clob := DBMS_SQLPA.REPORT_ANALYSIS_TASK('task_name_test','text', 'typical', 'summary');
SET LONG 100000 LONGCHUNKSIZE 100000 LINESIZE 300
set pagesize 3000
PRINT :v_clob
--cleanup
exec DBMS_SQLTUNE.DROP_TUNING_TASK(task_name => 'task_name_test');
exec DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'sqlset_name_test');

--output
Table created.

Elapsed: 00:00:22.92

Index created.

Elapsed: 00:00:09.23

no rows selected

Elapsed: 00:00:00.11

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.79

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.47

Index altered.

Elapsed: 00:00:00.06

no rows selected

Elapsed: 00:00:01.48

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.08

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.16

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.60

V_CLOB
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

General Information
---------------------------------------------------------------------------------------------

 Task Information:                              Workload Information:
 ---------------------------------------------  ---------------------------------------------
  Task Name    : task_name_test                  SQL Tuning Set Name        : sqlset_name_test
  Task Owner   : TESTU                           SQL Tuning Set Owner       : TESTU
  Description  :                                 Total SQL Statement Count  : 9

Execution Information:
---------------------------------------------------------------------------------------------
  Execution Name             : execution_name_test_compare    Started             : 10/23/2015 21:26:10
  Execution Type             : COMPARE PERFORMANCE            Last Updated        : 10/23/2015 21:26:10
  Description                : Compare Elapsed time           Global Time Limit   : UNLIMITED
  Scope                      : COMPREHENSIVE                  Per-SQL Time Limit  : UNUSED
  Status                     : COMPLETED                      Number of Errors    : 2
  Number of Unsupported SQL  : 2

Analysis Information:
---------------------------------------------------------------------------------------------
 Before Change Execution:                            After Change Execution:
 ---------------------------------------------       ---------------------------------------------
  Execution Name      : execution_name_test_before    Execution Name      : execution_name_test_after
  Execution Type      : TEST EXECUTE                  Execution Type      : TEST EXECUTE
  Scope               : COMPREHENSIVE                 Scope               : COMPREHENSIVE
  Status              : COMPLETED                     Status              : COMPLETED
  Started             : 10/23/2015 21:26:02           Started             : 10/23/2015 21:26:06
  Last Updated        : 10/23/2015 21:26:05           Last Updated        : 10/23/2015 21:26:10
  Global Time Limit   : UNLIMITED                     Global Time Limit   : UNLIMITED
  Per-SQL Time Limit  : UNUSED                        Per-SQL Time Limit  : UNUSED
  Number of Errors    : 2                             Number of Errors    : 2

 ---------------------------------------------
 Comparison Metric: ELAPSED_TIME
 ------------------
 Workload Impact Threshold: 1%
 --------------------------
 SQL Impact Threshold: 1%
 ----------------------

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
 Overall Impact      :  -34.67%
 Improvement Impact  :  0%
 Regression Impact   :  -34.67%

SQL Statement Count
-------------------------------------------
 SQL Category  SQL Count  Plan Change Count
 Overall               9                  1
 Regressed             2                  1
 Unchanged             3                  0
 with Errors           2                  0
 Unsupported           2                  0

Top 5 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------
|           |               | Impact on | Execution | Metric | Metric | Impact   | Plan   |
| object_id | sql_id        | Workload  | Frequency | Before | After  | on SQL   | Change |
-------------------------------------------------------------------------------------------
|        23 | 4n4nta6vd2j19 |   -33.49% |         1 |     24 |  82986 | -345675% | y      |
|        22 | 24fh3j5b4puv7 |    -1.18% |         1 | 118627 | 121550 |   -2.46% | n      |
|        28 | fb05dj6w5zm6u |      .53% |         1 |  96944 |  95641 |    1.34% | n      |
|        24 | 7ckyb6bhazww4 |      .08% |         2 |  16027 |  15933 |     .59% | n      |
|        27 | ax51s81797wdk |        0% |         2 |     46 |     48 |   -4.35% | n      |
-------------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------



PL/SQL procedure successfully completed.

Elapsed: 00:00:00.15

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL> select sql_text from v$sql where sql_id = '4n4nta6vd2j19';

SQL_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select * from tmp1 where object_id = 1

Elapsed: 00:00:00.08
SQL>