SQL Performance Analyzer Example
APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.0. [Release 11.1 to 11.2]Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 11.1.0. to 11.2.0.Affected Database: Oracle
GOAL
@Checked for relevance 23-August-2010.
The goal of this note is show an example of SQL Performance Analyzer.
SOLUTION
The steps involved in the SQL PERFORMANCE Analyzer are
2) Create a task to run Sql Performance Analyzer.
3) Execute Before Change TEST EXECUTE (Pre-Change SQL Trial).
Make a change that needs to tested.
4) Execute After Change TEST EXECUTE (Post-Change SQL Trial).
5) Comparing SQL Trials.
6) Generate Compare report.
Notes
This was tested on 11g database 11.1.0.6.0.
Execute the workload with optimizer_features_enable='9.2.0' set.
connect sh/sh
SELECT /*+ my_query */ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, 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;
SELECT /*+ my_query */ p.prod_name, s.time_id, t.week_ending_day,
SUM(s.amount_sold)
FROM sales s, products p, 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;
SELECT /*+ my_query */ p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_category = 'Photo'
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;
SELECT /*+ my_query */ p.prod_subcategory_desc, t.week_ending_day, SUM(s.amount_sold)
FROM sales s, products p, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND p.prod_subcategory_desc LIKE '%Audio'
GROUP BY p.prod_subcategory_desc, t.week_ending_day;
-- create my sql tuning set and populate it from the cursor cache
var sts_name varchar2(30);
exec :sts_name := 'small_sh_sts_4';
exec dbms_sqltune.drop_sqlset(:sts_name);
exec dbms_sqltune.create_sqlset(:sts_name, 'small demo workload to test SQLPA');
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;
/
select sql_id, plan_hash_value, buffer_gets, elapsed_time, substr(sql_text,1, 30
) text, executions
from dba_sqlset_statements
where sqlset_name = :sts_name
order by sql_id, plan_hash_value;
---create sql task
-- declare vars
var tname varchar2(30);
var sname varchar2(30);
-- init vars
exec :sname := 'small_sh_sts_4';
exec :tname := 'my_sqlpa_demo_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
---------------------------
SELECT task_name, status
FROM user_advisor_tasks
WHERE task_name = :tname;
--Now I am ready to run the Before Change Execute
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_sqlpa_demo_task',
execution_type => 'TEST EXECUTE',
execution_name => 'BEFORECHANGE');
end;
/
--We can check the status of this task.
from DBA_ADVISOR_EXECUTIONS where task_name='my_sqlpa_demo_task';
--Make a change that needs to tested.
alter system set optimizer_features_enable='11.1.0.6';
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_sqlpa_demo_task',
execution_type => 'TEST EXECUTE',
execution_name => 'AFTERCHANGE');
end;
/
--Now we need to compare the 2 executions Before Change and After Change.
--I am selecting the comparison matrix as BUFFER_GETS.
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_sqlpa_demo_task',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'DEMOTASK',
execution_params => dbms_advisor.arglist(
'comparison_metric',
'buffer_gets'));
end;
/
--Now I will generate a compare report
--The report format can be TEXT,HTML,OR XML
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
spool report.html
SELECT dbms_sqlpa.report_analysis_task('my_sqlpa_demo_task', 'HTML', 'ALL','ALL') FROM dual;
spool off
Click here to see the file report.txt in text format.
The report is divided into the following main sections:
1) General Information
2) Report Summary
3) Report Details
General Information
The General Information section contains basic information and metadata about the SQL Performance Analyzer task, the SQL Tuning Set used, and the pre-change and post-change executions.
The following shows the General Information section of a sample report.
It shows The task name my_sqlpa_demo_task , The task owner SH, THE STS NAME small_sh_sts_4,
execution type was COMPARE PERFORMANCE
Points to note here is
Comparison Metric used was BUFFER_GETS.
Their where total 4 sqls statement in this workload
Their was 0 errors while running this sql.
Report Summary
The Report summary contains
1) Overall Performance Statistics
In this case
Overall Impact : 0%
Improvement Impact : 0%
Regression Impact : 0%
( Note it was 0 % in this case because we compared based on BUFFER_GETS and the change based on buffer gets was less than 1 % which is WORKLOAD_IMPACT_THRESHOLD )
2) Performance Statistics of SQL Statements
It is shown under "SQL Statements Sorted by their Absolute Value of Change Impact on the Workload "
3) Errors
If their are any error while executing the sql it will be reported. Such sqls are not considered for
analysis.
Report Details
The Report Details section represents a drill-down into the performance of SQL statements that appears in the Report Summary section of the report. This section shows detailed information about each individual sql, it shows the individual impact on the workload, it gives information if the plan has changed and also shows the plan before the change and after the change.
1) In 11gR1 each sql statement is executed only once when doing the test execute, No matter how many times it was executed during the capture. In 11gR2 the sqls which are not long running are executed atleast twice for each SQL statement, for as many times as possible until the execution times out (up to a maximum of 10 times). The first execution is used to warm the buffer cache. All subsequent executions are then used to calculate the run-time execution statistics for the SQL statement based on their averages. The actual number of times that the SQL statement is executed depends on how long it takes to execute the SQL statement. Long-running SQL statement will only be executed a second time, and the execution statistics from this execution will be used. Other (faster-running) SQL statements are executed multiple times, and their execution statistics are averaged over these executions (statistics from the first execution are not used in the calculation). This is clearly indicated by the SPA report under each sql execution.The task parameter that control the multi execution is DISABLE_MULTI_EXEC which by default in 11gR2 is set to false.
DISABLE_MULTI_EXEC: SQL statements are executed multiple times and runtime statistics are then averaged. Set this parameter to 'TRUE' to disable this capability. In this case, each SQL in the SQL tuning set is executed only once.
2) SQL Analyze comment is added to each sql statement that SPA executes.
For example
/* SQL Analyze(135,0) */ SELECT /*+ my_query */ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold) FROM sales s, products p, 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
3) Each sql statement is executed one by one i.e not concurrently, so the SQL Performance analyzer does not do concurrency test.
4)By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.
For example in your workload you have the statement
and before executing the update statement you had
select empno ,ename from emp ;
EMPNO ENAME
------- ----------
7369 SMITH
Even after executing the above update statement you will still have EMPNO as 7369.
EMPNO ENAME
------- ----------
7369 SMITH
4) In 10g database releases and 11g R1 SPA uses milliseconds precision for measuring timing while doing the test execute, in 11g R2 SPA test execute uses micro seconds. What this means is in 10g or 11g R1 any sql that is executing in less than 1 milliseconds and test executed by SPA the time consumed in the statistics will be reported as 0.
Oracle? Database Real Application Testing User's Guide
11g Release 2 (11.2)
Part Number E16540-06
SQL Performance Analyzer
Document 1464274.1 Master Note for Real Application Testing Option
REFERENCES
NOTE:562899.1 - Using SQL Performance Analyzer to Test SQL Performance Impact of an UpgradeNOTE:560977.1 - Using Real Application Testing Functionality in Earlier Releasesa
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29953799/viewspace-1472433/ ，如需转载，请注明出处，否则将追究法律责任。
