SQL Performance Analyzer

Preamble

How to test and predict impact on your application of any system change ? What a difficult question for a developer/DBA and except comparing SQL statement by SQL statement there is no dedicated tool for this. With 11gR2 Oracle has released a new tool called SQL Performance Analyzer.

Before going further it’s worth to mention that SQL Performance Analyzer (SPA) is included in Oracle Real Application Testing (RAT) Enterprise Edition paid option.

By system changes Oracle mean (not exhaustively):

  • Database upgrades.
  • Tuning changes.
  • Schema changes.
  • Statistics gathering.
  • Initialization parameter change
  • OS or hardware changes.

Database upgrade is exactly what we will test in this blog post by simulating execution of a SQL statement in Oracle 9iR2 and then in 11gR2. Notice that same strategy can be applied to evaluate any initialization parameter, statistics (with pending statistics) changes.

Test database of this blog post is Oracle Enterprise Edition 11.2.0.2.0 running on Red Hat Enterprise Linux Server release 5.6 (Tikanga).

SQL Performance Analyzer testing

Just to show one limitation of SPA, but not of SQL Tuning Set (STS) I’m choosing a sql_id that has multiple plan with following query:

SQL> SELECT * FROM (SELECT sql_id,COUNT(DISTINCT plan_hash_value) FROM v$sql a
     WHERE EXISTS (SELECT sql_id, COUNT(*) FROM v$sql b WHERE a.sql_id=b.sql_id GROUP BY sql_id HAVING COUNT(*)>1)
     GROUP BY sql_id ORDER BY 2 DESC) WHERE rownum<=10;
 
SQL_ID        COUNT(DISTINCTPLAN_HASH_VALUE)
------------- ------------------------------
94rn6s4ba24wn                              5
9j8p0n3104sdg                              4
gv9varx8zfkq4                              4
9wbvj5pud8t2f                              4
20pm94kcsc31s                              3
afrmyr507wu03                              3
0tnssv00b0nyr                              2
1ds1kuqzkr7kn                              2
18hzyzu9945g4                              2
1290sa2814wt2                              2
 
10 ROWS selected.

Let’s choose first one and create a STS with the five sql_id, plan_hash_value pairs:

DECLARE
  cursor1 DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
  OPEN cursor1 FOR SELECT VALUE(p)
  FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''94rn6s4ba24wn''')) p;
 
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'STS01', populate_cursor => cursor1);
END;
/
 
PL/SQL PROCEDURE successfully completed.

We easily see that one SQL statement has been added to our STS even if the sql_id has five distinct explain plan:

SQL> SET lines 200
SQL> col description FOR a30
SQL> SELECT * FROM dba_sqlset;
 
        ID NAME                           OWNER                          DESCRIPTION                    CREATED   LAST_MODI STATEMENT_COUNT
---------- ------------------------------ ------------------------------ ------------------------------ --------- --------- ---------------
         1 STS01                          SYS                            STS FOR sql_id 94rn6s4ba24wn   17-NOV-11 17-NOV-11               1

Now let’s create a SPA task and associate the STS with it:

DECLARE
  task_name VARCHAR2(64);
  sts_task  VARCHAR2(64);
BEGIN
  task_name := 'Task01';
 
  sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'STS01', task_name => task_name, description => 'Task for sql_id 94rn6s4ba24wn');
END;
/
 
PL/SQL PROCEDURE successfully completed.

When executing the task you have to decide, with execution_type parameter, which kind of execution you want to perform. A standard SPA task is made of following steps:

  • Execute the task in TEST EXECUTE mode and generate a before change task report.
  • Change what you want on your database (upgrade, optimizer parameters, statistics, …), execute the task in TEST EXECUTE mode and generate an after change task report.
  • Execute the task in COMPARE PERFORMANCE mode and generate a compare performance task report.

Just to show one limitation of SPA I’ll first use the CONVERT SQLSET mode and generate the report:

SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'CONVERT SQLSET', execution_name => 'convert_sqlset');
 
PL/SQL PROCEDURE successfully completed.

You can check the task has well completed with:

SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, status
     FROM dba_advisor_executions
     WHERE task_name='Task01';
 
EXECUTION_NAME       EXECUTION_TYPE                 EXECUTION_START               EXECUTION_END                 STATUS
-------------------- ------------------------------ ----------------------------- ----------------------------- -----------
convert_sqlset       CONVERT SQLSET                 29-nov-2011 15:01:26          29-nov-2011 15:01:27          COMPLETED

Generate the report with a SQL statement like this:

SQL> SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> spool task01_before_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off

We can see in task01_convert_sqlset.html result file that with this particular execution all different plans are displayed, while in compare performance objective SPA is taking only one plan (the one it is currently parsing with information currently available). In any case comparing all plans of same sql_id would provide very complex reports that would probably be not usable…

The EXPLAIN PLAN execute mode does not provide any added value as it generates the explain plan for every SQL statement of the STS, explain plans that are also displayed in TEST EXECUTE reports.

For my test case, to simulate a database upgrade from 9iR2 to 11gR2, I first set optimizer feature to 9iR2 optimizer, execute the task in TEST EXECUTE mode and generate report, put then back optimizer parameter to default value, execute again the task in TEST EXECUTE mode and generate report and finally execute the task in COMPARE PERFORMANCE mode and generate final comparison report (the most interesting one).

We set environment variable for report generation, check optimizer value before changing and reset the task before starting:

SQL> SET LONG 999999 longchunksize 100000 linesize 200 head off feedback off echo off
SQL> show parameter optimizer_features_enable
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      11.2.0.2
 
SQL> EXEC DBMS_SQLPA.RESET_ANALYSIS_TASK('Task01');
 
PL/SQL PROCEDURE successfully completed.

We set optimizer to 9.2.0 to simulate a database upgrade situation and execute the task:

SQL> ALTER SESSION SET optimizer_features_enable='9.2.0';
 
SESSION altered.
 
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'before_change');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> spool task01_before_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off

We set back optimizer to default value and execute task again (we see that plan chosen is one of five initial plans associated with query):

SQL> ALTER SESSION SET optimizer_features_enable='11.2.0.2';
 
SESSION altered.
 
SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'TEST EXECUTE', execution_name => 'after_change');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> spool task01_after_change.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off

We can now execute a third time the task and generate the compare performance report based on the two previous test execute runs:

SQL> EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'Task01', execution_type => 'COMPARE PERFORMANCE', execution_name => 'compare_performance');
 
PL/SQL PROCEDURE successfully completed.
 
SQL> spool task01_compare_performance.html
SQL> SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('Task01', 'HTML', 'ALL', 'ALL') FROM dual;
SQL> spool off

SQL Performance Analyzer result

First let’s check all has been well executed:

SQL> SELECT execution_name, execution_type, TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end, advisor_name, status
     FROM dba_advisor_executions
     WHERE task_name='Task01';
 
EXECUTION_NAME                 EXECUTION_TYPE                 EXECUTION_START               EXECUTION_END                 STATUS
------------------------------ ------------------------------ ----------------------------- ----------------------------- -----------
after_change                   TEST EXECUTE                   18-nov-2011 16:18:01          18-nov-2011 16:18:18          COMPLETED
before_change                  TEST EXECUTE                   18-nov-2011 16:16:39          18-nov-2011 16:17:11          COMPLETED
compare_performance            COMPARE PERFORMANCE            18-nov-2011 16:18:54          18-nov-2011 16:18:57          COMPLETED
 
SQL> SELECT last_execution,execution_type,TO_CHAR(execution_start,'dd-mon-yyyy hh24:mi:ss') AS execution_start,
     TO_CHAR(execution_end,'dd-mon-yyyy hh24:mi:ss') AS execution_end,status
     FROM dba_advisor_tasks
     WHERE task_name='Task01';
 
LAST_EXECUTION                 EXECUTION_TYPE                 EXECUTION_START               EXECUTION_END                 STATUS
------------------------------ ------------------------------ ----------------------------- ----------------------------- -----------
compare_performance            COMPARE PERFORMANCE            18-nov-2011 16:18:54          18-nov-2011 16:18:57          COMPLETED

We can have a test overview of plan comparison with:

SQL> col EXECUTION_NAME FOR a15
SQL> SELECT execution_name, plan_hash_value, parse_time, elapsed_time, cpu_time,user_io_time,buffer_gets,disk_reads,direct_writes,
     physical_read_bytes,physical_write_bytes,rows_processed
     FROM dba_advisor_sqlstats
     WHERE task_name='Task01';
 
EXECUTION_NAME  PLAN_HASH_VALUE PARSE_TIME ELAPSED_TIME   CPU_TIME USER_IO_TIME BUFFER_GETS DISK_READS DIRECT_WRITES PHYSICAL_READ_BYTES PHYSICAL_WRITE_BYTES ROWS_PROCESSED
--------------- --------------- ---------- ------------ ---------- ------------ ----------- ---------- ------------- ------------------- -------------------- --------------
before_change        1328242299      40664      8630688    1831720       808827      135782     117208             0           960167936                    0             60
after_change         2949292326     167884      1808470     988850       340845       57450      38114             0           312229888                    0             60

We can generate the text version of the two explain plans, but again html version is much more readable for a normal human being:

SQL> col PLAN FOR a140
SQL> SET pages 500
SQL> SELECT p.plan_id, RPAD('(' || p.ID || ' ' || NVL(p.parent_id,'0') || ')',8) || '|' ||
     RPAD(LPAD (' ', 2*p.DEPTH) || p.operation || ' ' || p.options,40,'.') ||
     NVL2(p.object_owner||p.object_name, '(' || p.object_owner|| '.' || p.object_name || ') ', '') ||
     'Cost:' || p.COST || ' ' || NVL2(p.bytes||p.CARDINALITY,'(' || p.bytes || ' bytes, ' || p.CARDINALITY || ' rows)','') || ' ' ||
     NVL2(p.partition_id || p.partition_start || p.partition_stop,'PId:' || p.partition_id || ' PStart:' ||
     p.partition_start || ' PStop:' || p.partition_stop,'') ||
     'io cost=' || p.io_cost || ',cpu_cost=' || p.cpu_cost AS PLAN
     FROM dba_advisor_sqlplans p
     WHERE task_name='Task01'
     oder BY p.plan_id, p.id, p.parent_id;
 
   PLAN_ID PLAN
---------- --------------------------------------------------------------------------------------------------------------------------------------------
     89713 (0 0)   |SELECT STATEMENT .......................COST:11331 (207480 bytes, 1064 ROWS) io COST=11331,cpu_cost=
     89713 (1 0)   |  SORT GROUP BY.........................COST:11331 (207480 bytes, 1064 ROWS) io COST=11331,cpu_cost=
     89713 (2 1)   |    FILTER .............................COST:  io COST=,cpu_cost=
     89713 (3 2)   |      HASH JOIN ........................COST:11294 (207480 bytes, 1064 ROWS) io COST=11294,cpu_cost=
     89713 (4 3)   |        TABLE ACCESS BY INDEX ROWID.....(GSNX.OM_SHIPMENT_LINE) COST:3 (130 bytes, 5 ROWS) io COST=3,cpu_cost=
     89713 (5 4)   |          NESTED LOOPS .................COST:905 (166423 bytes, 1021 ROWS) io COST=905,cpu_cost=
     89713 (6 5)   |            HASH JOIN ..................COST:275 (28770 bytes, 210 ROWS) io COST=275,cpu_cost=
     89713 (7 6)   |              TABLE ACCESS FULL.........(GSNX.CORE_PARTY) COST:3 (4932 bytes, 274 ROWS) io COST=3,cpu_cost=
     89713 (8 6)   |              HASH JOIN ................COST:271 (24990 bytes, 210 ROWS) io COST=271,cpu_cost=
     89713 (9 8)   |                TABLE ACCESS FULL.......(GSNX.CORE_PARTY) COST:3 (4932 bytes, 274 ROWS) io COST=3,cpu_cost=
     89713 (10 8)  |                TABLE ACCESS FULL.......(GSNX.OM_SHIPMENT) COST:267 (21210 bytes, 210 ROWS) io COST=267,cpu_cost=
     89713 (11 5)  |            INDEX RANGE SCAN............(GSNX.OM_SHIPMENT_LINE_N1) COST:2 ( bytes, 6 ROWS) io COST=2,cpu_cost=
     89713 (12 3)  |        VIEW ...........................(SYS.VW_NSO_1) COST:10385 (637184 bytes, 19912 ROWS) io COST=10385,cpu_cost=
     89713 (13 12) |          SORT UNIQUE...................COST:10385 (423284 bytes, 19912 ROWS) io COST=8900,cpu_cost=
     89713 (14 13) |            UNION-ALL ..................COST:  io COST=,cpu_cost=
     89713 (15 14) |              SORT UNIQUE...............COST:8900 (190 bytes, 2 ROWS) io COST=8900,cpu_cost=
     89713 (16 15) |                FILTER .................COST:  io COST=,cpu_cost=
     89713 (17 16) |                  SORT GROUP BY.........COST:8900 (190 bytes, 2 ROWS) io COST=8900,cpu_cost=
     89713 (18 17) |                    NESTED LOOPS .......COST:8892 (2755 bytes, 29 ROWS) io COST=8892,cpu_cost=
     89713 (19 18) |                      HASH JOIN ........COST:8842 (1975 bytes, 25 ROWS) io COST=8842,cpu_cost=
     89713 (20 19) |                        TABLE ACCESS FUL(GSNX.MFG_WIP) COST:8808 (166191 bytes, 5361 ROWS) io COST=8808,cpu_cost=
     89713 (21 19) |                        INDEX FAST FULL (GSNX.OM_SHIPMENT_N2) COST:27 (1008432 bytes, 21009 ROWS) io COST=27,cpu_cost=
     89713 (22 18) |                      INDEX RANGE SCAN..(GSNX.MFG_WIP_LOT_QTY_N1) COST:2 (16 bytes, 1 ROWS) io COST=2,cpu_cost=
     89713 (23 16) |                  SORT AGGREGATE........COST: (9 bytes, 1 ROWS) io COST=,cpu_cost=
     89713 (24 23) |                    INDEX RANGE SCAN....(GSNX.OM_SHIPMENT_LINE_N1) COST:3 (360 bytes, 40 ROWS) io COST=3,cpu_cost=
     89713 (25 14) |              MINUS ....................COST:  io COST=,cpu_cost=
     89713 (26 25) |                SORT UNIQUE.............COST: (219010 bytes, 19910 ROWS) io COST=,cpu_cost=
     89713 (27 26) |                  INDEX FAST FULL SCAN..(GSNX.OM_SHIPMENT_UK1) COST:19 (231099 bytes, 21009 ROWS) io COST=19,cpu_cost=
     89713 (28 25) |                SORT UNIQUE.............COST: (204084 bytes, 22676 ROWS) io COST=,cpu_cost=
     89713 (29 28) |                  INDEX FAST FULL SCAN..(GSNX.MFG_WIP_N5) COST:1296 (518760 bytes, 57640 ROWS) io COST=1296,cpu_cost=
     89713 (30 2)  |      FILTER ...........................COST:  io COST=,cpu_cost=
     89713 (31 30) |        TABLE ACCESS BY INDEX ROWID.....(GSNX.MFG_WIP) COST:4 (19 bytes, 1 ROWS) io COST=4,cpu_cost=
     89713 (32 31) |          INDEX RANGE SCAN..............(GSNX.MFG_WIP_N5) COST:3 ( bytes, 1 ROWS) io COST=3,cpu_cost=
     89714 (0 0)   |SELECT STATEMENT .......................COST:19324 (252720 bytes, 1296 ROWS) io COST=19260,cpu_cost=663547063
     89714 (1 0)   |  SORT GROUP BY.........................COST:19324 (252720 bytes, 1296 ROWS) io COST=19260,cpu_cost=663547063
     89714 (2 1)   |    FILTER .............................COST:  io COST=,cpu_cost=
     89714 (3 2)   |      HASH JOIN ........................COST:16730 (252720 bytes, 1296 ROWS) io COST=16668,cpu_cost=633246741
     89714 (4 3)   |        NESTED LOOPS ...................COST:  io COST=,cpu_cost=
     89714 (5 4)   |          NESTED LOOPS .................COST:1248 (166423 bytes, 1021 ROWS) io COST=1240,cpu_cost=81128743
     89714 (6 5)   |            HASH JOIN ..................COST:617 (28770 bytes, 210 ROWS) io COST=610,cpu_cost=74949007
     89714 (7 6)   |              VIEW .....................(GSNX.INDEX$_join$_004) COST:3 (4932 bytes, 274 ROWS) io COST=2,cpu_cost=5340263
     89714 (8 7)   |                HASH JOIN ..............COST:  io COST=,cpu_cost=
     89714 (9 8)   |                  INDEX FAST FULL SCAN..(GSNX.CORE_PARTY_PK) COST:1 (4932 bytes, 274 ROWS) io COST=1,cpu_cost=77402
     89714 (10 8)  |                  INDEX FAST FULL SCAN..(GSNX.CORE_PARTY_UK2) COST:1 (4932 bytes, 274 ROWS) io COST=1,cpu_cost=77402
     89714 (11 6)  |              HASH JOIN ................COST:614 (24990 bytes, 210 ROWS) io COST=608,cpu_cost=64398724
     89714 (12 11) |                VIEW ...................(GSNX.INDEX$_join$_003) COST:3 (4932 bytes, 274 ROWS) io COST=2,cpu_cost=5340263
     89714 (13 12) |                  HASH JOIN ............COST:  io COST=,cpu_cost=
     89714 (14 13) |                    INDEX FAST FULL SCAN(GSNX.CORE_PARTY_PK) COST:1 (4932 bytes, 274 ROWS) io COST=1,cpu_cost=77402
     89714 (15 13) |                    INDEX FAST FULL SCAN(GSNX.CORE_PARTY_UK2) COST:1 (4932 bytes, 274 ROWS) io COST=1,cpu_cost=77402
     89714 (16 11) |                TABLE ACCESS FULL.......(GSNX.OM_SHIPMENT) COST:611 (21210 bytes, 210 ROWS) io COST=606,cpu_cost=53848440
     89714 (17 5)  |            INDEX RANGE SCAN............(GSNX.OM_SHIPMENT_LINE_N1) COST:2 ( bytes, 6 ROWS) io COST=2,cpu_cost=16293
     89714 (18 4)  |          TABLE ACCESS BY INDEX ROWID...(GSNX.OM_SHIPMENT_LINE) COST:3 (130 bytes, 5 ROWS) io COST=3,cpu_cost=29445
     89714 (19 3)  |        VIEW ...........................(SYS.VW_NSO_1) COST:15481 (808672 bytes, 25271 ROWS) io COST=15428,cpu_cost=544289828
     89714 (20 19) |          HASH UNIQUE...................COST:15481 (685783 bytes, 25271 ROWS) io COST=12215,cpu_cost=193587837
     89714 (21 20) |            UNION-ALL ..................COST:  io COST=,cpu_cost=
     89714 (22 21) |              HASH UNIQUE...............COST:12234 (262689 bytes, 5361 ROWS) io COST=12215,cpu_cost=193587837
     89714 (23 22) |                FILTER .................COST:  io COST=,cpu_cost=
     89714 (24 23) |                  HASH JOIN ............COST:12230 (262689 bytes, 5361 ROWS) io COST=12212,cpu_cost=180277196
     89714 (25 24) |                    TABLE ACCESS BY INDE(GSNX.MFG_WIP) COST:12169 (128664 bytes, 5361 ROWS) io COST=12152,cpu_cost=167815964
     89714 (26 25) |                      INDEX SKIP SCAN...(GSNX.MFG_WIP_N2) COST:647 ( bytes, 57640 ROWS) io COST=645,cpu_cost=16123961
     89714 (27 24) |                    INDEX FAST FULL SCAN(GSNX.OM_SHIPMENT_N2) COST:60 (525225 bytes, 21009 ROWS) io COST=60,cpu_cost=4408262
     89714 (28 23) |                  SORT AGGREGATE........COST: (9 bytes, 1 ROWS) io COST=,cpu_cost=
     89714 (29 28) |                    INDEX RANGE SCAN....(GSNX.OM_SHIPMENT_LINE_N1) COST:3 (54 bytes, 6 ROWS) io COST=3,cpu_cost=22564
     89714 (30 23) |                  SORT AGGREGATE........COST: (10 bytes, 1 ROWS) io COST=,cpu_cost=
     89714 (31 30) |                    INDEX RANGE SCAN....(GSNX.MFG_WIP_LOT_QTY_N1) COST:3 (10 bytes, 1 ROWS) io COST=3,cpu_cost=21764
     89714 (32 21) |              MINUS ....................COST:  io COST=,cpu_cost=
     89714 (33 32) |                SORT UNIQUE.............COST: (219010 bytes, 19910 ROWS) io COST=,cpu_cost=
     89714 (34 33) |                  INDEX FAST FULL SCAN..(GSNX.OM_SHIPMENT_UK1) COST:42 (231099 bytes, 21009 ROWS) io COST=42,cpu_cost=3824304
     89714 (35 32) |                SORT UNIQUE.............COST: (204084 bytes, 22676 ROWS) io COST=,cpu_cost=
     89714 (36 35) |                  INDEX FAST FULL SCAN..(GSNX.MFG_WIP_N5) COST:2972 (518760 bytes, 57640 ROWS) io COST=2946,cpu_cost=267746021
     89714 (37 2)  |      FILTER ...........................COST:  io COST=,cpu_cost=
     89714 (38 37) |        TABLE ACCESS BY INDEX ROWID.....(GSNX.MFG_WIP) COST:4 (19 bytes, 1 ROWS) io COST=4,cpu_cost=29946
     89714 (39 38) |          INDEX RANGE SCAN..............(GSNX.MFG_WIP_N5) COST:3 ( bytes, 1 ROWS) io COST=3,cpu_cost=21614
 
73 ROWS selected.

Below view provide potential improvement (or regression, colors are self explaining), 79% in our case. So in clear moving this database from 9iR2 to 11gR2 would provide huge gain to this particular query with no effort. Obviously this simple query is not representative of anything and you would need to add much more statements to your STS to be in better position before upgrade. Of course RAT and workload capture could be a great help for such task:

SQL> col message FOR a80
SQL> col FINDING_NAME FOR a30
SQL> col EXECUTION_NAME FOR a20
SQL> SELECT execution_name,finding_name,TYPE,impact,message FROM dba_advisor_findings WHERE task_name='Task01';
 
EXECUTION_NAME       FINDING_NAME                   TYPE            IMPACT MESSAGE
-------------------- ------------------------------ ----------- ---------- --------------------------------------------------------------------------------
compare_performance  normal, SUCCESSFUL completion  INFORMATION          0 The structure OF the SQL PLAN IN execution 'before_change' IS different than its
                                                                            corresponding PLAN which IS stored IN the SQL Tuning SET.
 
compare_performance  normal, SUCCESSFUL completion  SYMPTOM              0 The structure OF the SQL execution PLAN has changed.
compare_performance  normal, SUCCESSFUL completion  INFORMATION 79.0460506 The performance OF this SQL has improved.

Finally all reports generated:

References

  • Using SQL Performance Analyzer to Test SQL Performance Impact of 9i to 10gR2 Upgrade [ID 562899.1]
  • SQL PERFORMANCE ANALYZER EXAMPLE [ID 455889.1]

One thought on “SQL Performance Analyzer

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>