>  本文主要介紹 Oracle Real Application Testing，未實戰  --By Firsouler 2021.4.23

- [1.介紹](#1介紹)
- [2.SPA](#2spa)
  - [從生產環境中捕獲SQL工作量負載](#從生產環境中捕獲sql工作量負載)
  - [產生變化前執行SQL](#產生變化前執行sql)
  - [產生變化後執行SQL](#產生變化後執行sql)
  - [對比分析效能](#對比分析效能)
    - [SPA報告](#spa報告)
    - [SPA價值](#spa價值)
- [3.DB Replay](#3db-replay)
  - [工作負載捕獲](#工作負載捕獲)
  - [處理負載檔案](#處理負載檔案)
  - [工作負載重放](#工作負載重放)
  - [重放分析和報告](#重放分析和報告)
- [兩種方式的選擇](#兩種方式的選擇)
- [參考：](#參考)

### 1.介紹

Oracle Real Application Testing選項能夠執行Oracle資料庫的真實測試。透過捕獲生產工作負載並在生產部署之前評估系統更改對這些工作負載的影響，Oracle Real Application Testing將與系統更改相關的不穩定風險降至最低。SQL效能分析器和資料庫重放是Oracle Real Application Testing的關鍵元件。根據要測試的系統更改的性質和影響，以及要執行測試的系統型別，可以使用其中一個或兩個元件來執行測試。

Oracle RAT 主要有兩個功能，SQL Performance Analyzer(SPA) 和 Database Replay（DB Replay）。 具體如下：

### 2.SPA

可以使用SPA來分析任何型別的系統更改對SQL效能的影響，包括：

- 資料庫更新升級
- 作業系統或硬體相關配置改變
- 使用者改變
- 資料庫初始化引數改變
- 重新整理最佳化器統計資訊
- SQL最佳化操作

如下圖所示，SPA主要工作是：

- 測試SQL查詢效能方面改變的結果
- 在生產環境中捕獲包括統計資訊與繫結變數的SQL負載
- 在測試環境中重新執行SQL查詢

![spa_process](https://gitee.com/firsouler/my_study_notes/raw/master/images/oracle_rat/spa_process.jpg)

**SPA具體過程如下**

``` sql
--sql 樣例
myworkload.sql

connect sh/sh

SELECT /*+ my_query */ p.prod_name, s.time_id, t.week_ending_day, SUM(s.amount_sold) 
FROM sales s, products p, times t 
WHERE s.time_id=t.time_id 
AND s.prod_id = p.prod_id 
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

SELECT /*+ my_query */ p.prod_name, s.time_id, t.week_ending_day, 
SUM(s.amount_sold) 
FROM sales s, products p, times t 
WHERE s.time_id=t.time_id 
AND s.prod_id = p.prod_id 
GROUP BY p.prod_name, s.time_id, t.week_ending_day;

SELECT /*+ my_query */ p.prod_category, t.week_ending_day, s.cust_id, SUM(s.amount_sold) 
FROM sales s, products p, times t 
WHERE s.time_id = t.time_id 
AND s.prod_id = p.prod_id 
AND p.prod_category = 'Photo' 
GROUP BY p.prod_category, t.week_ending_day, s.cust_id;

SELECT /*+ my_query */ p.prod_subcategory_desc, t.week_ending_day, SUM(s.amount_sold) 
FROM sales s, products p, times t 
WHERE s.time_id = t.time_id 
AND s.prod_id = p.prod_id 
AND p.prod_subcategory_desc LIKE '%Audio' 
GROUP BY p.prod_subcategory_desc, t.week_ending_day;
```

#### 從生產環境中捕獲SQL工作量負載

- SQL調優集(STS)用於儲存SQL工作負載
- STS包括： SQL文字、繫結變數、執行計劃、執行統計
- 對於一個時間段內的遊標快取進行增量捕獲並放置於STS
- SQL調優集過濾與排除不需要的SQL
- 可傳輸的STS用來將工作量負載移至測試系統

參考命令

1) Create the Sql Tuning Set (STS).

```sql
--create_sts.sql 
-- create my sql tuning set and populate it from the cursor cache 
var sts_name varchar2(30); 
exec :sts_name := 'small_sh_sts_4'; 
exec dbms_sqltune.drop_sqlset(:sts_name); 
exec dbms_sqltune.create_sqlset(:sts_name, 'small demo workload to test SQLPA'); 
DECLARE 
stscur dbms_sqltune.sqlset_cursor; 
BEGIN 
OPEN stscur FOR 
SELECT VALUE(P) 
FROM TABLE(dbms_sqltune.select_cursor_cache( 
'sql_text like ''SELECT /*+ my_query%''', 
null, null, null, null, null, null, 'ALL')) P; 
-- populate the sqlset 
dbms_sqltune.load_sqlset(:sts_name, stscur); 
end; 
/

-- check content of sts 
select sql_id, plan_hash_value, buffer_gets, elapsed_time, substr(sql_text,1, 30 
) text, executions 
from dba_sqlset_statements 
where sqlset_name = :sts_name 
order by sql_id, plan_hash_value;
```

2) Create a task to run Sql Performance Analyzer

``` sql
--create_sqlpa_task.sql 
---create sql task 
-- declare vars 
var tname varchar2(30); 
var sname varchar2(30); 
-- init vars 
exec :sname := 'small_sh_sts_4'; 
exec :tname := 'my_sqlpa_demo_task'; 
-- 1. create a task with a purpose of change impact analysis 
------------------------------------------------------------ 
exec :tname := dbms_sqlpa.create_analysis_task(sqlset_name => :sname, - 
task_name => :tname); 
-- 2. check task status 
--------------------------- 
SELECT task_name, status 
FROM user_advisor_tasks 
WHERE task_name = :tname;
```

#### 產生變化前執行SQL

- 建立SQL工作負載效能基線
- 捕獲SQL執行計劃與統計資訊
- 連續執行SQL(非併發)
- 每個SQL只執行一次
- 忽略DDL語句
- 可選擇只做執行計劃解析選項

``` sql
--beforechange.sql

--Now I am ready to run the Before Change Execute 
begin 
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
task_name => 'my_sqlpa_demo_task', 
execution_type => 'TEST EXECUTE', 
execution_name => 'BEFORECHANGE'); 
end; 
/

--Using a Database link
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'my_sqlpa_demo_task',
execution_type => 'TEST EXECUTE',
execution_name => 'BEFORECHANGE',
execution_params => dbms_advisor.arglist('DATABASE_LINK', '&dblink_name'));
end;
/

--We can check the status of this task.
select execution_name,status, execution_end 
from DBA_ADVISOR_EXECUTIONS where task_name='my_sqlpa_demo_task';
```

#### 產生變化後執行SQL

- 手工實施計劃的改變，如資料庫升級/打補丁、最佳化統計重新整理、Schema變化、資料庫引數變化、調優活動
- 變化後重新執行SQL，如收集新的SQL執行計劃與統計資訊

``` sql
-- afterchange.sql
begin 
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
task_name => 'my_sqlpa_demo_task', 
execution_type => 'TEST EXECUTE', 
execution_name => 'AFTERCHANGE'); 
end; 
/
```

#### 對比分析效能

- 使用不同指標對比效能，如使用時間、CPU時間、最佳化器成本等
- SPA包含會顯示每一個SQL的變化影響，如最佳化SQL、回退SQL、非變化SQL、帶錯誤SQL
- 使用SQL最佳化嚮導與SQL計劃基線來修改回退的SQL

``` sql
--compare_runs.sql 
begin 
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
task_name => 'my_sqlpa_demo_task', 
execution_type => 'COMPARE PERFORMANCE', 
execution_name => 'DEMOTASK', 
execution_params => dbms_advisor.arglist( 
'comparison_metric', 
'buffer_gets')); 
end; 
/

--report.sql
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off 
spool report.html 
SELECT dbms_sqlpa.report_analysis_task('my_sqlpa_demo_task', 'HTML', 'ALL','ALL') FROM dual; 
spool off
```

##### SPA報告

##### SPA價值

- 在終端使用者受到影響之前可以識別SQL效能的衰減
- SPA可以對發生任何改變的SQL執行計劃鎖引起的影響進行幫助
- 可對成百上千的sql宣告自動進行跟蹤
- 可以低系統影響下捕獲SQL工作量負載
- 與修正衰減的SQL調優嚮導和SQL計劃基線進行整合

### 3.DB Replay

![db_replay](https://gitee.com/firsouler/my_study_notes/raw/master/images/oracle_rat/db_replay.jpg)

如上圖所示，資料庫重放，主要目的

- 使用真實的生產環境工作負載來測試資料庫負載和效能
- 定位應用程式的可擴充套件性和併發性問題
- 在上線前修改問題以降低遷移的風險
- 11g以上版本可以做Replay

> 生產系統要 10.2.0.4 以上， 測試系統 11.1以上

#### 工作負載捕獲

- 將所有外部客戶端的請求捕獲至二進位制檔案
- 排除系統後臺與內部的活動
- 捕獲時的最小效能影響
- 支援RAC，共享與本地檔案系統
- 可以按感興趣的時間段執行捕獲，如峰值、月末處理期等。

> 開銷 一般在2.5-7%之間

``` sql
--建立負載檔案存放目錄
mkdir -p /home/oracle/capture
create or replace directory cap as '/home/oracle/capture';

--開始負載捕獲
exec dbms_workload_repository.create_snapshot();
exec dbms_wrokload_capture.start_capture(name=>'pdbrat_cap',dir=>'CAP');

--執行swingbench，模擬產生負載

--結束負載
exec dbms_workload_capture.finish_capture();
exec dbms_workload_repository.create_snapshot();

```

#### 處理負載檔案

- 在測試資料庫伺服器上，將捕獲的負載處理成可以重放的格式
- 只需要處理一次，可以多次重放
- 對於RAC資料庫，捕獲檔案需要放到同一位置進行處理
- 推薦採用共享檔案系統

``` sql
--目標庫
mkdir -p /home/oracle/replay

--確保目標庫中資料物件與源庫一樣
create pluggable database pdbreplay from pdbrat file_name_convert=('pdbrat','pdbreplay');

--open，建立相關目錄
create or replace directory replay as '/tmp/replay';
grant read,write on directory replay to public;

--執行預處理
exec dbms_workload_replay.process_capture('REPLAY');
--評估 需要多少客戶端等
wrc mode=calibrate replaydir=/home/oracle/replay
```

#### 工作負載重放

- 工作負載重放保留捕獲系統的時間段，併發與依賴關係
- 重放裝置是一個消費程式負載和想重放系統傳送請求的指定客戶端程式
- 重放裝置由一個或多個客戶端組成，為了事項高併發性工作流，可能需要啟動多個客戶端去推動工作負載

``` sql
--初始化
exec dbms_workload_replay.initialize_replay(replay_name=>'repjob',replay_dir=>'REPLAY');

--對映連線
select conn_id,capture_conn,replay_conn from dba_workload_connection_map where replay_id=1;

--修改為測試庫連線
select 'exec dbms_workload_replay.remap_connect('||conn_id||'. ''(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.99)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME= PDBREPLAY)))'');' from dba_workload_connection_map where replay_id=1;

--重放配置，同步重發
exec dbms_workload_replay.prepare_replay(synchronization=TURE,connect_time_scale=>100,think_time_scale=>100,think_time_auto_correct=> FALSE);

--開啟重放客戶端
wrc admin/oracle@pdbreplay replaydir=/home/oracle/replay

--啟動重放
exec dbms_workload_repository.create_snapshot();
exec dbms_workload_replay.start_replay;
exec dbms_workload_repository.create_snapshot();

--生成報告,html格式或者text格式
select max(id) from dba_workload_replays where status='COMPLETED';
set pages 0 long 999999
spool /home/oracle/replay_report.html
select dbms_workload_replay.report(1,'HTML') from dual;

```

#### 重放分析和報告

重放過程進度可監控， 提供了全面的報告以供分析，三種報告

- 資料差異：對比每個呼叫返回的行數並報告差異
- 錯誤差異： 對每個呼叫，都會報告錯誤差異（新錯誤：重發出現的、未找到：捕獲出現重發為出現、變異：不同的錯誤）
- 效能差異：提供效能資訊，awr/ash等

### 兩種方式的選擇

![spa_dbreplay](https://gitee.com/firsouler/my_study_notes/raw/master/images/oracle_rat/spa_dbreplay.jpg)

### 參考：
- Oracle公益培訓課《Oracle Real application Testing》 王旭
- https://cloud.tencent.com/developer/article/1431515
- https://www.cnblogs.com/jyzhao/p/8231728.html#3
- SQL Performance Analyzer Example (Doc ID 455889.1)
- Master Note for Real Application Testing Option (Doc ID 1464274.1)

【RAT】Oracle Real Application Testing（真用應用測試）介紹

相關文章