【博客文章2022】全命令行SQL Performance Analyzer进行真实环境DML类测试
Author: Bo Tang
1. 准备实验环境：
首先以apps用户执行以下这条sql语句（依据硬件的不同，大约需要执行300秒），同时马上执行下面第2节的收集tuning set：
2. 从cursor cache中收集tuning set, 持续10分钟，间隔3秒钟（以SYS身份运行）：
SQL> begin
dbms_sqltune.create_sqlset(sqlset_name => 'ASTS1');
dbms_sqltune.capture_cursor_cache_sqlset(
sqlset_name => 'ASTS1' ,
time_limit => 10*60,
repeat_interval => 3,
basic_filter=> q'# ((module like 'DWH_TEST%' and sql_text not like '%applicat%') or
sql_text LIKE '%fact_pd_out_itm_293%')
and parsing_schema_name in ('APPS')
#');
end;
/
|
查看ASTS1中包含的SQL语句：
SQL> select owner, name from dba_sqlset where name='ASTS1';
-----------------------------------------------------------------------------------------
SYS ASTS1
SQL> select sql_id , sql_text from dba_sqlset_statements where sqlset_name='ASTS1';
------------------------------------------------------------------------------------------------------------------------
chrbybrsjk0az update apps. fact_pd_out_itm_293 f
set pd_price_units_eur=pd_price_units_eur-
(select avg(pd_price_units_eur) avgval from apps. fact_pd_out_itm_293 a
where a.project_id=f.project_id and a.outlet_id=f.outlet_id
group by project_id, outlet_id) *0.01
where pd_price_units_eur>4000 "
gp9x9u2nh2uy2 select * from v_fact_pd_out_itm_293
|
3. 为了模拟两次远程真实运行的基准测试，而创建公共数据库链（以SYS身份运行）。
生产环境可以分别创建两个公共数据库链接：A和B，A链接用于做连接到A库的before测试；B链接用于做连接到B库的after测试，而执行SPA的本机只是作为中立测试机器。这样做可以把before测试和after测试物理隔离，用于测试硬件改动或者Oracle版本升级等等before和after环境同时共存的大型环境。
SQL> create public database link tomydb connect to system identified by oracle_4U using 'rcat';
|
由于rcat测试库的db_domain是example.com，所以引用这个公共数据库链的时候，应该称其为：tomydb.example.com。
4. 创建并执行SQL Performance Analyzer Before任务（以SYS身份运行，大约要运行5分钟）：
SQL> set serveroutput on
SQL> var sts_task varchar2(64);
SQL> declare
exe_task1 varchar2(64);
begin
:sts_task:= dbms_sqlpa.create_analysis_task(task_name => 'aspa1',description => 'aspa1',sqlset_name=> 'ASTS1');
dbms_sqlpa.set_analysis_task_parameter(task_name => 'aspa1', parameter=> 'EXECUTE_FULLDML', value => 'TRUE');
exe_task1:= dbms_sqlpa.execute_analysis_task(task_name=>'aspa1',
execution_name=>'aspa1before',
execution_type=>'TEST EXECUTE',
execution_desc=>'aspa1before',
execution_params=>dbms_advisor.arglist('DATABASE_LINK','TOMYDB.EXAMPLE.COM'));
dbms_output.put_line('The task is '||:sts_task);
dbms_output.put_line('The before execution is '||exe_task1);
end;
/
SQL> select dbms_sqlpa.report_analysis_task(task_name=> :sts_task, type=>'text',section=>'summary') from dual;
|
这里对execution_type作详细解释： 默认是[TEST] EXECUTE：它指测试执行每一条SQL语句，并收集执行计划和执行统计信息。这些执行计划和执行统计信息会存储在advisor framework中。 EXPLAIN PLAN：为每一条SQL语句产生执行计划 。这与EXPLAIN PLAN命令相似。这些执行计划会存储在advisor framework中。 COMPARE [PERFORMANCE] ：比较性能数据。 CONVERT SQLSET：测试执行，实际并没有执行。 可以通过一下视图看到advisor的执行进度：
SQL> select ADVISOR_NAME, sofar,totalwork from V$ADVISOR_PROGRESS;
ADVISOR_NAME SOFAR TOTALWORK
---------------------------------------------------------------- ---------- ----------
SQL Performance Analyzer 0 2
|
5. 对环境进行更改（创建一个索引）：
以apps用户身份创建一个索引：
6. 创建并执行SQL Performance Analyzer After任务（以SYS身份运行，大约要运行5分钟）：
SQL> declare
exe_task2 varchar2(64);
begin
exe_task2:= dbms_sqlpa.execute_analysis_task(task_name=>'myspa1',
execution_name=>'myspa1after',
execution_type=>'TEST EXECUTE',
execution_desc=>'myspa1after',
execution_params=>dbms_advisor.arglist('DATABASE_LINK','TOMYDB.EXAMPLE.COM'));
end;
/
|
7. 查看SQL Performance Analyzer After任务（以SYS身份运行） ：
7.1 比较实际执行时间：
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'aspa1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'spa1_compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'aspa1before',
'execution_name2', 'aspa1after',
'comparison_metric', 'elapsed_time') );
end;
/
|
7.2 比较CPU_TIME：
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'aspa1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'spa1_compare_cpu_time',
execution_params => dbms_advisor.arglist('execution_name1', 'aspa1before',
'execution_name2', 'aspa1after',
'comparison_metric', 'cpu_time') );
end;
/
|
7.3 比较BUFFER_GETS：
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'aspa1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'spa1_compare_buffer_gets',
execution_params => dbms_advisor.arglist('execution_name1', 'aspa1before',
'execution_name2', 'aspa1after',
'comparison_metric', 'buffer_gets') );
end;
/
|
7.4 比较比较物理读：
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'aspa1',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'spa1_compare_disk_reads',
execution_params => dbms_advisor.arglist('execution_name1', 'aspa1before',
'execution_name2', 'aspa1after',
'comparison_metric', 'disk_reads') );
end;
/
|
8. 获得SPA报告（以elapsed_time为例）：
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off ;
spool /home/oracle/aspa1_report_elapsed_time.html;
SELECT dbms_sqlpa.report_analysis_task('aspa1', 'HTML', 'ALL','ALL', execution_name=>'spa1_compare_elapsed_time') FROM dual;
spool off;
|
9. 查看SPA报告：
/home/oracle/aspa1_report_elapsed_time.html内容如下： SQL> SELECT dbms_sqlpa.report_analysis_task('aspa1', 'HTML', 'ALL','ALL', execution_name=>'spa1_compare_elapsed_time') FROM dual; SQL Performance Impact Analyzer Report
General Information
Task Information:
| Workload Information:
| | Task Name | : aspa1 |
|---|
| Task Owner | : SYS |
|---|
| Description | : aspa1 |
|---|
| | SQL Tuning Set Name | : ASTS1 |
|---|
| SQL Tuning Set Owner | : SYS |
|---|
| Total SQL Statement Count | : 2 |
|---|
|
Execution Information:
| Execution Name | : spa1_compare_elapsed_time |
|---|
| Execution Type | : COMPARE PERFORMANCE |
|---|
| Description | : |
|---|
| Scope | : COMPREHENSIVE |
|---|
| Status | : COMPLETED |
|---|
| | Started | : 05/21/2022 23:08:38 |
|---|
| Last Updated | : 05/21/2022 23:08:39 |
|---|
| Global Time Limit | : UNLIMITED |
|---|
| Per-SQL Time Limit | : UNUSED |
|---|
| Number of Errors | : 0 |
|---|
|
Analysis Information:
Before Change Execution:
| After Change Execution:
| | Execution Name | : aspa1before |
|---|
| Execution Type | : TEST EXECUTE REMOTE |
|---|
| Database Link | : TOMYDB |
|---|
| Scope | : COMPREHENSIVE |
|---|
| Status | : COMPLETED |
|---|
| Started | : 05/21/2022 22:05:28 |
|---|
| Last Updated | : 05/21/2022 22:18:34 |
|---|
| Global Time Limit | : UNLIMITED |
|---|
| Per-SQL Time Limit | : UNUSED |
|---|
| Number of Errors | : 0 |
|---|
| | Execution Name | : aspa1after |
|---|
| Execution Type | : TEST EXECUTE REMOTE |
|---|
| Database Link | : TOMYDB |
|---|
| Scope | : COMPREHENSIVE |
|---|
| Status | : COMPLETED |
|---|
| Started | : 05/21/2022 22:39:37 |
|---|
| Last Updated | : 05/21/2022 22:48:41 |
|---|
| Global Time Limit | : UNLIMITED |
|---|
| Per-SQL Time Limit | : UNUSED |
|---|
| Number of Errors | : 0 |
|---|
|
| Comparison Metric: ELAPSED_TIME
| Workload Impact Threshold: 1%
| SQL Impact Threshold: 1%
| |
Report Summary
Projected Workload Change Impact:
| Overall Impact | : | 32.52% |
|---|
| Improvement Impact | : | 32.52% |
|---|
| Regression Impact | : | 0% |
|---|
SQL Statement Count
| SQL Category | SQL Count | Plan Change Count |
|---|
| Overall | 2 | 2 | | Improved | 2 | 2 |
Top 2 SQL Sorted by Absolute Value of Change Impact on the Workload
object_id |
sql_id | Impact on
Workload | Execution
Frequency | Metric
Before | Metric
After | Impact
on SQL | Plan
Change |
|---|
| 8 | chrbybrsjk0az | 31.38% | 1 | 427416900 | 291744828 | 31.74% | y | | 9 | gp9x9u2nh2uy2 | 1.14% | 4 | 1232314 | 4096 | 99.67% | y |
Note: time statistics are displayed in microseconds
Report Details
SQL Details:
| Object ID | : 8 |
|---|
| Schema Name | : APPS |
|---|
| Container Name | : Unknown (con_dbid: 1629279676) |
|---|
| SQL ID | : chrbybrsjk0az |
|---|
| Execution Frequency | : 1 |
|---|
| SQL Text | : update apps.fact_pd_out_itm_293 f set pd_price_units_eur=pd_price_units_eur- (select avg(pd_price_units_eur) avgval from apps.fact_pd_out_itm_293 a where a.project_id=f.project_id and a.outlet_id=f.outlet_id group by project_id, outlet_id) *0.01 where pd_price_units_eur>4400 |
|---|
Execution Statistics:
Stat Name | Impact on
Workload | Value
Before | Value
After | Impact
on SQL |
|---|
| elapsed_time | 31.38% | 427.4169 | 291.744828 | 31.74% | | parse_time | .9% | .073335 | .029464 | 59.82% | | cpu_time | 33.73% | 358.491927 | 236.213528 | 34.11% | | user_io_time | 29.72% | 271.520165 | 189.880189 | 30.07% | | buffer_gets | 34.27% | 10842447 | 7090981 | 34.6% | | cost | 35.97% | 7556143 | 4832095 | 36.05% | | reads | 34.08% | 9895009 | 6488581 | 34.43% | | writes | 0% | 0 | 0 | 0% | | io_interconnect_bytes | 34.08% | 81059913728 | 53154455552 | 34.43% | | rows | | 0 | 0 | |
Note: time statistics are displayed in seconds Notes:
Before Change: - The statement was first executed to warm the buffer cache.
- Statistics shown were from the second execution.
|
After Change: - The statement was first executed to warm the buffer cache.
- Statistics shown were from the second execution.
|
Findings (2):
- The performance of this SQL has improved.
- The structure of the SQL execution plan has changed.
|
Execution Plan Before Change:
| Plan Id | : 1201 |
|---|
| Plan Hash Value | : 1762397204 |
|---|
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|
| 0 | UPDATE STATEMENT | | 1721 | 24094 | 7556143 | 00:01:42 | | 1 | . UPDATE | FACT_PD_OUT_ITM_293 | 0 | 0 | 4294967295 | 00:00:00 | | * 2 | .. TABLE ACCESS FULL | FACT_PD_OUT_ITM_293 | 1721 | 24094 | 4395 | 00:00:01 | | 3 | .. SORT GROUP BY NOSORT | | 1 | 14 | 4388 | 00:00:01 | | * 4 | ... TABLE ACCESS FULL | FACT_PD_OUT_ITM_293 | 721 | 10094 | 4388 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - filter("PD_PRICE_UNITS_EUR">4400)
- 4 - filter("A"."OUTLET_ID"=:B1 AND "A"."PROJECT_ID"=:B2)
Note
- dynamic sampling used for this statement Execution Plan After Change:
| Plan Id | : 1301 |
|---|
| Plan Hash Value | : 2675115628 |
|---|
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|
| 0 | UPDATE STATEMENT | | 1101 | 15414 | 4832095 | 00:01:05 | | 1 | . UPDATE | FACT_PD_OUT_ITM_293 | 0 | 0 | 4294967295 | 00:00:00 | | 2 | .. TABLE ACCESS BY INDEX ROWID BATCHED | FACT_PD_OUT_ITM_293 | 1101 | 15414 | 907 | 00:00:01 | | * 3 | ... INDEX RANGE SCAN | I_FACT_PD_OUT_ITM_293 | 1101 | 0 | 5 | 00:00:01 | | 4 | .. SORT GROUP BY NOSORT | | 1 | 14 | 4388 | 00:00:01 | | * 5 | ... TABLE ACCESS FULL | FACT_PD_OUT_ITM_293 | 721 | 10094 | 4388 | 00:00:01 |
Predicate Information (identified by operation id):
- 3 - access("PD_PRICE_UNITS_EUR">4400)
- 5 - filter("A"."OUTLET_ID"=:B1 AND "A"."PROJECT_ID"=:B2)
SQL Details:
| Object ID | : 9 |
|---|
| Schema Name | : APPS |
|---|
| Container Name | : Unknown (con_dbid: 1629279676) |
|---|
| SQL ID | : gp9x9u2nh2uy2 |
|---|
| Execution Frequency | : 4 |
|---|
| SQL Text | : select * from v_fact_pd_out_itm_293 |
|---|
Execution Statistics:
Stat Name | Impact on
Workload | Value
Before | Value
After | Impact
on SQL |
|---|
| elapsed_time | 1.14% | 1.232314 | .004096 | 99.67% | | parse_time | 97.47% | 1.197928 | .012466 | 98.96% | | cpu_time | 1.11% | 1.011499 | .004084 | 99.6% | | user_io_time | 1.16% | .798499 | 0 | 100% | | buffer_gets | .87% | 25788 | 2076 | 91.95% | | cost | .18% | 4396 | 908 | 79.34% | | reads | 1.02% | 25389 | 0 | 100% | | writes | 0% | 0 | 0 | 0% | | io_interconnect_bytes | 1.02% | 207986688 | 0 | 100% | | rows | | 242 | 242 | |
Note: time statistics are displayed in seconds Notes:
Before Change: - The statement was first executed to warm the buffer cache.
- Statistics shown were from the second execution.
|
After Change: - The statement was first executed to warm the buffer cache.
- Statistics shown were averaged over next 9 executions.
|
Findings (2):
- The performance of this SQL has improved.
- The structure of the SQL execution plan has changed.
|
Execution Plan Before Change:
| Plan Id | : 1202 |
|---|
| Plan Hash Value | : 2820496992 |
|---|
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|
| 0 | SELECT STATEMENT | | 242 | 3388 | 4396 | 00:00:01 | | 1 | . HASH GROUP BY | | 242 | 3388 | 4396 | 00:00:01 | | * 2 | .. TABLE ACCESS FULL | FACT_PD_OUT_ITM_293 | 1101 | 15414 | 4395 | 00:00:01 |
Predicate Information (identified by operation id):
- 2 - filter("PD_PRICE_UNITS_EUR">4400)
Note
- dynamic sampling used for this statement Execution Plan After Change:
| Plan Id | : 1302 |
|---|
| Plan Hash Value | : 2474639355 |
|---|
| Id | Operation | Name | Rows | Bytes | Cost | Time |
|---|
| 0 | SELECT STATEMENT | | 242 | 3388 | 908 | 00:00:01 | | 1 | . HASH GROUP BY | | 242 | 3388 | 908 | 00:00:01 | | 2 | .. TABLE ACCESS BY INDEX ROWID BATCHED | FACT_PD_OUT_ITM_293 | 1101 | 15414 | 907 | 00:00:01 | | * 3 | ... INDEX RANGE SCAN | I_FACT_PD_OUT_ITM_293 | 1101 | 0 | 5 | 00:00:01 |
Predicate Information (identified by operation id):
- 3 - access("PD_PRICE_UNITS_EUR">4400)
Note
- dynamic sampling used for this statement
SQL> spool off;
|
