随着业务升级，目前很多客户都慢慢将Oracle低版本数据库迁移至高版本，如Oracle11.2.0.4 迁移至 Oracle19c，那么如何测试性能呢？有一个很好的工具SPA（此功能可以结合Oracle Real Application Testing进行整体测试），该工具具体使用细则参考本博下面文章
影响性能的系统变化主要有
- 初始化参数变化
- 数据库升级
- 硬件变化
- 操作系统变化
- 应用程序模式对象的增加或改变
- SQL基线或概要文件的实现
- 等其他改变
DBMS_SQLPA包介绍
下面主要针对某个查询的例子(数据库升级），仅供参考，主要步骤如下：
1.创建一个分析任务
declare
g_task varchar2(100);
begin
g_task := dbms_sqlpa.create_analysis_task(task_name=>'task2021',sql_text=>'select last_name,first_name,department_name from hr.employees join hr.departments using(department_id)');
end;
/
2.基于低版本场景进行分析
建议情况 缓冲区缓存
alter session set optimizer_features_enable='11.2.0.4';
exec dbms_sqlpa.execute_analysis_task(task_name=>'task2021',execution_type=>'test execute',execution_name=>'before_change');
3.新版本执行分析任务
建议情况 缓冲区缓存
alter session set optimizer_features_enable='19.1.0';
exec dbms_sqlpa.execute_analysis_task(task_name=>'task2021',execution_type=>'test execute',execution_name=>'after_change');
4.修改前后进行比较
执行分析对比
exec dbms_sqlpa.execute_analysis_task(task_name=>'task2021',execution_type=>'compare performance',execution_name=>'compare change',execution_params=>dbms_advisor.arglist('comparison_metric','buffer_gets'));
查看分析结果
set long 10000 longchunksize 10000 lines 200 head off feedback off echo off
select dbms_sqlpa.report_analysis_task('task2021','TEXT','changed_plans','ALL') from dual;
示例输出如下:
General Information
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : task2021
Task Owner : HR
Description :
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : compare change Started : 07/15/2021 08:31:22
Execution Type : COMPARE PERFORMANCE Last Updated : 07/15/2021 08:31:23
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : before_change Execution Name : after_change
Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE
Scope : COMPREHENSIVE Scope : COMPREHENSIVE
Status : COMPLETED Status : COMPLETED
Started : 07/15/2021 08:26:22 Started : 07/15/2021 08:27:26
Last Updated : 07/15/2021 08:26:29 Last Updated : 07/15/2021 08:27:31
Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED
Number of Errors : 0 Number of Errors : 0
---------------------------------------------
Comparison Metric: BUFFER_GETS
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------
Report Details
---------------------------------------------------------------------------------------------
SQL Details:
-----------------------------
Object ID : 4
Schema Name : HR
Container Name : MYPDB
SQL ID : 3h31mt0hzp1am
Execution Frequency : 1
SQL Text : select last_name,first_name,department_name from
hr.employees join hr.departments using(department_id)
Execution Statistics:
-----------------------------
--------------------------------------------------------------------
| | Impact on | Value | Value | Impact |
| Stat Name | Workload | Before | After | on SQL |
--------------------------------------------------------------------
| elapsed_time | -162.93% | .000464 | .00122 | -162.93% |
| parse_time | -130.61% | .00148 | .003413 | -130.61% |
| cpu_time | -180.37% | .000438 | .001228 | -180.37% |
| user_io_time | 0% | 0 | 0 | 0% |
| buffer_gets | 0% | 8 | 8 | 0% |
| cost | 0% | 5 | 5 | 0% |
| reads | 0% | 0 | 0 | 0% |
| writes | 0% | 0 | 0 | 0% |
| io_interconnect_bytes | 0% | 0 | 0 | 0% |
| rows | | 106 | 106 | |
--------------------------------------------------------------------
Note: time statistics are displayed in seconds
Notes:
-----------------------------
Before Change:
1. The statement was first executed to warm the buffer cache.
2. Statistics shown were averaged over next 9 executions.
After Change:
1. The statement was first executed to warm the buffer cache.
2. Statistics shown were averaged over next 9 executions.
Execution Plan Before Change:
-----------------------------
Plan Id : 701
Plan Hash Value : 1473400139
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3604 | 5 | 00:00:01 |
| 1 | MERGE JOIN | | 106 | 3604 | 5 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 | 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 | 00:00:01 |
| * 4 | SORT JOIN | | 107 | 1926 | 3 | 00:00:01 |
| 5 | VIEW | index$_join$_001 | 107 | 1926 | 2 | 00:00:01 |
| * 6 | HASH JOIN | | | | | |
| 7 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 1926 | 1 | 00:00:01 |
| 8 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 1926 | 1 | 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
* 4 - filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
* 6 - access(ROWID=ROWID)
Execution Plan After Change:
-----------------------------
Plan Id : 702
Plan Hash Value : 1473400139
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 3604 | 5 | 00:00:01 |
| 1 | MERGE JOIN | | 106 | 3604 | 5 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 432 | 2 | 00:00:01 |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 | 00:00:01 |
| * 4 | SORT JOIN | | 107 | 1926 | 3 | 00:00:01 |
| 5 | VIEW | index$_join$_001 | 107 | 1926 | 2 | 00:00:01 |
| * 6 | HASH JOIN | | | | | |
| 7 | INDEX FAST FULL SCAN | EMP_DEPARTMENT_IX | 107 | 1926 | 1 | 00:00:01 |
| 8 | INDEX FAST FULL SCAN | EMP_NAME_IX | 107 | 1926 | 1 | 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 4 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
* 4 - filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
* 6 - access(ROWID=ROWID)
参考
- 《Oracle Database 12C 性能优化攻略》
- https://blog.csdn.net/itdba/article/details/42215345