Oracle11g中，真實應用測試選項（the Real Application Testing Option）提供了一個有用的特點，叫SQL效能分析器（SQL Performance Analyzer,SQLPA，SPA）。系統包DBMS_SQLPA讓你可以註冊和比較SQL調優集（STS）中的SQL語句執行的統計資訊。透過SQL效能分析器，可比較資料庫變化前後SQL語句的執行情況。下面將透過簡單例子說明該Oracle 11g 新特點。

1. 建立樣例表和資料

為了稍後的說明，首先，需建立一張表和往表中加一些資料，指令碼如下：

create table test

(idnumber not null);

begin

for i in 1..10000 loop

insert into test(id)

values (i);

end loop;

commit;

end;

/

2. 執行SQL語句並獲取執行計劃

登入SQL*Plus並執行SQL語句：

set serveroutput off

col id format 99999

select a.id, b.id

from testa, test b

wherea.id=b.id

andb.id=500;

select * from table(

dbms_xplan.display_cursor);

其執行計劃如下，記住其SQL_ID並將會新增到後面部分SQL調優集中的查詢語句中：

-------------------------------------

SQL_ID 683kdkrs2dmrk, child number 0

-------------------------------------

select a.id, b.id from test a, test b where a.id=b.id

and b.id=500

Plan hash value: 2625395012

----------------------------------------------------------------

----------------------------------------------------------------

| 0 |SELECT STATEMENT | | | | 15 (100)|

|* 1| HASH JOIN | | 1 | 26 | 15 (7)|

|* 2| TABLE ACCESS FULL| TEST | 1 | 13 | 7 (0)|

|* 3| TABLE ACCESS FULL| TEST | 1 | 13 | 7 (0)|

----------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1 -access("A"."ID"="B"."ID")

2 - filter("A"."ID"=500)

3 - filter("B"."ID"=500)

Note

-----

- dynamic sampling used for this statement

3. 捕獲SQL調優集查詢

一旦SQL語句位於共享池中，就可以建立一個新的SQL調優集，並向其中增加SQL語句：

1) 建立一個STS:

BEGIN

DBMS_SQLTUNE.CREATE_SQLSET(

sqlset_name => 'test_sts',

description => 'STS for SPA demo');

END;

/

2) 透過查詢遊標緩衝向STS中增加查詢：

DECLARE

l_cursor DBMS_SQLTUNE.sqlset_cursor;

BEGIN

OPENl_cursor FOR

SELECT VALUE(p)

FROM TABLE (

DBMS_SQLTUNE.select_cursor_cache(

'sql_id = ''&sql_id''', -- basic_filter

NULL, -- object_filter

NULL, -- ranking_measure1

NULL, -- ranking_measure2

NULL, -- ranking_measure3

NULL, -- result_percentage

1) -- result_limit

) p;

DBMS_SQLTUNE.load_sqlset (

sqlset_name => 'test_sts',

populate_cursor => l_cursor);

END;

/

accept sql_id prompt "Enter value forsql_id: "

683kdkrs2dmrk

PL/SQL procedure successfully completed.

3) 可查詢STS內容以確定該SQL已被正確註冊：

col sql format a50

set lines 120

SELECT sql_id,

substr(sql_text, 1, 50) sql

FROMTABLE(

DBMS_SQLTUNE.select_sqlset (

'test_sts'));

SQL_ID SQL

------------- ---------------------

683kdkrs2dmrk select a.id, b.id

from test a, test b

where a.i

3. 改變資料庫前，生成和儲存該SQL語句執行統計資訊

該步驟可能會花費一些時間，因為要執行STS查詢和儲存其執行統計資訊。按順序執行如下步驟：

1) 建立一個參考STS的SQLPA分析任務

var v_out char(50)

begin

:v_out:=dbms_sqlpa.create_analysis_task(

sqlset_name => 'test_sts',

task_name => 'test_spa_task');

end;

/

print v_out

V_OUT

-------------

test_spa_task

2) 檢查該任務是否已建立：

col TASK_NAME format a14

col ADVISOR_NAME format a24

select TASK_NAME,

ADVISOR_NAME,

created

fromDBA_ADVISOR_TASKS

wheretask_name='test_spa_task';

TASK_NAME ADVISOR_NAME CREATED

-------------- ---------------------------------

test_spa_task SQL Performance Analyzer 15-AUG-07

3) 執行該SQLPA分析任務：

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => 'test_spa_task',

execution_type => 'TEST EXECUTE',

execution_name => 'test_spa_task_before');

end;

/

4) 監視該任務及其狀態，直到任務完成：

col TASK_NAME format a20

select execution_name,

status,

execution_end

fromDBA_ADVISOR_EXECUTIONS

wheretask_name='test_spa_task'

orderby execution_end;

EXECUTION_NAME STATUS EXECUTION

------------------------------ --------------------

test_spa_task_before COMPLETED 15-AUG-07

5) 改變資料庫

在表TEST上建立一個索引：

create unique index test_idx on test(id);

Index created.

6) 改變資料庫後，執行該SQLPA分析任務

該指令碼和前面類似。只是需改變名字以區別資料庫改變前後的執行統計資訊：

? 執行SQLPA分析任務：

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => 'test_spa_task',

execution_type => 'TEST EXECUTE',

execution_name => 'test_spa_task_after');

end;

/

? 監視該任務及其狀態,直到其完成：

col TASK_NAME format a20

select execution_name,

status,

execution_end

fromDBA_ADVISOR_EXECUTIONS

wheretask_name='test_spa_task'

orderby execution_end;

EXECUTION_NAME STATUS EXECUTION

------------------------------ --------------------

test_spa_task_before COMPLETED 15-AUG-07

test_spa_task_after COMPLETED 15-AUG-07

7. 比較因資料庫改變而發生的執行改變

需再次執行分析任務,這次分析器將比較和儲存比較結果：

begin

DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(

task_name => 'test_spa_task',

execution_type => 'COMPAREPERFORMANCE',

execution_name => 'test_spa_task_compare',

execution_params => dbms_advisor.arglist(

'comparison_metric',

'buffer_gets'));

end;

/

PL/SQL procedure successfully completed.

一旦完成，就可如下這樣用REPORT_ANALISIS_TASK列印分析報告：

variable rep CLOB;

begin

:rep:= DBMS_SQLPA.REPORT_ANALYSIS_TASK(

task_name=>'test_spa_task',

type=>'HTML',

level=>'ALL',

section=>'ALL');

end;

/

SET LONG 100000

set LONGCHUNKSIZE 100000

set LINESIZE 200

set head off

set feedback off

set echo off

spool sts_changes.html

PRINT :rep

spool off

set head on

建議SECTION引數置為SUMMARY或ALL，而不非DBMS_SQLPA文件中說的置為SECTION_ALL那樣。

可透過SQL*Plus(文字輸出)或瀏覽器（HTML輸出）瀏覽生成的報告結果：

8. 另一個顯示比較結果的方法：

除了列印報告，還可從下面檢視中查詢報告：

? DBA_ADVISOR_FINDINGS

? DBA_ADVISOR_SQLPLANS

? DBA_ADVISOR_SQLSTATS

9. 清除執行統計資訊、分析任務及相關表

1) 重置任務結果：

begin

dbms_sqlpa.reset_analysis_task(task_name=>'test_spa_task');

end;

/

col TASK_NAME format a20

select execution_name,

status,

execution_end

fromDBA_ADVISOR_EXECUTIONS

wheretask_name='test_spa_task'

orderby execution_end;

no rows selected

2) 清除任務本身：

begin

dbms_sqlpa.drop_analysis_task(task_name=>'test_spa_task');

end;

/

col TASK_NAME format a14

col ADVISOR_NAME format a24

select TASK_NAME,

ADVISOR_NAME,

created

fromDBA_ADVISOR_TASKS

wheretask_name='test_spa_task';

no rows selected

3) 此外，還可以：

? 用過程cancel_analysis_task 取消正執行的分析任務。

? 用過程interrupt_analysis_task暫停執行的分析任務。

? 用過程resume_analysis_task恢復被暫停的分析任務。

? 清除相關表和索引：

drop table gark cascade constraints purge;

10. 總結

針對SQLPA做如下總結：

? 可輕鬆從AWR中捕獲STS中SQL語句。

? 不必重建測試系統（僅INSERT/UPDATE/DELETE語句的SELECT部分被執行)。

? STS中SQL語句僅是真實應用負載的一個樣例。

具體請參考：

? Oracle 11gPerformance Tuning Guide – 23.SQL Performance Analyzer

? Oracle 11g PL/SQLTypes and Packages Reference – DBMS_SQLPA

Oracle11g Reference

Oracle 11g 中SQL效能最佳化新特性之SQL效能分析器（SQLPA）

相關文章