影响性能的系统变化主要有

初始化参数变化
数据库升级
硬件变化
操作系统变化
应用程序模式对象的增加或改变
SQL基线或概要文件的实现
等其他改变

DBMS_SQLPA包介绍

下面主要针对某个查询的例子(数据库升级），仅供参考，主要步骤如下：

1.创建一个分析任务

declareg_task varchar2(100);beging_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 offselect 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    : 0Analysis 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 secondsNotes:-----------------------------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)