oracle spa性能测试,9i升级到11g用SPA做性能测试-CSDN博客

这是一个自己负责实施的项目，由于原来的版本是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)

BEGIN

(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或其他错误信息，可以及时纠正，以免正式升级后出现问题。