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):
|
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")
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/67/viewspace-1024660/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/67/viewspace-1024660/