《oracle11gSPA应用在9i升级到10gSQL性能测试》由会员分享，可在线阅读，更多相关《oracle11gSPA应用在9i升级到10gSQL性能测试（10页珍藏版）》请在金锄头文库上搜索。
1、SPA实施目的为了保证升级10g能更加平稳，我们采用Oracle 11g SQL 性能分析器(SQL Performance Analyzer，以下简称SPA)新功能来预测数据库的关键SQL在Oracle 10g平台上性能情况，以便提前发现问题并做相关的优化。大型业务关键应用程序要在响应时间、吞吐量、运行时间和可用性方面提供特定服务级别的保证。对系统的任何更改（如升级数据库或修改配置）通常都需要进行全面的测试和验证，然后才能在生产系统中实施这些更改。在移到生产系统之前为了保证安全，数据库管理员(DBA) 必须让测试系统承受与生产环境中的工作量很近似的压力，以便分析系统级更改对整体SQL 性能的
2、影响，并在在移到生产之前进行必要的优化。Oracle Database 11g 引入了SQL 性能分析器；使用该工具可以准确地预测系统更改对SQL 语句性能的影响。这种功能可向DBA 提供有关SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样，你就在测试环境中先进行更改，以确定数据库升级是否会影响SQL性能。SQL 性能分析器可用于预测和防止会影响SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：l 数据库升级l 实施优化建议l 更改方案l 收集统计信息l 更改数据库参数l 更改操作系统和硬件SQL 性能分
3、析器是11g的新功能，通过相关的配置可以应用在Oracle 9i升级到10g的过程中，大致的框架如下：先收集9i数据库的上的sql trace信息(需要涵盖关键业务的SQL)，然后在11g数据库上生成STS(SQL Tuning Set)和SQL统计信息，接着把这些SQL通过数据库链连接到10g 数据库上运行并把统计信息返回到11g库上，最后对每条SQL生成9i和10g上的对比报告，从这个报告便能发现SQL性能是否改善，执行计划有没有改变。SPA实施步骤9i库上收集必要的trace信息根据现场提供的信息，我们在9i的数据库选取典型的会话收集trace信息。比如我们选择进程号是23287的会话进
4、行跟踪：oradebug setospid 23287 oradebug unlimitoradebug event 10046 trace name context forever,level 4关闭跟踪：oradebug setospid 23287 oradebug event 10046 trace name context off还可以使用其他方法：1) 使用dbms_support跟踪数据库session?/rdbms/admin/dbmssuppexec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, waits=true, binds=fa
5、lse );exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null );2) 生成整个系统trcalter 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=TRUEma
6、x_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)安装JA
7、VA 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的环境，建议安装
8、最新的PSU补丁，需要安装相关的补丁(补丁参考Note:560977.1)。下面列出主要是9i数据库升级到10g数据库，11g测试数据库和10g升级数据库需要安装的补丁。升级前版本升级后版本安装补丁下载途径9.x1)使用11.1.0.6测试需要安装patch:6865809使用11.1.0.7测试，需要安装patch:87565942)10.2.0.2数据库需要安装patch:690332210.2.0.3数据库需要安装patch:690333510.2.0.4数据库需要安装patch:6893073从METALINK下载创建11g数据库到10g数据库的dblink以及11g数据库存放9i生成
9、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, PRO
10、CEDURE, 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数据库的DBLINKcreate public database link spa_link connect to system ident
11、ified 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 SP
12、A| 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- directory object specified: spa_dir| Specify the name of the mapping table to use| 输入 table_name 的
13、值: 9i_map- 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 files3_spa_first_sqltrial.sql：创建SPA task，根据在9i生成的trc产生执行计划和统计信息。SQL 3_spa_first_sqltrial.sql| 10 Most active
