2135 积分	502 好友	184 主题

发消息

[SQL调优] 11g spa sql performance analyzer

1^#

发表于 2013-9-21 18:38:28 | 查看: 3713| 回复: 1

1.  SPA介绍

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

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

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

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

2.  SPA实施步骤
2.1  9i库上收集必要的trace信息,创建MAPPING表
（1）根据现场提供的信息，我们在9i的proplh数据库上收集SQL的trace.
通过10046 event level 4跟踪活动的会话：

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

  收集完成后将以上trace event关闭

  alter system set events '10046 trace name context off';

从3月12日14:54分开始收集，到3月12日17：07分结束收集。一共收集语句666条。

（2）建立MAP表

在源环境创建MAPPING表。

create table mapping_table tablespace USERS 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;

2.2 创建测试环境
创建测试必要的10g(测试目标库)和11g(SPA测试需要)2个库。 11g库建议使用dbca选择general purpose模板创建即可。将源库数据导入到10g测试目标库中，并进一步确认相关实例参数和统计信息收集情况。

2.3 进行SPA测试
（1）IMP导入MAPPING表到测试环境

exp \'/ AS SYSDBA\' tables=mapping_table file=mapping.dmp （9i）

imp \'/ AS SYSDBA\' tables=mapping_table file=mapping.dmp （10g）

11g创建Directory

create directory UDUMP as '/proplh/Archivelog/trace/udump';

（2）在测试环境,用生产环境采集的TRACE文件生成SQL TUNNING SET(简称STS)

declare

mycur dbms_sqltune.sqlset_cursor;

begin

dbms_sqltune.create_sqlset('9i_prod_wkld');

open mycur for

select value(p)

from table(dbms_sqltune.select_sql_trace(

directory=>'UDUMP',

file_name=>'%trc',

mapping_table_name => 'MAPPING_TABLE',

select_mode => dbms_sqltune.single_execution)) p;

dbms_sqltune.load_sqlset(

sqlset_name => '9i_prod_wkld',

populate_cursor => mycur,

commit_rows => 1000);

close mycur;

end;

/

（3）在目标测试环境(10g)中创建必要的用户并赋予相关权限，并在11g中创建DBLINK

SQL> create user spadba identified by oracle;

User created.

SQL> grant dba to spadba;

Grant succeeded.

SQL> grant all on dbms_sqlpa to spadba;

Grant succeeded.

在11g中创建DBLINK

SQL> create public database link proplh10g connect to spadba identified by oracle using 'proplh';

Database link created.

SQL>

SQL> select * from tab@proplh10g;

TNAME TABTYPE CLUSTERID

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

TV TABLE

（4）在测试环境,创建SPA任务

var sts_task varchar2(64);

exec :sts_task:= dbms_sqlpa.create_analysis_task(task_name => '9i_10g_spa',description => 'experiment for 9.2.0.4 to 10.2.0.5 upgrade',sqlset_name=> '9i_prod_wkld');

（5）在测试环境,运行SPA任务(9i)

var exe_task varchar2(64);

exec :exe_task:=dbms_sqlpa.execute_analysis_task(task_name=>'9i_10g_spa',execution_name=>'9i_trail',execution_type=>'CONVERT SQLSET',execution_desc=>'9i sql trail generated from sts');

（6）在测试环境,运行SPA任务(10g)

set timing on;

var exe_task varchar2(64);

exec :exe_task:= dbms_sqlpa.execute_analysis_task(task_name=>'9i_10g_spa',execution_name=>'10g_trail',execution_type=>'TEST EXECUTE',execution_desc=>'10g trail test',execution_params=>dbms_advisor.arglist('DATABASE_LINK','PROPLH10G'));

（7）在测试环境,运行SPA任务(比较两次运行结果)

比较CPU_TIME

EXEC dbms_sqlpa.execute_analysis_task( -

task_name => '9i_10g_spa', -

execution_name => 'compare_9i_10g_cpu', -

execution_type => 'COMPARE PERFORMANCE', -

execution_params => dbms_advisor.arglist('COMPARISON_METRIC','CPU_TIME','EXECUTION_NAME1','9i_trail','EXECUTION_NAME2','10g_trail'), -

execution_desc => 'Compare 9i SQL Trace Performance to 10g Test-Execute for CPU_TIME')

比较BUFFER_GETS

EXEC dbms_sqlpa.execute_analysis_task( -

task_name => '9i_11g_spa1', -

execution_name => 'compare_9i_10g_buffergets', -

execution_type => 'COMPARE PERFORMANCE', -

execution_params => dbms_advisor.arglist('COMPARISON_METRIC','BUFFER_GETS','EXECUTION_NAME1','9i_trail1','EXECUTION_NAME2','10g_trail4'), -

    execution_desc => 'Compare 9i SQL Trace  Performance to 10g Test-Execute for BUFFER_GETS')

（8）在测试环境，提取分析报告
产生cpu 比较report

set heading off long 100000000 longchunksize 10000 echo off;

set linesize 1000 trimspool on;

spool cputime_summary.html

select xmltype(dbms_sqlpa.report_analysis_task('9i_11g_spa1',

'html',

'typical',

'all',

null,

100,

'compare_9i_11g_cpu')).getclobval(0,0)

from dual;

spool off

产生buffergets 比较report

set heading off long 100000000 longchunksize 10000 echo off;

set linesize 1000 trimspool on;

spool buffer_gets.html

select xmltype(dbms_sqlpa.report_analysis_task('9i_11g_spa1',

'html',

'typical',

'all',

null,

100,

'compare_9i_10g_buffergets')).getclobval(0,0)

from dual;

  spool off

产生errors比较report

spool errors_summary.html

select xmltype(dbms_sqlpa.report_analysis_task('9i_10g_spa',

'html',

'errors',

'summary',

null,

100,

'10g_trail')).getclobval(0,0)

from dual;

spool off

产生unsupport比较report

spool unsuppor_all.html

select xmltype(dbms_sqlpa.report_analysis_task('9i_10g_spa',

'html',

'unsupported',

'all',

null,

100,

'10g_trail')).getclobval(0,0)

from dual;

spool off

3. SPA报告分析
CPU比较

Overall Impact	:	-39.44%
Improvement Impact	:	6.63%
Regression Impact	:	-46.07%

SQL Statement Count

SQL Category	SQL Count	Plan Change Count
Overall	666	283
Improved	2	1
Regressed	14	14
Unchanged	635	268
with Errors	12	0
Unsupported	3	0

上述报告可以看出，本次测试运行666条SQL语句。两条语句的性能提升了，14条语句的CPU TIME有所上升，635条语句的性能没有显著变化，12条语句发生了错误，还有3条语句是SPA不支持的INSERT语句。

共有283条语句的执行计划发生了变化，这是由于10g中使用CBO优化器引起的。

SPA显示升级后cpu上升的原因包括：
1. 目标测试主机的CPU配置较源库差
2. 升级到10g后默认采用CBO优化器，CBO优化器在硬解析语句时消耗的CPU比源库使用的RBO高，这是因为CBO的算法比RBO复杂很多

性能变差的非系统递归语句列在附录中。

BUFFERGETS比较

Overall Impact	:	28.03%
Improvement Impact	:	32.33%
Regression Impact	:	-4.3%

SQL Statement Count

SQL Category	SQL Count	Plan Change Count
Overall	666	283
Improved	3	2
Regressed	1	1
Unchanged	647	280
with Errors	12	0
Unsupported	3	0

上诉报告可以看出，本次测试运行666条SQL语句。3条语句的性能提升了，647条语句的性能没有显著变化，12条语句发生了错误，还有3条语句是SPA不支持的INSERT语句。
由于此处性能下降的一条语句是系统递归语句，且其逻辑读增加并不多，所以不在此列出。

DISKREADS比较

Overall Impact	:	79.29%
Improvement Impact	:	79.29%
Regression Impact	:	0%

SQL Statement Count

SQL Category	SQL Count	Plan Change Count
Overall	666	283
Improved	14	11
Unchanged	637	272
with Errors	12	0
Unsupported	3	0

上述报告可以看出，没有语句的物理读明显恶化了。

遇到的错误

object_id	sql_id	Error Message
76	1x25z29v9g8sg	ORA-00905: missing keyword
90	2998g29666p96	ORA-00911: invalid character
122	2ysbz8jq4xsss	ORA-01756: quoted string not properly terminated
134	35t9qgk9afyn3	ORA-00942: table or view does not exist
159	3q43507801d6a	ORA-01756: quoted string not properly terminated
167	3vv430ss1q2qk	ORA-01756: quoted string not properly terminated
292	6pw7c28f66v18	ORA-00905: missing keyword
355	86x3nu2xavkjg	ORA-01756: quoted string not properly terminated
431	9tchcj4w00nsw	ORA-00911: invalid character
548	czjhxc0mnsgsd	ORA-00942: table or view does not exist
645	gf37dzz8d6nxj	ORA-00905: missing keyword
663	gxx05a0dr5q4z	ORA-00942: table or view does not exist

以上这些SQL_ID对应的语句由于在运行时遇到了错误而没有成功测试。

不支持的语句

object_id	sql_id	sql_text
59	1dsrac0fk8qdg	insert into C_USER_OPTLOG (optid, optype, opunitcode, op
180	4388kxvhfb80g	INSERT into rec_sms_info(sid,unitcode,handlercode,smstim
555	d3ra3cpr0rmm4	insert into IN_SYSTEM_LOG values(SEQSYSTEMLOG_SEQ.NEXTVA

由于SPA仅支持测试DML语句的查询部分，所以对于以上三条INSERT语句是不支持测试的。

分享0

下载专业ORACLE数据库恢复工具PRM-DUL For Oracle http://www.parnassusdata.com/zh-hans/emergency-services

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线： 13764045638 QQ: 47079569

收藏0 回复只看该作者道具举报