当前位置：文档库 › ORACLE-技术文档-ORACLE11G新特性-SPA使用指南-V121120

ORACLE-技术文档-ORACLE11G新特性-SPA使用指南-V121120

ORACLE-技术文档-11G新特性-

SPA使用指南-

（v120726）

版本说明

版本说明 (2)

1. 概述 (4)

2. SPA 过程 (4)

2.1. 简述 (4)

2.2. 工作流程 (4)

2.3.捕捉有代表性的SQL工作负载 (5)

2.3.1. 创建SQL调整集（STS） (5)

2.3.2. 生产库加载SQL调整集 (5)

2.3.3. 传送SQL调整集 (6)

2.3.4. 将STS导入测试系统 (7)

2.4. 创建SPA任务 (8)

2.4.1. 在测试库创建一个SPA任务 (8)

2.5. 分析更改前SQL工作负载 (8)

2.6. 分析升级后的sql工作负载 (9)

2.7. 比较SQL性能 (9)

2.7.1. 为了比较升级前和升级后SQL性能,需要第三次执行EXECUTE_ANALYSIS_TASK过程. (9)

2.7.2. 生成SPA报表 (10)

2.7.3. 分析性能报表 (12)

3. 参考文档 (12)

1.概述

ORACLE11G 的新特性SPA,可对给定SQL结果集进行性能分析,特别适合在有大的动作(比如升级\迁移等)做前后的性能比较。

分析结果以永久对象存在数据库内部，可供以后查询和修改。

2.SPA 过程

2.1.简述

SQL工作负载不仅包括SQL语句，还包括环境信息（绑定变量和执行频率）。

2.2.工作流程

2.3.捕捉有代表性的SQL工作负载

2.3.1.创建SQL调整集（STS）

SQL> exec dbms_sqltune.create_sqlset(sqlset_name=>'sql_test',description=>'11g spa test'); PL/SQL procedure successfully completed.

2.3.2.生产库加载SQL调整集

2.3.2.1. 从当前的缓存加载SQL语句作为调整集.

SQL> DECLARE

2 mycur DBMS_SQLTUNE.SQLSET_CURSOR;

3 BEGIN

4 OPEN mycur FOR

5 SELECT VALUE(P)

6 FROM table(

7 DBMS_SQLTUNE.SELECT_CURSOR_CACHE(

8 'parsing_schema_name <> ''SYS'' AND elapsed_time > 500000',

9 NULL, NULL, NULL, NULL, 1, NULL,

10 'ALL')) P;

12 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'sql_test',

13 populate_cursor => mycur);

15 END;

/ 16

PL/SQL procedure successfully completed.

2.3.2.2. 从AWR报告提取SQL语句作为调整集

SQL> EXEC DBMS_SQLTUNE.CREATE_SQLSET('my_workload');

SQL>

SQL> DECLARE

2 cur DBMS_SQLTUNE.SQLSET_CURSOR;

3 BEGIN

4 OPEN cur FOR

5 SELECT VALUE(P)

6 FROM table(

7 DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(24,27,

8 'parsing_schema_name <> ''SYS''',

9 NULL, NULL,NULL,NULL,

10 1,

11 NULL,

12 'ALL')) P;

14 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_workload',

15 populate_cursor => cur,

16 load_option => 'MERGE',

17 update_option => 'ACCUMULATE');

18 END;

19 /

2.3.3.传送SQL调整集

在能进行传送之前,需要在生产库创建一个中转表作为下个环节的导出源.

2.3.3.1. 创建中转表

SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET(table_name =>'STGTAB_SQLSET');

此表不能创建在SYS模式下.

2.3.3.2. 将SQL调整结果集导入到中转表

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET(sqlset_name=>'sql_test',staging_table_name=>'STGTAB_SQLSET');

从中转表选择数据,验证是否有数据

SQL> select count(*) from STGTAB_SQLSET;

说明已经存在数据了.

2.3.4.将STS导入测试系统

2.3.4.1. 先使用数据泵将中转表数据导出和导入

[oracle@ora11g ~]$ expdp system DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=REPLAY_DIR TABLES=STGTAB_SQLSET

Export: Release 11.2.0.1.0 - Production on Tue Jul 24 00:45:38 2012

Password:

UDE-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=REPLAY_DIR TABLES=STGTAB_SQLSET

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 576 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION

. . exported "SYSTEM"."STGTAB_SQLSET" 100.7 KB 72 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/home/oracle/replay_dir/STGTAB_SQLSET.dmp

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 00:47:36

2.3.4.2. 将此表导入到测试系统的system用户下

[oracle@11g replay_dir]$ impdp system DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=REPLAY_DIR

Import: Release 11.2.0.1.0 - Production on Tue Jul 24 01:20:51 2012

Password:

UDI-28002: operation generated ORACLE error 28002

ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=REPLAY_DIR

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/PRE_TABLE_ACTION

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SYSTEM"."STGTAB_SQLSET" 100.7 KB 72 rows

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 01:23:20

2.3.4.3. 在测试库解压中转表的数据到STS

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name=>'sql_test',replace=>true ,staging_table_name=>'STGTAB_ SQLSET');

PL/SQL procedure successfully completed.

2.4.创建SPA任务

2.4.1.在测试库创建一个SPA任务

SQL> variable sts_task VARCHAR2(64);

SQL> exec :sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'sql_test',task_name=>'spa_task1');

PL/SQL procedure successfully completed

2.5.分析更改前SQL工作负载

现实环境中，通常源库和测试库的版本至少差一个版本，比如源库可能是10.2版本，而测试库版本是11G,如果源库的版本是10G，需要在测试库将参数：optimizer_features_enable 设置为10.2.0,将此参数设置为此10.2.0后,生成的性能数据就是升级前的数据了.

本案例由于是为了演示整个SPA的过程,源库和测试库的版本都是11G的,所以上述参数环节就不用设置了.直接进行性能抓取即可.

SQL>

exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'spa_task1',execution_type=>'test execute',execution_name=>'before_change');

PL/SQL procedure successfully completed.

一定注意黄色部分,test 和execute 之前不是下划线,而是空格.

2.6.分析升级后的sql工作负载

如果是由于版本升级做的SPA,在此处需要将optimizer_features_enable 设置为11.2.0,本次案例由于不是这个目的,所以此参数不用处理.

SQL>

exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'spa_task1',execution_type=>'test execute',execution_name=>'after_change');

PL/SQL procedure successfully completed.

2.7.比较SQL性能

2.7.1.为了比较升级前和升级后SQL性能,需要第三次执行

EXECUTE_ANALYSIS_TASK过程.

SQL> exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'spa_task1',execution_type=>'compare performance');

PL/SQL procedure successfully completed.

2.7.2.生成SPA报表

SQL> variable report1 clob;

SQL>

exec :report1 := DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name=>'spa_task1',type=>'text',level=>'typical',section=>'summary');

PL/SQL procedure successfully completed.

SQL> SQL> set long 100000 longchunksize 100000 linesize 120

SQL> print :report1

REPORT1

------------------------------------------------------------------------------------------------------------------------

General Information

---------------------------------------------------------------------------------------------

Task Information: Workload Information:

--------------------------------------------- ---------------------------------------------

Task Name : spa_task1 SQL Tuning Set Name : sql_test

Task Owner : SYSTEM SQL Tuning Set Owner : SYSTEM

Description : Total SQL Statement Count : 10

Execution Information:

---------------------------------------------------------------------------------------------

REPORT1

------------------------------------------------------------------------------------------------------------------------

Execution Name : EXEC_89 Started : 07/24/2012 02:56:05 Execution Type : COMPARE PERFORMANCE Last Updated : 07/24/2012 02:56:06 Description : Global Time Limit : UNLIMITED

Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED

Status : COMPLETED Number of Errors : 2

Number of Unsupported SQL : 3

Analysis Information:

---------------------------------------------------------------------------------------------

Before Change Execution: After Change Execution:

--------------------------------------------- ---------------------------------------------

REPORT1

------------------------------------------------------------------------------------------------------------------------ Execution Name : before_change Execution Name : after_change Execution Type : TEST EXECUTE Execution Type : TEST EXECUTE

Scope : COMPREHENSIVE Scope : COMPREHENSIVE Status : COMPLETED Status : COMPLETED Started : 07/24/2012 02:24:25 Started : 07/24/2012 02:33:36 Last Updated : 07/24/2012 02:25:04 Last Updated : 07/24/2012 02:33:38 Global Time Limit : UNLIMITED Global Time Limit : UNLIMITED

Per-SQL Time Limit : UNUSED Per-SQL Time Limit : UNUSED

Number of Errors : 2 Number of Errors : 2

---------------------------------------------

REPORT1

------------------------------------------------------------------------------------------------------------------------ Comparison Metric: ELAPSED_TIME

------------------

Workload Impact Threshold: 1%

--------------------------

SQL Impact Threshold: 1%

----------------------

Report Summary

---------------------------------------------------------------------------------------------

Projected Workload Change Impact:

REPORT1

------------------------------------------------------------------------------------------------------------------------

-------------------------------------------

Overall Impact : 6.44%

Improvement Impact : 6.44%

Regression Impact : 0%

SQL Statement Count

-------------------------------------------

SQL Category SQL Count Plan Change Count

Overall 10 0

Improved 2 0

Unchanged 3 0

REPORT1

------------------------------------------------------------------------------------------------------------------------

with Errors 2 0

Unsupported 3 0

Top 5 SQL Sorted by Absolute Value of Change Impact on the Workload

---------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 28 | a4vxhbv8nxt57 | 4.25% | 1 | 55276 | 49395 | 10.64% | n | | 31 | c99yw1xkb4f1u | 2.19% | 1 | 19309 | 16283 | 15.67% | n |

REPORT1

------------------------------------------------------------------------------------------------------------------------

| 29 | a50nw0ap6kv2c | .85% | 1 | 60288 | 59112 | 1.95% | n | | 26 | 767pug2dbpqpc | .05% | 3 | 1075 | 1052 | 2.14% | n | | 24 | 02suhrf4z78n3 | .03% | 2 | 145 | 127 | 12.41% | n | -----------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

2.7.

3.分析性能报表

如上报表的黄色部分为报表的主要部分,报表最后给出了分析汇总.

3.参考文档

ORACLE 的官方文档

Oracle? Database PL/SQL Packages and Types Reference

11g Release 2 (11.2)

Part Number E16760-05

和

Oracle? Database Real Application Testing User's Guide

11g Release 2 (11.2)

Part Number E16540-03