隨著業務升級，目前很多客戶都慢慢將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

【SQL】Oracle資料庫變更後sql效能對比

相關文章