9i升级到11g用SPA做性能测试

这是一个自己负责实施的项目，由于原来的版本是9i，比较老，而且是跨版本升级到11g，所以不能简单用sqlset迁移做性能测试，下面简单介绍下我的操作方法：
--在生产库中利用10046抓取SQL Trace，由于要抓的trace比较多，所以临时放在其他空间足够的目录里
alter system set user_dump_dest='/oradata_rman_bak/udump' scope=memory;
alter system set events '10046 trace name context forever , level 4';

alter system set events '10046 trace name context off';
alter system set user_dump_dest='/oracle/admin/c3prd/udump' scope=memory;

然后利用alter system获取10046 trace要格外小心，他会追踪开启10046这个时间点之后的所有会话，如果在这期间有rman备份，那会是灾难，数据库会有严重的性能问题，而且如果会话运行结束了，但是没有断开，这个会话会一直被追踪，所以我还是建议用脚本单独开启或停止用户会话的追踪，也可以避免一个系统的定期job，脚本可以参考如下，你也可以根据生产的实际情况进行调整：

CREATE OR REPLACE PROCEDURE P_ENABLE_TRACE(I_ENABLE_RANGE NUMBER)

AS

BEGIN

  IF (I_ENABLE_RANGE = 0) THEN

    FOR X IN (SELECT SID, SERIAL# SERIAL

                FROM V$SESSION

               WHERE MACHINE NOT IN ('xhdb-server3','xhdb-server4')

                 AND USERNAME NOT IN ('SYS','SYSTEM','PERFSTAT')) LOOP

      DBMS_SUPPORT.START_TRACE_IN_SESSION(X.SID, X.SERIAL, TRUE, FALSE);

    END LOOP;

  ELSE

    FOR X IN (SELECT SID, SERIAL# SERIAL

                FROM V$SESSION

               WHERE MACHINE NOT IN ('xhdb-server3','xhdb-server4')

                 AND USERNAME NOT IN ('SYS','SYSTEM','PERFSTAT')

                 AND LOGON_TIME > SYSDATE-I_ENABLE_RANGE/1440) LOOP

      DBMS_SUPPORT.START_TRACE_IN_SESSION(X.SID, X.SERIAL, TRUE, FALSE);

    END LOOP;

  END IF;

END;

/

在生产库抓取的所有trace文件，都需要打包传输到测试服务器上，并在测试数据库中进行SQL重演。

--在生产库中创建Mapping表
CREATE TABLE MAPPING_TABLE 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;

--导出生产库的Mapping表，并传到测试库
exp \'/ as sysdba\' prdmapping.dmp log=exp_mapping_table.log tables=MAPPING_TABLE

--将Mapping表导入到测试数据库中
imp \'/ as sysdba\' file=/oradata_rman_bak/udump/prdmapping.dmp log=imp_mapping_table.log full=y

从生产库传过来的trace文件，需要统一放在一个指定的目录中，然后需要在测试库中创建指向trace目录的数据库目录对象。
create directory DIR_TRACE as '/udump/udump';

需要在测试库中创建一个SQL Tuning Set，且将生产库抓取的trace文件信息导入到测试数据库中：
declare
mycur dbms_sqltune.sqlset_cursor;
begin
dbms_sqltune.create_sqlset('9i_prod_wkld_ora92');
open mycur for select value(p)
from table(dbms_sqltune.select_sql_trace(
directory => 'DIR_TRACE',
file_name => '%ora%',
mapping_table_name => 'MAPPING_TABLE',
select_mode => dbms_sqltune.SINGLE_EXECUTION)) p;
dbms_sqltune.load_sqlset( sqlset_name => '9i_prod_wkld_ora92',
populate_cursor => mycur,
commit_rows => 1000);
close mycur;
end;
/

需要在测试库创建一个SPA工作任务，用于进行本次的SQL性能比较。
variable sts_task VARCHAR2(64);
EXEC :sts_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK( -
task_name => '9i_11g_spa_ora92', -
description => 'Upgrade from 9208 to 11203, SQL Testing', -
sqlset_name => '9i_prod_wkld_ora92');

创建SPA工作任务之后，需要从STS(SQL Tuning Set)中获取所有的SQL在原生产库9i中的执行信息：
exec dbms_sqlpa.execute_analysis_task( -
  task_name   => '9i_11g_spa_ora92', -
  execution_name => '9i_trial_ora92', -
  execution_type => 'CONVERT SQLSET', -
  execution_desc => '9i sql trial generated from STS');

然后在原生产库抓取到的SQL，都需要在测试库中执行，并记录其执行信息。
exec dbms_sqlpa.execute_analysis_task( -
task_name => '9i_11g_spa_ora92',-
execution_name => '11g_trial_ora92_4',-
execution_type => 'TEST EXECUTE',-
execution_desc => 'remote test-execute trial on 11g db');

创建9i和11g的各自trail之后，可以对比两次执行的SQL统计信息。

 exec dbms_sqlpa.execute_analysis_task( -
task_name => '9i_11g_spa_ora92', -
execution_name => 'compare_9i_112_elapsed_4', -
execution_type => 'COMPARE PERFORMANCE', -
execution_params => dbms_advisor.arglist( -
'COMPARISON_METRIC', 'ELAPSED_TIME', -
'EXECUTION_NAME1','9i_trial_ora92', -
'EXECUTION_NAME2','11g_trial_ora92_4'), -
execution_desc => 'Compare 9i elapsed time');

其中 COMPARISON_METRIC参数可以有多个选择，比如有elapsed_time，CPU_TIME，BUFFER_GETS

做完SQL性能对比之后，可以从数据库中取出SQL性能变化报告。我们主要关注3中类型的SQL性能报告：
1、 所有SQL性能变化；
2、 所有不被SPA支持的SQL列表
3、 所有执行出现错误的SQL列表
对于这三种类型的报告，分别可以使用以下方式获取：
--设置环境
set lines 188 pages 9999 long 999999 trim on trims on
--获取所有SQL的性能变化情况
spool spa_ora92_elapsed_20130422.html
select xmltype(dbms_sqlpa.report_analysis_task( -
'9i_11g_spa_ora92', 'html', 'all', 'all', null, 100, -
'compare_9i_112_elapsed_4')).getclobval(0,0) from dual;
spool off

--获取不支持的SQL列表
spool spa_ora92_elapsed_unsupported_20130422.html
select xmltype(dbms_sqlpa.report_analysis_task( -
'9i_11g_spa_ora92', 'html', 'unsupported', 'all', null, 100, -
'compare_9i_112_elapsed_4')).getclobval(0,0) from dual;
spool off

--获取所有执行出错的SQL列表
spool spa_ora92_elapsed_errors_20130422.html
select xmltype(dbms_sqlpa.report_analysis_task( -
'9i_11g_spa_ora92', 'html', 'errors', 'all', null, 100, -
'compare_9i_112_elapsed_4')).getclobval(0,0)-
from dual;
spool off

最后打开html文件查看是否有性能下降的sql或其他错误信息，可以及时纠正，以免正式升级后出现问题。

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

转载于:http://blog.itpub.net/21560888/viewspace-1718630/