Table of contents
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]
