SQL 效能分析器（SPA）工具概覽

作為 Oracle Real Application Testing 選件/特性，這篇文章將提供一個關於 SQL 效能分析器（SPA）工具的簡要概覽。
DBA 的一個重要工作是確保在一個計劃內的變更安排後，當前生產環境負載和 SQL 執行計劃可以持續平滑執行。變更可能包含資料庫升級、增加一個新索引或改變一個特定的資料庫引數。 SPA 工具作為 Oracle Real Application Testing 選件的一部分提供，允許將生產環境負載中的 SQL 拿到測試環境的目標資料庫執行，所以可以透過比對結果識別退化的效能問題，並在遷移、升級或特定系統變更之前修復。如果您計劃使用資料庫重演特性，在執行重演之前使用 SPA 是 Oracle 建議的最佳實踐。目標是在資料庫重演之前識別和修復所有的 SQL 效能退化，所以我們可以只關注重演特性中的併發和吞吐量 。SQL 效能分析器使用 SQL 調優集（STS）作為輸入，STS 已經存在了很長一段時間了，這允許 DBA 提取現有生產環境的 SQL 語句工作負載並在節省時間和資源的前提下輕鬆比對一組變更前和變更後的執行結果。 一個 SQL 調優集（STS）是一個包含了一系列從工作負載中得來的 SQL 語句集及其執行上下文資訊（例如使用者和繫結變數、執行的統計資訊和執行計劃）的資料庫物件。

如下清單提供了一些 DBA 考慮使用 SPA 工具的常見場景。 

使用場景

1.    資料庫升級 – 一個新版本資料庫意味著一個新版本的最佳化器。DBA 能在升級生產系統之前主動發現任何 SQL 效能退化。
2.    部署一個補丁 – 您可能會部署一個與效能或最佳化器相關的特定修復的補丁。使用 SPA 來檢查您的生產環境 SQL 負載能幫助您驗證這個補丁不會引起任何 SQL 效能退化。
3.    資料庫初始化引數變更 - 有各種各樣的資料庫引數可能影響效能，所以這是 SPA 用處的一個很好的場景。
4.    Schema 變更例如增加索引 – schema 變更和修改，如增加索引會直接影響最佳化器的決定和計劃。SPA 可用來測試這些變更並確保不會引入負面影響。
5.    改變或重新整理最佳化器統計資訊 – 最佳化器統計資訊直接關係到最佳化器的決策和執行計劃的生成，您可以使用 SPA 來測試新的統計資訊和設定來確保它們不會引起 SQL 效能退化。 

使用 SPA 包括執行以下工作流文件/步驟。SPA 工具完全整合到 Oracle12c Cloud Control 中，Oracle 也提供了一個名為 DBMS_SQLPA 的 PLSQL 包來允許 DBA 使用 PL/SQL 實施這些步驟。這個工作流使用了一個迭代的過程來執行、對比和分析、以及修復這些退化。DBA 可使用諸如 SQL 執行計劃基線或 SQL 調優顧問等工具/特性來修復 SPA 發現的壞或退化的 SQL 語句。

SPA 工作流
1.    捕捉您想要分析的生產系統的 SQL 工作負載，並將其儲存為一個 SQL 調優集。
2.    設定目標測試系統（這應該儘可能多地和生產系統一致）。
3.    在測試系統建立一個 SPA 任務。

4.    構建變更前 SPA 任務。
5.    進行系統變更。
6.    構建變更後 SPA 任務。                                                              
7.    對比和分析變更前後的效能資料。
8.    調優或修復任何退化的 SQL 語句。
9.    重複地6~8步，直到 SQL 效能在測試系統上可接受。

出於本文的目的，我們將透過一個簡單例項“給表增加一個索引 Schema 變更”來介紹。 

?    源資料庫版本 12.1.0.2.0
?    目標測試系統 12.1.0.2.0
?    系統變更是給 t1 表增加一個索引 
?    效能報告將生成 HTML 格式的詳細資訊

SPA – 使用 PL/SQL API 的簡單介紹


1.    捕獲 SQL 工作負載到一個 SQL 調優集

建立和填充 STS

BEGIN 
  DBMS_SQLTUNE.DROP_SQLSET (sqlset_name  => 'MYSIMPLESTSUSINGAPI'); 
END;
/

BEGIN 
  DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name  => 'MYSIMPLESTSUSINGAPI', description  => 'My Simple STS Using the API' );
END;
/

1a. 使用 SCOTT 使用者執行一下 PLSQL 程式碼執行 SQL 語句。（PLSQL 用來模擬使用繫結變數的 SQL 語句工作負載） 
var b1 number;

declare
v_num number;
 begin
  for i in 1..10000 loop
  :b1 := i;
  select c1 into v_num from t1 where c1 = :b1;
 end loop;
end;
/

1b. 從遊標快取中找到使用 parsing schema=SCOTT 的語句來填充 STS

DECLARE
  c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''SCOTT''', NULL, NULL, NULL, NULL, 1, NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'MYSIMPLESTSUSINGAPI', populate_cursor => c_sqlarea_cursor);
END;
/

1c. 檢查 STS 中捕獲了多少 SQL 語句

COLUMN NAME FORMAT a20
COLUMN COUNT FORMAT 99999
COLUMN DESCRIPTION FORMAT a30

SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;

Results:

NAME                     SQLCNT DESCRIPTION
-------------------- ---------- ------------------------------
MYSIMPLESTSUSINGAPI          12 My Simple STS Using the API

1d. 顯示 STS 的內容

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCOTT", SQL_TEXT,   ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'MYSIMPLESTSUSINGAPI' ) );

Results: (partial)

SQL_ID        SCOTT                          SQL_TEXT                                               ELAPSED                  BUFFER_GETS
------------- ------------------------------ ------------------------------                                  ----------                     -----------
0af4p26041xkv SCOTT                 SELECT C1 FROM T1 WHERE C1 = :  169909252             18185689

2.  設定目標系統

為了演示目的，這裡將使用 STS 的捕獲源作為同樣的目標測試系統。 

3.  建立 SPA 任務

VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'MYSIMPLESTSUSINGAPI', task_name => 'MYSPATASKUSINGAPI');
print t_name

Results:

T_NAME
-----------------
MYSPATASKUSINGAPI

4.  建立和執行變更前的 SPA 任務

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'TEST EXECUTE', execution_name => 'MY_BEFORE_CHANGE');


5.  做出系統變更

CREATE INDEX t1_idx ON t1 (c1);

6.  建立和執行變更後的 SPA 任務

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'TEST EXECUTE', execution_name => 'MY_AFTER_CHANGE');

7.  對比和分析變更前和變更後的效能

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'COMPARE PERFORMANCE', execution_name => 'MY_EXEC_COMPARE', execution_params => dbms_advisor.arglist('comparison_metric', 'elapsed_time'));

-- Generate the Report

set long 100000000 longchunksize 100000000 linesize 200 head off feedback off echo off TRIMSPOOL ON TRIM ON
VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MYSPATASKUSINGAPI', 'html', 'typical', 'all');
SPOOL C:\mydir\SPA_detailed.html
PRINT :rep
SPOOL off


HTML 格式的報告示例：

下面是 SPA 報告的一部分截圖。報告由3個部分組成，一個部分涉及到變更前和變更後的任務包括 範圍，狀態，執行起始時間，錯誤個數和比較的標準；第二個部分總結部分包括了變更帶來的負載影響；第三個部分包括了詳細的 SQL 資訊，比如 SQLID 以及需要比較的度量，比如對負載的影響，執行的頻率，以及在這個例子裡在變更前後的執行時間這個度量。比如對於 SQL ID 0af4p26041xkv 來說，負載影響是97%。我們要實施的變更對於效能有好的影響，可以把執行時間從12766降低到29。我們還可以看到執行計劃在變更後發生了變化。這些資訊可以幫助 DBA 進一步關注在特定的問題或者效能退化上，對於當前的這個例子，影響是對效能有提升。

下面的截圖顯示了在增加了索引後執行計劃的變化。這部分資訊可以讓 DBA 進一步關注在變更前後某個具體 SQL 的執行計劃上。對任何 SQL 退化來說，這可以讓 DBA 來清楚瞭解執行計劃是如何變化的，並且可以進一步採取計劃，比如使用 SQL Tuning Advisor 或者建立 SPM 基線。