oracle 11g資料庫中SPA使用例子介紹

oracle 11g推出了新特性SPA(SQL performance Analyze)現在已經被廣泛應用到升級和遷移的情境中，當然比如一些其他的情境也可以考慮使用，比如（最佳化器參數修改、IO子系統變更等），這種功能可向DBA 提供有關SQL語句效能的詳細資料，例如，執行前後的統計資訊，提高或降低效能的語句，主要用于衡量環境變化後SQL執行效能是否出現衰變等。

SPA的主要實施步驟如下：
1 在源環境捕捉SQL負載，產生SQLSET

exec dbms_sqltune.create_sqlset(‘SPA_SQLSET’);

從cursor cache收集SQLSET：

cat  sts_add.sh
date
sqlplus -s spa/spa_jzdb3 <<EOF1
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT value(P)
    FROM TABLE(dbms_sqltune.select_cursor_cache('parsing_schema_name not in (''SYS'') and  module not in (''PL/SQL Developer'') and force_matching_signature not in (select force_matching_signature from DBA_SQLSET_STATEMENTS ) ',NULL,NULL,NULL,NULL,1,NULL,'ALL')) p;
    dbms_sqltune.load_sqlset('SPA_SQLSET', cur, load_option=>'MERGE');
  CLOSE cur;
END;
/
從awr snapshot收集SQLSET：

declare
    cur sys_refcursor;
    begin
    open cur for select value(P) from table(dbms_sqltune.select_workload_repository(77589,78343)) p;
    dbms_sqltune.load_sqlset(sqlset_name=>'SPA_SQLSET',populate_cursor=>cur,load_option=>'MERGE',update_option=>'ACCUMULATE');
    close cur;
    end;
    /
從awr baseline收集SQLSET
從another sql set收集SQLSET
從10046 trace file

2 將SQLSET匯入到中轉表

源端建立stage table
exec DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET11_TAB', schema_name => 'SPA', tablespace_name => 'USERS');

將sqlset打包到stage table：
exec DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'SPA_SQLSET', sqlset_owner => 'SPA', staging_table_name => 'SQLSET11_TAB', staging_schema_owner => 'SPA');
3 將中專表匯入到新庫環境中，解壓舞台表資料到SQLSET中

impdp spa/spa_jzdb3 directory=back dumpfile=sqlset11_tab.dmp logfile=sqlset11_tab.log table_exists_action=replace

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (SQLSET_NAME=>'SPA_SQLSET', SQLSET_OWNER=> 'SPA', REPLACE=>TRUE, STAGING_TABLE_NAME=>'SQLSET11_TAB', STAGING_SCHEMA_OWNER=>'SPA'); 解壓舞台表sqlset11_tab到sqlset中
4 建立SPA任務，先產生10g的trail，然後在11g中在產生11g的trail

建立SPA任務：
var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'SPA_SQLSET';
exec :tname := 'SPA_TASK';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);

產生oracle 10g的trail
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'CONVERT SQLSET',
execution_name => 'CONVERT_10G');
end;
/

產生在目標庫的trail，由於需要在目標庫執行SQL執行時間可能比較長
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'TEST EXECUTE',
execution_name => 'EXEC_11G');
end;
/
5 執行比較任務，產生SPA報告

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'elapsed_time') );
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'CPU_TIME') );
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TASK',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'BUFFER_GETS') );
end;
/
產生SPA報告：

set trimspool on
set trim on
set pages 0
set long 999999999
set linesize 1000
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED

spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_elapsed_time') FROM dual;
spool off;

spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_CPU_time') FROM dual;
spool off;

spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_BUFFER_GETS_time') FROM dual;
spool off;

spool changed_plans.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK','HTML','CHANGED_PLANS','ALL',top_sql=>300) FROM dual;
spool off;

spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'errors','summary') FROM dual;
spool off;

spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TASK', 'HTML', 'unsupported','all') FROM dual;
spool off;
/
6 分析效能退化的SQL語句

常用的SPA Script：

-- 檢查運行SPA的進程的運行狀態
SELECT SID, TASK_ID, SOFAR, TOTALWORK, START_TIME, START_TIME + (SYSDATE - START_TIME)/SOFAR * TOTALWORK EST_END_TIME
  FROM V$ADVISOR_PROGRESS
 WHERE SOFAR <> TOTALWORK
   AND SOFAR <> 0
 ORDER BY 3;

如果在執行過程中cancel掉，再次對sqlset操作會爆出如下錯誤：
SQL> EXEC DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name   => 'SPA_SQLSET', basic_filter  => 'executions<3');
BEGIN DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name   => 'SPA_SQLSET', basic_filter  => 'executions<1'); END;

*
ERROR at line 1:
ORA-13757: "SQL Tuning Set" "SPA_SQLSET" owned by user "SPA" is active.
ORA-06512: at "SYS.DBMS_SQLTUNE", line 5712
ORA-06512: at line 1

找到task然後刪除掉
SQL> SELECT TASK_ID, OWNER, TASK_NAME FROM DBA_ADVISOR_TASKS WHERE TASK_NAME LIKE 'SPA%' ORDER BY 1;

   TASK_ID OWNER                                              TASK_NAME
---------- -------------------------------------------------- --------------------------------------------------
      1235 SPA                                                SPA_TASK

SQL> EXEC DBMS_SQLPA.DROP_ANALYSIS_TASK('SPA_TASK');

PL/SQL procedure successfully completed.

刪除sqlset
EXEC DBMS_SQLTUNE.DROP_SQLSET('SPA_SQLSET', 'SPA');

如果SQL資訊太多，為了便於我們儘快的分析，我們需要抓取最需要分析的SQL，比如執行次數、該SQL的執行使用者、執行module，綁定變數做篩選：

刪除未使用綁定變數的sql
CREATE INDEX IDX_SQLSET11_TAB_F_S ON SQLSET11_TAB(FORCE_MATCHING_SIGNATURE, SQL_ID) PARALLEL 8;
BEGIN
FOR X IN(SELECT FORCE_MATCHING_SIGNATURE, MIN(SQL_ID) SQL_ID FROM SQLSET11_TAB
          GROUP BY FORCE_MATCHING_SIGNATURE
          HAVING COUNT(*) > 1)
LOOP
  DELETE FROM SQLSET11_TAB WHERE FORCE_MATCHING_SIGNATURE = X.FORCE_MATCHING_SIGNATURE AND SQL_ID <> X.SQL_ID;
  COMMIT;
END LOOP;
END;
/

刪除執行次數小於10次的SQL
exec dbms_sqltune.delete_sqlset(sqlset_name=>'SPA_SQLSET',basic_filter=>'executions<10',sqlset_owner=>'SPA');

刪除非指定使用者的SQL
delete from spa.sqlset11_tab where PARSING_SCHEMA_NAME not in ('ACCOUNTING','SPS');

刪除指定module比如PL/SQL Developer的SQL
delete from spa.sqlset11_tab where MODULE='PL/SQL Developer';