【www.bbyears.com--php应用】
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 <
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";