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 < 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';