随着业务升级，目前很多客户都慢慢将Oracle低版本数据库迁移至高版本，如Oracle11.2.0.4 迁移至 Oracle19c，那么如何测试性能呢？有一个很好的工具SPA（此功能可以结合Oracle Real Application Testing进行整体测试），该工具具体使用细则参考本博下面文章 - http://blog.itpub.net/29487349/viewspace-2769759/ #### 影响性能的系统变化主要有 - 初始化参数变化 - 数据库升级 - 硬件变化 - 操作系统变化 - 应用程序模式对象的增加或改变 - SQL基线或概要文件的实现 - 等其他改变 #### DBMS_SQLPA包介绍 下面主要针对某个查询的例子(数据库升级），仅供参考，主要步骤如下： ##### 1.创建一个分析任务 ``` sql 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.基于低版本场景进行分析 建议情况 缓冲区缓存 ``` sql 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.新版本执行分析任务 建议情况 缓冲区缓存 ``` sql 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.修改前后进行比较 执行分析对比 ``` sql 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')); ``` 查看分析结果 ``` sql 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; ``` 示例输出如下: ``` sql 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