oracle11g SPA 应用在9i升级到10g SQL性能测试

SPA实施目的

为了保证升级10g能更加平稳，我们采用Oracle 11g SQL 性能分析器(SQL Performance Analyzer，以下简称SPA)新功能来预测数据库的关键SQL在Oracle 10g平台上性能情况，以便提前发现问题并做相关的优化。

大型业务关键应用程序要在响应时间、吞吐量、运行时间和可用性方面提供特定服务级别的保证。对系统的任何更改（如升级数据库或修改配置）通常都需要进行全面的测试和验证，然后才能在生产系统中实施这些更改。在移到生产系统之前为了保证安全，数据库管理员(DBA) 必须让测试系统承受与生产环境中的工作量很近似的压力，以便分析系统级更改对整体SQL 性能的影响，并在在移到生产之前进行必要的优化。

Oracle Database 11g 引入了SQL 性能分析器；使用该工具可以准确地预测系统更改对SQL 语句性能的影响。这种功能可向DBA 提供有关SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样，你就在测试环境中先进行更改，以确定数据库升级是否会影响SQL性能。

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

? 数据库升级 ? 实施优化建议 ? 更改方案 ? 收集统计信息 ? 更改数据库参数 ? 更改操作系统和硬件

SQL 性能分析器是11g的新功能，通过相关的配置可以应用在Oracle 9i升级到10g的过程中，大致的框架如下：

先收集9i数据库的上的sql trace信息(需要涵盖关键业务的SQL)，然后在11g数据库上生成STS(SQL T uning Set)和SQL统计信息，接着把这些SQL通过数据库链连接到10g 数据库上运行并把统计信息返回到11g库上，最后对每条SQL生成9i和10g上的对比报告，从这个报告便能发现SQL性能是否改善，执行计划有没有改变。

SPA实施步骤

9i库上收集必要的trace信息

根据现场提供的信息，我们在9i的数据库选取典型的会话收集trace信息。比如我们选择进程号是23287的会话进行跟踪： oradebug setospid 23287 oradebug unlimit

oradebug event 10046 trace name context forever,level 4 关闭跟踪：

oradebug setospid 23287

oradebug event 10046 trace name context off 还可以使用其他方法：

1) 使用dbms_support跟踪数据库session @?/rdbms/admin/dbmssupp

exec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, waits=>true, binds=>false ); exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null ); 2) 生成整个系统trc

alter system set events '10046 trace name context forever,level 12'; alter system set events '10046 trace name context off';

在9i数据库通过选取特定模块的会话进行跟踪，由于会产生大量的trc，考虑到磁盘空间的问题，建议指定user_dump_dest目录到剩余空间较多的文件系统， 设置下列参数： timed_statistics=TRUE

max_dump_file_size=UNLIMITED。

创建10g测试环境

10g测试环境版本必须是10.2.0.2以后的版本。SPA支持模式执行9i中的SQL,所以不会对数据做任何的改动，对目前的生产环境的性能几乎没有影响。

另外，为了实现SPA的功能，需要安装相关的补丁，还要安装JAVAVM等组件。 (补丁参

考Note:560977.1，组件参考Note:276554.1)。

10g的CBO优化器依赖于表的统计信息， 如果没有统计信息将会动态采样，动态采样对SQL性能和系统的资源有很大的影响，所以必须收集统计信息。 安装相关的组件在10.2版本的数据库(补丁参考Note: 276554.1) 安装JAVA VM

@?/javavm/install/initjvm.sql; 创建DBMS_XQUERYINT @?/rdbms/admin/initxqry.sql 创建OXQServer.class @?/rdbms/admin/initxml.sql 授予SYSTEM用户权限:

grant execute on DBMS_SQLPA to system ; grant advisor to system;

需要说明的是，虽然测试环境基本能模拟生产环境的数据量，但测试库和生产库的数据和压力还有一些差异的地方，所以最后的性能对比结果还要看看是否和环境有关。

创建11g SPA环境

要实现SPA必须要有11g的环境，建议安装最新的PSU补丁，需要安装相关的补丁(补丁参考Note:560977.1)。下面列出主要是9i数据库升级到10g数据库，11g测试数据库和10g升级数据库需要安装的补丁。

升级前版本 9.x 升级后版本 10.2.0.2/10.2.0.3/10.2.0.4 安装补丁 1)使用11.1.0.6测试需要安装patch:6865809 使用11.1.0.7测试，需要安装patch:8756594 推荐使用11.1.0.7 2)10.2.0.2数据库需要安装下载途径 从METALINK下载 patch:6903322 10.2.0.3数据库需要安装patch:6903335 10.2.0.4数据库需要安装patch:6893073 创建11g数据库到10g数据库的dblink以及11g数据库存放9i生成trc的目录： 创建11g上创建9i对象表，使用下面的脚本：

create table 9i_map 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;

在11g数据库上创建存放trc的目录：

create or replace directory spa_dir as '/archlog1/spa/ trace'; 在11g数据库上创建连接到10g数据库的DBLINK

create public database link spa_link connect to system identified by oracle using 'spa_test';

进行SPA测试

在11g上数据库进行SPA性能分析请参考Doc ID 742644.1。 1_create_mapping_table.sql：在9i数据库创建mapping表。

2_sqltrace2sts.sql：抽取9i数据库生成的trc生成SQL tuning set。有生成trc可能会非常大，可能会导致生成SQL tuning set非常慢。 SQL> @2_sqltrace2sts.sql

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | NOTE:

| 1- This script should be executed on the 11g system running SPA | 2- Trace files and mapping table should have already imported into | the 11g system which is running SPA

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Specify the name of the directory object containing the trace files

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

输入 dir_name 的值: spa_dir--<指定trc保存的目录

>> directory object specified: spa_dir

| Specify the name of the mapping table to use

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 table_name 的值: 9i_map--<输入创建mapping表

>> mapping table specified: 9i_map

| Specify the name of the SQL tuning set (STS) to create

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 sts_name 的值: spa_test---输入新创建的sts名称

>> SQL tuning set specified: spa_test

| Creating an empty SQL tuning set

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Populating SQL tuning set from the specified SQL trace files

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

3_spa_first_sqltrial.sql：创建SPA task，根据在9i生成的trc产生执行计划和统计信息。 SQL> @3_spa_first_sqltrial.sql

| 10 Most active SQL tuning sets

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

NAME OWNER SQL_COUNT ------------------------------ ------------------------------ ----------

spa_test SYS 0

| Specify the name and owner of SQL tuning set to use

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 sts_name 的值: spa_test 输入 sts_owner 的值:

>> SQL tuning set specified: spa_test owned by

| Specify the name of SQL performance analyzer task to create

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 task_name 的值: spa_task

>> SPA Task specified: spa_task

| Specify the name of the first SQL trial to create. This trial | will be created by converting the specified STS.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 first_trial 的值: first

>> SPA First SQL trial specified: first

| Run create a SPA analysis task

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

4_spa_second_sqltrial.sql：通过DBLINK在10.2数据库上执行相关SQL语句，生成执行计划和统计信息。

SQL> @4_spa_second_sqltrial.sql

| 10 Most active SQL performance analyzer tasks

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Specify the name of SPA task to use

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 tsk_name 的值: spa_task

>> SPA task specified: spa_task

| Specify the name of the second SQL trial to create. This trial | will be created by remotely test executing SQL statements | over a public database link

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 second_trial 的值: second

>> SPA second SQL specified: second

| 10 Most recent database links

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Specify the name of db_link to user by SPA

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 dblink 的值: spa_link

>> database link specified: spa_link

| Specify a per-SQL time limit to control long running queries

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 输入 per_sql_time_limit 的值:

>> per-SQL time limit specified:

5_spa_compare.sql：比较两次SQL测试，在选择compare_metric推荐使用CPU_TIME或者BUFFER_GETS。

执行rpt.sql生成报告。

SPA生成测试报告

数据库SPA测试

一般情况下会生成3个html文件：cpu_errors.html、cpu_regress.html、cpu_summary.html。 cpu_errors.html显示执行错误的sql语句，cpu_regress.html显示性能下降的sql语句，cpu_summary.html是比较两次sql语句执行的汇总。 以下是基于CPU_TIME的Summary Report：

总共采集了303条语句，没有性能下降的语句，有6条语句性能得到改善，还有113语句执行失败。

With Error的语句121条，可以分为两类： 一类是Insert语句，SPA工具不支持

另一类是和dblink相关的语句，因为相关的dblink在BOSS库上没有创建导致语句执行失败

SPA总结

正如所有的测试一样，SPA测试不可能发现所有升级后可能遇到的性能问题，由于环境的差异、场景的差异等等一些现实的问题，在升级后仍然有可能遇到一些预料之外的性能问题，但SPA可以尽可能减少这些问题。在SPA测试中发现的REGRESSED的SQL语句，我们可以提前分析解决。在升级后发现的新的REGRESSED的SQL语句，可以尝试通过10g提供的相关工具，如SQL Tuning Advisor、SQL Profile等分析解决。

由于测试时间有限，选取的会话数不能涵盖所有的应用场景，测试有一定的的局限性，所以还需要借助其他手段的测试，如功能测试、压力测试以及用户体验性测试等，从另一个角度验证升级后的性能变化。

本文来源：https://www.bwwdw.com/article/6trp.html