SQL PERFORMANCE ANALYZER, a great tool for upgrade testing tuning and benchmark check

Changes that affect SQL execution plans, such as upgrading a database or adding
new indexes, can severely impact SQL performance. As a result, DBAs spend
enormous amount of time and effort identifying and fixing SQL statements that
have regressed due to the changes. SQL Performance Analyzer (SPA), a key feature
of the Real Application Testing option introduced in Oracle Database 11g, can
predict and prevent SQL execution performance problems caused by system
changes.[@more@]

here is a simple step by step how to use it to fulfill your task:

1.
SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> show parameter optimizer_features_enable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable string 11.1.0.6

SQL> alter system set optimizer_features_enable='9.2.0' ;

2.

SQL>SELECT /*+ my_query */ p.prod_name, s.time_id, t.week_ending_day,
SUM(s.amount_sold)
FROM sh.sales s, sh.products p, sh.times t
WHERE s.time_id=t.time_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

no rows selected

SQL>SELECT /*+ my_query */ distinct source_id, store, sum(level_need) NEED,
source_diff1_id, source_diff2_id,
source_diff3_id,
source_diff4_id
from rms_owner.ALC_LEVEL_LOC_TEMP
group by source_id,
store,
source_diff1_id,
source_diff2_id,
source_diff3_id,
source_diff4_id
order by store;

no rows selected

3. make a tuning set:

var sts_name varchar2(30);
exec :sts_name := 'spa_test';

exec dbms_sqltune.drop_sqlset(:sts_name);
exec dbms_sqltune.create_sqlset(:sts_name, 'test SQLPA in QA');

DECLARE
stscur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN stscur FOR
SELECT VALUE(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(
'sql_text like ''SELECT /*+ my_query%''',
null, null, null, null, null, null, 'ALL')) P;
-- populate the sqlset
dbms_sqltune.load_sqlset(:sts_name, stscur);
end;
/

4. check records in dictionary table:

select SQLSET_NAME,sql_id, plan_hash_value, buffer_gets,
elapsed_time, substr(sql_text,1, 50 ) text, executions
from dba_sqlset_statements
where sqlset_name = 'spa_test'
order by sql_id, plan_hash_value;

SQLSET_NAME SQL_ID PLAN_HASH_VALUE BUFFER_GETS ELAPSED_TIME TEXT EXECUTIONS
------------------------------ ------------- --------------- ----------- ------------ -------------------------------------------------------------------------------- ----------
spa_test ag87nkcumbh7d 693687463 0 2588 SELECT /*+ my_query */ distinct source_id, store, 1
spa_test grharfhj0yxfk 2121776064 48 6535 SELECT /*+ my_query */ p.prod_name, s.time_id, t.w 1

5. create a task to run spa

-- declare vars
var tname varchar2(30);
var sname varchar2(30);

-- init vars
exec :sname := 'spa_test';
exec :tname := 'my_spa_task';

--
-- 1. create a task with a purpose of change impact analysis
------------------------------------------------------------
exec :tname := dbms_sqlpa.create_analysis_task(sqlset_name => :sname, -
task_name => :tname);

-- 2. check task status
---------------------------
col EXECUTION_TYPE for a15
col HOW_CREATED for a10
col LAST_EXECUTION for a10
SELECT task_name, status,CREATED,
LAST_MODIFIED,ADVISOR_NAME
LAST_EXECUTION,EXECUTION_TYPE,
HOW_CREATED
FROM dba_advisor_tasks
WHERE task_name = :tname;

TASK_NAME STATUS CREATED LAST_MODIFIED LAST_EXECU EXECUTION_TYPE HOW_CREATE
------------------------------ ----------- ------------------- ------------------- ---------- --------------- ----------
my_spa_task INITIAL 2009-07-27:15:40:30 2009-07-27:15:40:31 SQL Perfor CMD
mance Anal
yzer
6. Execute Before change

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_spa_task',
execution_type => 'TEST EXECUTE',
execution_name => 'BEFORECHANGE');
end;
/

note: execution_type

Type of the action to perform by the function.
If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE parameter. Possible values are:

[TEST] EXECUTE - test-execute every SQL statement and collect its execution plans and execution statistics.
The resulting plans and statistics will be stored in the advisor framework. This is default.

EXPLAIN PLAN - generate explain plan for every statement in the SQL workload.
This is similar to the EXPLAIN PLAN command.
The resulting plans will be stored in the advisor framework in association with the task.

COMPARE [PERFORMANCE] - analyze and compare two versions of SQL performance data.
The performance data is generated by test-executing or generating explain plan of the SQL statements.
Use this option when two executions of type EXPLAIN_PLAN or TEST_EXECUTE already exist in the task

CONVERT SQLSET - used to read the statistics captured in a SQL Tuning Set and model them as a task execution.
This can be used when you wish to avoid executing the SQL statements
because valid data for the experiment already exists in the SQL Tuning Set.

7. check the stats of This Task

col owner for a15
col task_name for a20
col EXECUTION_TYPE for a20
select owner, task_name,execution_name,status, EXECUTION_TYPE,EXECUTION_START, execution_end
from DBA_ADVISOR_EXECUTIONS where task_name='my_spa_task';

OWNER TASK_NAME EXECUTION_NAME STATUS EXECUTION_TYPE EXECUTION_START EXECUTION_END
--------------- -------------------- ------------------------------ ----------- -------------------- ------------------- -------------------
SYS my_spa_task BEFORECHANGE COMPLETED TEST EXECUTE 2009-07-27:15:43:19 2009-07-27:15:43:20

8. make the changes

SQL> alter system set optimizer_features_enable='11.1.0.6';

System altered.

9. Execute After change

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_spa_task',
execution_type => 'TEST EXECUTE',
execution_name => 'AFTERCHANGE');
end;
/

10. create Executtion of Compare report

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_spa_task',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'COMPARETASK',
execution_params => dbms_advisor.arglist(
'comparison_metric',
'buffer_gets'));
end;
/

parameter

COMPARISON_METRIC: specify an expression of execution statistics to use
in performance comparison (Example: buffer_gets, cpu_time + buffer_gets * 10)

11. generate the html report

set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
spool report.html
SELECT dbms_sqlpa.report_analysis_task('my_spa_task', 'HTML', 'ALL','ALL') FROM dual;
spool off

report:

Top 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
6	ag87nkcumbh7d	0%	0	0	0%	0%	0%	n
7	grharfhj0yxfk	0%	48	48	0%	100%	100%	y

---

Execution Statistics:

Stat Name	Impact on Workload	Value Before	Value After	Impact on SQL	% Workload Before	% Workload After
elapsed_time	0%	0	0	0%	0%	0%
parse_time	-500%	0	.005	-.5%	0%	100%
cpu_time	0%	0	0	0%	0%	0%
buffer_gets	0%	48	48	0%	100%	100%
cost	25%	4	3	25%	100%	100%
reads	0%	0	0	0%	0%	0%
writes	0%	0	0	0%	0%	0%
io_interconnect_bytes	0%	0	0	0%	0%	0%
rows		0	0

---

Findings (2):

The structure of the SQL execution plan has changed. This SQL statement returned zero rows.

Execution Plan Before Change:

---

---

Plan Id	: 3770
Plan Hash Value	: 2121776064

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	83	4	00:00:01
1	. SORT GROUP BY		1	83	4	00:00:01
2	.. NESTED LOOPS		1	83	3	00:00:01
3	... NESTED LOOPS		1	51	3	00:00:01
4	.... PARTITION RANGE ALL		1	35	3	00:00:01
5	..... TABLE ACCESS FULL	SALES	1	35	3	00:00:01
6	.... TABLE ACCESS BY INDEX ROWID	TIMES	1	16	1	00:00:01
* 7	..... INDEX UNIQUE SCAN	TIME_PK	1		1	00:00:01
8	... TABLE ACCESS BY INDEX ROWID	PRODUCTS	1	32	1	00:00:01
* 9	.... INDEX UNIQUE SCAN	PRODUCTS_PK	1		1	00:00:01

Predicate Information (identified by operation id):

---

• 7 - access("S"."TIME_ID"="T"."TIME_ID")
• 9 - access("S"."PROD_ID"="P"."PROD_ID")

Execution Plan After Change:

---

Plan Id	: 3772
Plan Hash Value	: 3751115706

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	83	3	00:00:01
1	. HASH GROUP BY		1	83	3	00:00:01
2	.. NESTED LOOPS
3	... NESTED LOOPS		1	83	2	00:00:01
4	.... NESTED LOOPS		1	51	2	00:00:01
5	..... PARTITION RANGE ALL		1	35	2	00:00:01
6	...... TABLE ACCESS FULL	SALES	1	35	2	00:00:01
7	..... TABLE ACCESS BY INDEX ROWID	TIMES	1	16	0	00:00:01
* 8	...... INDEX UNIQUE SCAN	TIME_PK	1		0	00:00:01
* 9	.... INDEX UNIQUE SCAN	PRODUCTS_PK	1		0	00:00:01
10	... TABLE ACCESS BY INDEX ROWID	PRODUCTS	1	32	0	00:00:01

Predicate Information (identified by operation id):

---

• 8 - access("S"."TIME_ID"="T"."TIME_ID")
• 9 - access("S"."PROD_ID"="P"."PROD_ID")