SPA常用脚本汇总
附件为 一个SPA报告 spa_buffergets_summary
SQL 性能分析器 SQL Performance Analyzer SPA
Oracle Database 11g 引入了 SQL 性能分析器；使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样一来，您就可以执行诸如以下操作的操作：在测试环境中进行更改，以确定数据库升级是否会改进工作量性能。
- 11g 的新增功能
- 目标用户：DBA、QA、应用程序开发人员
- 帮助预测系统更改对 SQL 工作量响应时间的影响
- 建立不同版本的 SQL 工作量性能（即 SQL 执行计划和执行统计信息）
- 以串行方式执行 SQL（不考虑并发性）
- 分析性能差异
- 提供对单个 SQL 的细粒度性能分析
- 与 SQL 优化指导集成在一起以优化回归
SQL 性能分析器：使用情形
SQL 性能分析器可用于预测和防止会影响 SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：
- 数据库升级
- 实施优化建议
- 更改方案
- 收集统计信息
- 更改数据库参数
- 更改操作系统和硬件
DBA 甚至可以使用 SQL 性能分析器为最复杂的环境预测先期更改导致的 SQL 性能更改。例如，随着应用程序在开发周期中的变化，数据库应用程序开发人员可以测试对方案、 数据库对象和重写应用程序的更改，以减轻任何潜在的性能影响。
使用 SQL 性能分析器还可以比较 SQL 性能统计信息。
SQL 性能分析器：概要
1. 收集 SQL：在这个阶段中，将收集用于表示生产系统中的 SQL 工作量的 SQL 语句集。可以使用 SQL 优化集或自动工作量资料档案库 (AWR) 来捕获要传送的信息。因为 AWR 本质上是捕获高负载的 SQL，所以应考虑修改默认的 AWR 快照设置和捕获的顶级 SQL，以确保 AWR 捕获最大数量的 SQL 语句。这可以确保捕获更加完整的 SQL 工作量。
2. 传送：在这个阶段中，应将得到的工作量结果传送到测试系统。从生产系统导出 STS，然后将 STS 导入到测试系统。
3. 计算“之前版本”性能：在进行任何更改之前，执行 SQL 语句，收集评估将来的更改对工作量性能的可能影响所需的基线信息。在此阶段收集的信息给出了系统工作量当前状态的一个快照。性能数据包括：
4. 进行更改：获得了之前版本数据后，可以实施计划的更改，然后开始查看对性能的影响。
5. 计算“之后版本”性能：在数据库环境中进行了更改之后才执行此步骤。SQL 工作量的每个语句都在虚拟执行（仅收集统计信息）模式下运行，收集与步骤 3 所捕获的信息相同的信息。
6. 比较和分析 SQL 性能：在获得了两个版本的 SQL 工作量性能数据后，可以通过比较之后版本与之前版本的数据来进行性能分析。比较的根据是执行统计信息，如所用时间、CPU 时间和缓冲区获取次数等。
7. 优化回归的 SQL：在此阶段中，已经准确地确认了哪些 SQL 语句在进行数据库更改时可能导致性能问题。在此阶段中可以使用任何一种数据库工具来优化系统。例如，可以对确认的语句使用 SQL 优化指导或访问指导，然后实施相应的建议。也可以使用在步骤 3 中捕获的计划植入 SQL 计划管理 (SPM) 以确保计划保持不变。在实施了任何优化操作后，应重复该过程来创建新的之后版本，然后分析性能差异以确保新的性能是可接受的。
默认情况下SPA若涉及到DML语句则只有查询部分Query会被执行，但是貌似是从11.2开始可以执行完全的DML了，需要加入参数EXECUTE_FULLDML，但是该参数目前有一些BUG:
Bug 10428438 : WITH EXECUTE_FULLDML ROWS IS ALWAYS SET TO 0 11.2.0.1
Bug 14635522 : SPA SHOULD CAPTURE AND REPLAY TRANSACTIONS 11.2.0.3
By default, only the query portion of DMLs is executed. Using APIs, you can execute the full DML by using the EXECUTE_FULLDML task parameter.EXECUTE_FULLDML when set to TRUE executes DML statement fully, including acquiring row locks and modifying rows; When EXECUTE_FULLDML is set to FALSE (the default value is false) to execute only the query part of the DML without modifying data. When TRUE, SQL Performance Analyzer will issue a rollback following DML execution to prevent persistent changes from being made by the DML. So SPA does not make make any change to the data in the tables.
执行方法如下：
execute DBMS_SQLPA.SET_ANALYSIS_TASK_PARAMETER(task_name => 'TASK_21137', - parameter => 'EXECUTE_FULLDML', - value => 'TRUE');
从cursor cache中收集tuning set, 持续12分钟，间隔5秒钟
beginDBMS_SQLTUNE.CREATE_SQLSET (sqlset_name => 'MAC_SPA');dbms_sqltune.capture_cursor_cache_sqlset(sqlset_name => 'MAC_SPA' ,time_limit => 12*60,repeat_interval => 5);end ;/basic_filter=> q'# module like 'DWH_TEST%' and sql_text not like '%applicat%' and parsing_schema_name in ('APPS') #'basic_filter => 'sql_text LIKE ''%my_objects%'' and parsing_schema_name = ''SPA_TEST_USER''',==>过滤条件使用
从当前cursor cache中匹配条件 获得SQLset ROW
SELECT sql_id, sql_text FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500')) ORDER BY sql_id;SELECT * FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''4rm4183czbs7j''')); DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT value(P) FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE) P; -- Process each statement (or pass cursor to load_sqlset). CLOSE cur;END;/ -- create the tuning setEXEC DBMS_SQLTUNE.CREATE_SQLSET('MAC_SPA');-- populate the tuning set from the cursor cacheDECLARE cur DBMS_SQLTUNE.SQLSET_CURSOR;BEGIN OPEN cur FOR SELECT VALUE(P) FROM table( DBMS_SQLTUNE.SELECT_CURSOR_CACHE( 'parsing_schema_name <> ''SYS'' AND elapsed_time > 5000000', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MAC_SPA', populate_cursor => cur);END;/
从AWR快照中加载SQLset ROW到SQL TUNING SET
DECLARE cur sys_refcursor;BEGIN OPEN cur FOR SELECT VALUE (P) FROM table(dbms_sqltune.select_workload_repository(4146,4161)) P; -- Process each statement (or pass cursor to load_sqlset) DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'MAC_SPA', populate_cursor => cur); CLOSE cur;END;/
将SQL TUNING SET Pack到表中：
set echo onselect name,statement_count from dba_sqlset;drop table maclean.pack_sqlset purge;exec DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('PACK_SQLSET','MACLEAN');exec DBMS_SQLTUNE.PACK_STGTAB_SQLSET('MAC_SPA','SYS','PACK_SQLSET','MACLEAN');SQL> desc maclean.pack_sqlset; Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(30) OWNER VARCHAR2(30) DESCRIPTION VARCHAR2(256) SQL_ID VARCHAR2(13) FORCE_MATCHING_SIGNATURE NUMBER SQL_TEXT CLOB PARSING_SCHEMA_NAME VARCHAR2(30) BIND_DATA RAW(2000) BIND_LIST SQL_BIND_SET MODULE VARCHAR2(48) ACTION VARCHAR2(32) ELAPSED_TIME NUMBER CPU_TIME NUMBER BUFFER_GETS NUMBER DISK_READS NUMBER DIRECT_WRITES NUMBER ROWS_PROCESSED NUMBER FETCHES NUMBER EXECUTIONS NUMBER END_OF_FETCH_COUNT NUMBER OPTIMIZER_COST NUMBER OPTIMIZER_ENV RAW(1000) PRIORITY NUMBER COMMAND_TYPE NUMBER FIRST_LOAD_TIME VARCHAR2(19) STAT_PERIOD NUMBER ACTIVE_STAT_PERIOD NUMBER OTHER CLOB PLAN_HASH_VALUE NUMBER PLAN SQL_PLAN_TABLE_TYPE SPARE1 NUMBER SPARE2 NUMBER SPARE3 BLOB SPARE4 CLOB
将测试对应 schema的数据和 上述PACK TABLE 导出导入到 目标测试库中：
set echo onexec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('MAC_SPA','SYS',TRUE,'PACK_SQLSET','MACLEAN');alter system flush buffer_cache;alter system flush shared_pool;
创建SPA任务 并运行;
var sts_task varchar2(64);exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '10g_11g_spa',description => 'experiment for 10gR2 to 11gR2 upgrade',sqlset_name=> 'MAC_SPA');PL/SQL procedure successfully completed.var exe_task varchar2(64);exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'10g_11g_spa',execution_name=>'10g_trail',execution_type=>'CONVERT SQLSET',execution_desc=>'10g sql trail');var exe_task varchar2(64);exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'10g_11g_spa',execution_name=>'11g_trail',execution_type=>'TEST EXECUTE',execution_desc=>'11g sql trail');
执行任务比较
比较CPU_TIMEEXEC dbms_sqlpa.execute_analysis_task( - task_name => '10g_11g_spa', - execution_name => 'compare_10g_112_cpu', - execution_type => 'COMPARE PERFORMANCE', - execution_params => dbms_advisor.arglist('COMPARISON_METRIC','CPU_TIME','EXECUTION_NAME1','10g_trail','EXECUTION_NAME2','11g_trail'), - execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for CPU_TIME') /比较BUFFER_GETSEXEC dbms_sqlpa.execute_analysis_task( - task_name => '10g_11g_spa', - execution_name => 'compare_10g_112_buffergets', - execution_type => 'COMPARE PERFORMANCE', - execution_params => dbms_advisor.arglist('COMPARISON_METRIC','BUFFER_GETS','EXECUTION_NAME1','10g_trail','EXECUTION_NAME2','11g_trail'), - execution_desc => 'Compare 10g SQL Trace Performance to 11g Test-Execute for BUFFER_GETS') /比较实际执行时长 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'SPA_TEST', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_elapsed_time', execution_params => dbms_advisor.arglist('execution_name1', '10g_trail', 'execution_name2', '11g_trail', 'comparison_metric', 'elapsed_time') ); end; /比较物理读begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => '10g_11g_spa', execution_type => 'COMPARE PERFORMANCE', execution_name => 'Compare_physical_reads0', execution_params => dbms_advisor.arglist('execution_name1', '10g_trail', 'execution_name2', '11g_trail', 'comparison_metric', 'disk_reads') ); end; /Set the comparison_metric parameter to specify an expression of execution statistics to use in the performance impact analysis. Possible values include the following metrics or any combination of them: elapsed_time (default), cpu_time, buffer_gets, disk_reads, direct_writes, and optimizer_cost.
获得SPA报告:
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off spool spa_report_elapsed_time.html SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') FROM dual; spool off产生buffergets 比较report set heading off long 100000000 longchunksize 10000 echo off;set linesize 1000 trimspool on;spool buffergets_summary.htmlselect xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa', 'html', 'typical', 'all', null, 100, 'compare_10g_112_buffergets')).getclobval(0,0)from dual;spool off产生errors比较report spool errors_summary.htmlselect xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa', 'html', 'errors', 'summary', null, 100, '11g_trail')).getclobval(0,0)from dual;spool off产生unsupport比较report spool unsuppor_all.htmlselect xmltype(dbms_sqlpa.report_analysis_task('10g_11g_spa', 'html', 'unsupported', 'all', null, 100, '11g_trail')).getclobval(0,0)from dual;spool off
execution_type
Type of the action to perform by the function. If NULL it will default to the value of the DEFAULT_EXECUTION_TYPE parameter. Possible values are:
[TEST] EXECUTE – test-execute every SQL statement and collect its execution plans and execution statistics. The resulting plans and statistics will be stored in the advisor framework. This is default.
EXPLAIN PLAN – generate explain plan for every statement in the SQL workload. This is similar to the EXPLAIN PLAN command. The resulting plans will be stored in the advisor framework in association with the task.
COMPARE [PERFORMANCE] – analyze and compare two versions of SQL performance data. The performance data is generated by test-executing or generating explain plan of the SQL statements. Use this option when two executions of type EXPLAIN_PLAN or TEST_EXECUTE already exist in the task
CONVERT SQLSET – used to read the statistics captured in a SQL Tuning Set and model them as a task execution. This can be used when you wish to avoid executing the SQL statements because valid data for the experiment already exists in the SQL Tuning Set.
For 9i Upgrade to 10g
exec dbms_stats.gather_system_stats(gathering_mode=>'NOWORKLOAD');alter system set "_optim_peek_user_binds"=false; ==> 禁用BIND PEEK特性，该特性在10g中有exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );commit;9i ?/rdbms/admin/dbmssuppexec dbms_support.start_trace(binds=>TRUE, waits=> FALSE);exec dbms_support.stop_trace;exec dbms_support.start_trace_in_session(sid=>sid,serial=>ser, binds=>TRUE, waits=>FALSE);select sid,serial# from v$SESSION WHERE ... ;exec dbms_support.stop_trace_in_session(sid=>SID,serial=>ser);create table mapping_table tablespace USERS asselect object_id id, owner, substr(object_name, 1, 30) name from dba_objects where object_type not in ('CONSUMER GROUP', 'EVALUATION CONTEXT', 'FUNCTION', 'INDEXTYPE', 'JAVA CLASS', 'JAVA DATA', 'JAVA RESOURCE', 'LIBRARY', 'LOB', 'OPERATOR', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'QUEUE', 'RESOURCE PLAN', 'SYNONYM', 'TRIGGER', 'TYPE', 'TYPE BODY')union allselect user_id id, username owner, null name from dba_users;declare mycur dbms_sqltune.sqlset_cursor;begin dbms_sqltune.create_sqlset('9i_prod_wkld'); open mycur for select value(p) from table(dbms_sqltune.select_sql_trace( directory=>'SPADIR', file_name=>'%trc', mapping_table_name => 'MAPPING_TABLE', select_mode => dbms_sqltune.single_execution)) p; dbms_sqltune.load_sqlset( sqlset_name => '9i_prod_wkld', populate_cursor => mycur, commit_rows => 1000); close mycur;end;/create user spadba identified by oracle;grant dba to spadba;grant all on dbms_sqlpa to spadba;create public database link to10g connect to spadba identified by oracle using 'STRINGS';var sts_task varchar2(64);exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '9i_11g_spa1',description => 'experiment for 9i to 11gR2 upgrade',sqlset_name=> '9i_prod_wkld');var exe_task varchar2(64);exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'9i_11g_spa1',execution_name=>'9i_trail1',execution_type=>'CONVERT SQLSET',execution_desc=>'9i sql trail generated from sts');dbms_sqlpa.execute_analysis_task(task_name=>'9i_11g_spa1',execution_name=>'10g_trail1',execution_type=>'TEST EXECUTE',execution_desc=>'10g trail test',-execution_params=>dbms_advisor.arglist('DATABASE_LINK','DBLINKNAME'));select sofar,totalwork from V$ADVISOR_PROGRESS where task_id=<TID>;