SQL 性能分析器（SPA）工具概览

作为 Oracle Real Application Testing 选件/特性，这篇文章将提供一个关于 SQL 性能分析器（SPA）工具的简要概览。
DBA 的一个重要工作是确保在一个计划内的变更安排后，当前生产环境负载和 SQL 执行计划可以持续平滑运行。变更可能包含数据库升级、增加一个新索引或改变一个特定的数据库参数。 SPA 工具作为 Oracle Real Application Testing 选件的一部分提供，允许将生产环境负载中的 SQL 拿到测试环境的目标数据库运行，所以可以通过比对结果识别退化的性能问题，并在迁移、升级或特定系统变更之前修复。如果您计划使用数据库重演特性，在运行重演之前使用 SPA 是 Oracle 建议的最佳实践。目标是在数据库重演之前识别和修复所有的 SQL 性能退化，所以我们可以只关注重演特性中的并发和吞吐量 。SQL 性能分析器使用 SQL 调优集（STS）作为输入，STS 已经存在了很长一段时间了，这允许 DBA 提取现有生产环境的 SQL 语句工作负载并在节省时间和资源的前提下轻松比对一组变更前和变更后的执行结果。 一个 SQL 调优集（STS）是一个包含了一系列从工作负载中得来的 SQL 语句集及其执行上下文信息（例如用户和绑定变量、执行的统计信息和执行计划）的数据库对象。

如下清单提供了一些 DBA 考虑使用 SPA 工具的常见场景。 

使用场景

1.    数据库升级 – 一个新版本数据库意味着一个新版本的优化器。DBA 能在升级生产系统之前主动发现任何 SQL 性能退化。
2.    部署一个补丁 – 您可能会部署一个与性能或优化器相关的特定修复的补丁。使用 SPA 来检查您的生产环境 SQL 负载能帮助您验证这个补丁不会引起任何 SQL 性能退化。
3.    数据库初始化参数变更 - 有各种各样的数据库参数可能影响性能，所以这是 SPA 用处的一个很好的场景。
4.    Schema 变更例如增加索引 – schema 变更和修改，如增加索引会直接影响优化器的决定和计划。SPA 可用来测试这些变更并确保不会引入负面影响。
5.    改变或刷新优化器统计信息 – 优化器统计信息直接关系到优化器的决策和执行计划的生成，您可以使用 SPA 来测试新的统计信息和设置来确保它们不会引起 SQL 性能退化。 

使用 SPA 包括执行以下工作流文档/步骤。SPA 工具完全集成到 Oracle12c Cloud Control 中，Oracle 也提供了一个名为 DBMS_SQLPA 的 PLSQL 包来允许 DBA 使用 PL/SQL 实施这些步骤。这个工作流使用了一个迭代的过程来执行、对比和分析、以及修复这些退化。DBA 可使用诸如 SQL 执行计划基线或 SQL 调优顾问等工具/特性来修复 SPA 发现的坏或退化的 SQL 语句。

SPA 工作流
1.    捕捉您想要分析的生产系统的 SQL 工作负载，并将其保存为一个 SQL 调优集。
2.    设置目标测试系统（这应该尽可能多地和生产系统一致）。
3.    在测试系统创建一个 SPA 任务。

4.    构建变更前 SPA 任务。
5.    进行系统变更。
6.    构建变更后 SPA 任务。                                                              
7.    对比和分析变更前后的性能数据。
8.    调优或修复任何退化的 SQL 语句。
9.    重复地6~8步，直到 SQL 性能在测试系统上可接受。

出于本文的目的，我们将通过一个简单实例“给表增加一个索引 Schema 变更”来介绍。 

?    源数据库版本 12.1.0.2.0
?    目标测试系统 12.1.0.2.0
?    系统变更是给 t1 表增加一个索引 
?    性能报告将生成 HTML 格式的详细信息

SPA – 使用 PL/SQL API 的简单介绍


1.    捕获 SQL 工作负载到一个 SQL 调优集

创建和填充 STS

BEGIN 
  DBMS_SQLTUNE.DROP_SQLSET (sqlset_name  => 'MYSIMPLESTSUSINGAPI'); 
END;
/

BEGIN 
  DBMS_SQLTUNE.CREATE_SQLSET (sqlset_name  => 'MYSIMPLESTSUSINGAPI', description  => 'My Simple STS Using the API' );
END;
/

1a. 使用 SCOTT 用户运行一下 PLSQL 代码执行 SQL 语句。（PLSQL 用来模拟使用绑定变量的 SQL 语句工作负载） 
var b1 number;

declare
v_num number;
 begin
  for i in 1..10000 loop
  :b1 := i;
  select c1 into v_num from t1 where c1 = :b1;
 end loop;
end;
/

1b. 从游标缓存中找到使用 parsing schema=SCOTT 的语句来填充 STS

DECLARE
  c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR SELECT VALUE(p) FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name = ''SCOTT''', NULL, NULL, NULL, NULL, 1, NULL,'ALL')) p;
 DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'MYSIMPLESTSUSINGAPI', populate_cursor => c_sqlarea_cursor);
END;
/

1c. 检查 STS 中捕获了多少 SQL 语句

COLUMN NAME FORMAT a20
COLUMN COUNT FORMAT 99999
COLUMN DESCRIPTION FORMAT a30

SELECT NAME, STATEMENT_COUNT AS "SQLCNT", DESCRIPTION FROM   USER_SQLSET;

Results:

NAME                     SQLCNT DESCRIPTION
-------------------- ---------- ------------------------------
MYSIMPLESTSUSINGAPI          12 My Simple STS Using the API

1d. 显示 STS 的内容

COLUMN SQL_TEXT FORMAT a30   
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCOTT", SQL_TEXT,   ELAPSED_TIME AS "ELAPSED", BUFFER_GETS FROM   TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'MYSIMPLESTSUSINGAPI' ) );

Results: (partial)

SQL_ID        SCOTT                          SQL_TEXT                                               ELAPSED                  BUFFER_GETS
------------- ------------------------------ ------------------------------                                  ----------                     -----------
0af4p26041xkv SCOTT                 SELECT C1 FROM T1 WHERE C1 = :  169909252             18185689

2.  设置目标系统

为了演示目的，这里将使用 STS 的捕获源作为同样的目标测试系统。 

3.  创建 SPA 任务

VARIABLE t_name VARCHAR2(100);
EXEC :t_name := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => 'MYSIMPLESTSUSINGAPI', task_name => 'MYSPATASKUSINGAPI');
print t_name

Results:

T_NAME
-----------------
MYSPATASKUSINGAPI

4.  创建和执行变更前的 SPA 任务

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'TEST EXECUTE', execution_name => 'MY_BEFORE_CHANGE');


5.  做出系统变更

CREATE INDEX t1_idx ON t1 (c1);

6.  创建和执行变更后的 SPA 任务

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'TEST EXECUTE', execution_name => 'MY_AFTER_CHANGE');

7.  对比和分析变更前和变更后的性能

EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'MYSPATASKUSINGAPI', execution_type => 'COMPARE PERFORMANCE', execution_name => 'MY_EXEC_COMPARE', execution_params => dbms_advisor.arglist('comparison_metric', 'elapsed_time'));

-- Generate the Report

set long 100000000 longchunksize 100000000 linesize 200 head off feedback off echo off TRIMSPOOL ON TRIM ON
VAR rep   CLOB;
EXEC :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK('MYSPATASKUSINGAPI', 'html', 'typical', 'all');
SPOOL C:\mydir\SPA_detailed.html
PRINT :rep
SPOOL off


HTML 格式的报告示例：

下面是 SPA 报告的一部分截屏。报告由3个部分组成，一个部分涉及到变更前和变更后的任务包括 范围，状态，执行起始时间，错误个数和比较的标准；第二个部分总结部分包括了变更带来的负载影响；第三个部分包括了详细的 SQL 信息，比如 SQLID 以及需要比较的度量，比如对负载的影响，执行的频率，以及在这个例子里在变更前后的执行时间这个度量。比如对于 SQL ID 0af4p26041xkv 来说，负载影响是97%。我们要实施的变更对于性能有好的影响，可以把执行时间从12766降低到29。我们还可以看到执行计划在变更后发生了变化。这些信息可以帮助 DBA 进一步关注在特定的问题或者性能退化上，对于当前的这个例子，影响是对性能有提升。

下面的截屏显示了在增加了索引后执行计划的变化。这部分信息可以让 DBA 进一步关注在变更前后某个具体 SQL 的执行计划上。对任何 SQL 退化来说，这可以让 DBA 来清楚了解执行计划是如何变化的，并且可以进一步采取计划，比如使用 SQL Tuning Advisor 或者创建 SPM 基线。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1988494/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12798004/viewspace-1988494/