SQL Performance Analyzer SPA常用脚本汇总

标签: sql performance analyzer | 发表时间:2013-03-18 09:50 | 作者:liu_maclean
出处:http://blog.csdn.net

SPA常用脚本汇总

附件为 一个SPA报告  spa_buffergets_summary

 

SQL 性能分析器 SQL Performance Analyzer SPA

Oracle Database 11g 引入了 SQL 性能分析器;使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响。SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响。这种功能可向 DBA 提供有关 SQL 语句性能的详细信息,例如,执行前后的统计信息,提高或降低性能的语句。这样一来,您就可以执行诸如以下操作的操作:在测试环境中进行更改,以确定数据库升级是否会改进工作量性能。

 

  1. 11g 的新增功能
  2. 目标用户:DBA、QA、应用程序开发人员
  3. 帮助预测系统更改对 SQL 工作量响应时间的影响
  4. 建立不同版本的 SQL 工作量性能(即 SQL 执行计划和执行统计信息)
  5. 以串行方式执行 SQL(不考虑并发性)
  6. 分析性能差异
  7. 提供对单个 SQL 的细粒度性能分析
  8. 与 SQL 优化指导集成在一起以优化回归

SQL 性能分析器:使用情形 
SQL 性能分析器可用于预测和防止会影响 SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括(但不限于)以下任何一种更改:

  1. 数据库升级
  2. 实施优化建议
  3. 更改方案
  4. 收集统计信息
  5. 更改数据库参数
  6. 更改操作系统和硬件

 

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秒钟

 

 

begin
DBMS_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 set
EXEC DBMS_SQLTUNE.CREATE_SQLSET('MAC_SPA');
-- populate the tuning set from the cursor cache
DECLARE
 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 on
select 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 on
exec 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_TIME
EXEC 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_GETS
EXEC 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.html
select 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.html
select 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.html
select 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/dbmssupp

exec 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 as
select 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 all
select 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>;
作者:liu_maclean 发表于2013-3-18 9:50:01 原文链接
阅读:0 评论:0 查看评论

相关 [sql performance analyzer] 推荐:

ORACLE SQL Performance Analyzer的使用

- - CSDN博客数据库推荐文章
通过 SPA,您可以根据各种更改类型(如初始化参数更改、优化器统计刷新和数据库升级)播放特定的. SQL 或整个 SQL 负载,然后生成比较报告,帮助您评估它们的影响.. 在 Oracle Database 11g 之前的版本中,我必须捕获所有 SQL 语句,通过跟踪运行这些语句,. 然后得到执行计划 — 这是一项极其耗时又极易出错的任务.

SQL Performance Analyzer SPA常用脚本汇总

- - CSDN博客数据库推荐文章
附件为 一个SPA报告  spa_buffergets_summary. SQL 性能分析器 SQL Performance Analyzer SPA. Oracle Database 11g 引入了 SQL 性能分析器;使用该工具可以准确地评估更改对组成工作量的 SQL 语句的影响. SQL 性能分析器可帮助预测潜在的更改对 SQL 查询工作量的性能影响.

『Google发布移动Web性能工具PCAP Web Performance Analyzer』

- - jackyrong
最近,Google的Page Speed团队一直在关注如何更好的优化移动Web应用性能. 虽然我们拥有类似Firebug、Page Speed、YSlow!和Chrome/Safari Developer Tools等优秀工具,但是移动浏览器缺少这样的性能分析利器. 为此,Page Speed团队发布了一个分析移动浏览器网络信息的工具——PCAP Web Performance Analyzer.

Eclipse Memory Analyzer简介

- - Java - 编程语言 - ITeye博客
Eclipse Memory Analyzer是一个非常棒的堆内存分析工具,是JDK自带的堆分析工具jhat的一个非常好的替代品,能够快速地定位Java内存泄露的原因.       可能有的同学会问,JVM不是号称自动内存管理,GC会自动垃圾回收,Java怎么会有内存泄露,不会搞错吧.       在开始分析之前,我们先想想,在编程这个角度上,我们如何避免堆内存泄露呢.

MySQL的Performance Schema库

- - 数据库 - ITeye博客
Performance Schema是MySQL自带的、较为底层的性能监控特性,提供一系列、具备自定义收集粒度的监控体系. 对MySQL服务执行过程中的各项事件(Events)的分析尤为重视. Performance Schema的精细化控制,主要通过performance_schema库下的一系列setup表来实现.

Elasticsearch Performance Tuning Practice at eBay

- -
Elasticsearch is an open source search and analytic engine based on Apache Lucene that allows users to store, search, analyze data in near real time. This document summarizes the challenges as well as the process and tools that the Pronto team builds to address the challenges in a strategic way.

[译] 使用 APK Analyzer 分析你的 APK

- - IT瘾-dev
本文来自“天天P图攻城狮”公众号(ttpic_dev). 本文是对 《Analyze Your Build with APK Analyzer》 的翻译. Android Studio 2.2包含了APK Analyzer,通过它我们能够直观地看到APK的组成. 使用APK Analyzer不仅能够减少你花在debug上的时间,而且还能减少你的APK大小.

tsung: 好文: PHP performance tips - Google Webmaster

- 小汐 - Planet DebianTW
Google 提供的 PHP 效能調校(Optimize performance)的幾點原則, 詳細可見此文:. 原文: Let's make the web faster - PHP performance tips. 作者: Eric Higgins, Google Webmaster. 此文內容很短, 講得都是大方向, 主題內容如下:.

使用performance api监测页面性能

- - jackyrong
对于前端开发来说,知道整个页面从开始加载到有内容展示出来的时间是很重要的事情. 通常我们要知道页面加载的时间的话.是采用计算几个关键的时间点的方法来得出页面加载的时间.但是这个方式存在一些问题,比如:我们不知道浏览器在开始解析页面之前卸载前一个文档,解析dns的时间. 那么performance API是啥,能做啥和不能做啥呢.

18 Command Line Tools to Monitor Linux Performance(转)

- - Linux - 操作系统 - ITeye博客
This list of commands shown here are very enough for you to pick the one that is suitable for your monitoring scenario.. Top command is a performance monitoring program which is used frequently by many system administrators to monitor Linux performance and it is available under many.