一次SQL Performance Analyzer的使用过程

港控/mmm° 2022-08-01 00:11 4阅读 0赞

数据库版本升级、变更系统参数前使用SQL Performance Analyzer可以衡量升级前后、参数变更前后SQL语句的执行性能是否有变化，以及这些变化对于整体性能的影响程度，对于性能恶化的sql语句结合SQL Tuning Advisor可是进一步实现调优，确保系统性能在升级或者参数变更后依然维持稳定。关于SPA的详细介绍可以参考” Real Application Testing User's Guide”。本文对于如何实施SPA的完整过程做了一个演示，大家如果要用到SPA可以直接往里套，不用去看手册上的繁琐介绍。

SPA核心步骤有三步：变更前执行SQL->变更后执行SQL-->变更前后的性能比对，为了模拟出SQL语句性能下降进而影响系统整体性能的效果，演示中将按照如下过程进行：”1.变更前执行SQL-->2.drop掉SQL所访问的某张表上的索引来模拟变更的动作\-->3.变更后执行SQL-->4.变更前后性能比对(此时访问这张表的SQL性能会有明显下降)-->5.使用SQL Tuning Advisor调优\-->6.再次作性能比对(此时SQL性能恢复到变更前状态)

\--将生产库awr中的负荷load到sql tuning set里

declare

begin

DBMS\_SQLTUNE.CREATE\_SQLSET(sqlset\_name=>'chhsts1',description=>'chhsts1',sqlset\_owner=>'SYSTEM');

end;

\--加了commit\_rows参数之后可以在load时观察DBA\_SQLSET的statement\_count字段了解load进度

DECLARE

type chhtype is ref cursor;

cur chhtype;

BEGIN

OPEN cur FOR SELECT VALUE(P)  FROM table(dbms\_sqltune.select\_workload\_repository(begin\_snap=>17740,end\_snap=>17741,recursive\_sql=>dbms\_sqltune.NO\_RECURSIVE\_SQL)) P;

dbms\_sqltune.load\_sqlset(sqlset\_name=>'chhsts1',populate\_cursor=>cur,commit\_rows=>2);

CLOSE cur;

END;

\--查看sqlset内容，可以使用下面任何语句

select value(p) from table(dbms\_sqltune.select\_sqlset(sqlset\_name=>'chhsts1')) p;

select \* from dba\_sqlset\_statements where sqlset\_name='chhsts1';

\--生产库上将STS内容export到CHHSTS1\_TAB表

declare

begin

DBMS\_SQLTUNE.CREATE\_STGTAB\_SQLSET (table\_name=>'CHHSTS1\_TAB',schema\_name=>'SYSTEM',tablespace\_name=>'TS\_PUB');

end;

declare

begin

dbms\_sqltune.pack\_stgtab\_sqlset(sqlset\_name=>'chhsts1',sqlset\_owner=>'SYSTEM',staging\_table\_name=>'CHHSTS1\_TAB',staging\_schema\_owner=>'SYSTEM');

end;

\--传输chhsts1\_tab表到测试数据库，将表内容导入到测试库的STS，测试库上的数据必须尽可能和生产库一致，我环境里的测试库数据是通过存储底层复制的方式从生产库拷贝而来，所以测试库和生产库上的数据完全相同，省去了将生产库数据导出至测试库的步骤

\*\*生产库expdp

expdp system/shzw\_2013 tables=chhsts1\_tab directory=hisdmp logfile=exp\_chhsts1\_tab.log dumpfile=chhsts1\_tab.dmp

\*\*测试库impdp

impdp system/abcd\_1234 directory=hisdmp logfile=imp\_chhsts1\_tab.log dumpfile=chhsts1\_tab.dmp

\*\*从表中将sql语句导入测试库的STS

begin

dbms\_sqltune.UNPACK\_STGTAB\_SQLSET(sqlset\_name=>'chhsts1',sqlset\_owner=>'SYSTEM',replace=>FALSE,staging\_table\_name=>'CHHSTS1\_TAB',staging\_schema\_owner=>'SYSTEM');

end;

\--测试库上创建analysis task，名称chhtask1

set serveroutput on

declare

v\_taskname varchar2(1000);

begin

v\_taskname:=DBMS\_SQLPA.CREATE\_ANALYSIS\_TASK(sqlset\_name => 'chhsts1',task\_name => 'chhtask1');

dbms\_output.put\_line(v\_taskname);

end;

\--测试库上create pre-change sql trial，设定超时时间20分钟，不应用session中设定的环境变量，这里为节省运行时间，只针对特定的SQL语句生成pre-change sql trial即用basic\_filter参数限制只针对CA\_BNK\_ZDZ\_DS\_PAY\_REC、CM\_RES\_LIFECYCLE两张表的访问语句进行优化

\*\*为了观察analyze task的效果先清空shared pool，实际大家做的时候不需要这一步

alter system flush shared\_pool;

declare

begin

DBMS\_SQLPA.EXECUTE\_ANALYSIS\_TASK(task\_name=>'chhtask1',execution\_type=>'TEST EXECUTE',execution\_name=>'chhexec\_pre1',execution\_params=>dbms\_advisor.arglist('APPLY\_CAPTURED\_COMPILENV', 'NO','TIME\_LIMIT','1200','basic\_filter','sql\_text like ''%select t.\* from ad.CA\_BNK\_ZDZ\_DS\_PAY\_REC%'' or sql\_text like ''%CM\_RES\_LIFECYCLE%'''));

end;

\*\*执行完后从shared pool里看到了以/\* SQL Analyze(5663,0) \*/开头的语句，这些都是SQLPA发起的，5663代表执行execute\_analysis\_task的sessionid可以看到每条语句的执行次数都为10次，第1次执行oracle称为warm buffer，也就是排除掉物理磁盘因素对SQL语句执行的干扰，真正计入统计的是后面9次的执行性能平均值

select sql\_text,executions from v$sql where sql\_text like '%CA\_BNK\_ZDZ\_DS\_PAY\_REC%' or sql\_text like '%CM\_RES\_LIFECYCLE%';

SQL\_TEXT         EXECUTIONS

/\* SQL Analyze(5663,0) \*/ select t.\* from ad.CA\_BNK\_ZDZ\_DS\_PAY\_REC t where t.TRANSACTION\_ID=:1  and t.ACTION\_DATE=:2          10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_0 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_1 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_10 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_11 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_12 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_13 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_14 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_15 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_16 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_17 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_18 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_19 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_2 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_20 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_21 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_22 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_23 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_24 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_25 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_26 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_27 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_28 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_29 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_3 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_30 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_31 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_32 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_33 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_34 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_35 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_36 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_37 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_38 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_39 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_4 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_40 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_41 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_42 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_43 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_44 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_45 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_46 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_47 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_48 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_49 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_5 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_6 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_7 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_8 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_9 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10

\--测试库上执行变更，为模拟实现变更后sql语句执行性能下降的效果，删除掉ad.CA\_BNK\_ZDZ\_DS\_PAY\_REC表字段transaction\_id上的索引

drop index ad.INX\_CA\_BNK\_ZDZ\_DS\_PAY\_REC\_1;

\--测试库上create post-change sql trial，设定超时时间20分钟，不应用session中设定的环境变量，同样利用basic\_filter只针对CA\_BNK\_ZDZ\_DS\_PAY\_REC、CM\_RES\_LIFECYCLE这两套表进行分析

alter system flush shared\_pool;

declare

begin

DBMS\_SQLPA.EXECUTE\_ANALYSIS\_TASK(task\_name=>'chhtask1',execution\_type=>'TEST EXECUTE',execution\_name=>'chhexec\_post1',execution\_params=>dbms\_advisor.arglist('APPLY\_CAPTURED\_COMPILENV', 'NO','TIME\_LIMIT','1200','basic\_filter','sql\_text like ''%select t.\* from ad.CA\_BNK\_ZDZ\_DS\_PAY\_REC%'' or sql\_text like ''%CM\_RES\_LIFECYCLE%'''));

end;

\--再次查询post\_change sql trial时SQLPA执行的语句，发现对于CA\_BNK\_ZDZ\_DS\_PAY\_REC表的语句执行次数只有两次，这是因为表上的索引被drop，语句采用的是FTS执行计划，执行2次共用了42秒，对于这类耗时长的语句oracle最多执行2次，第1次也是起到warm作用，体现在报告中的应该是第2次的性能值

select sql\_text,executions from v$sql where sql\_text like '%CA\_BNK\_ZDZ\_DS\_PAY\_REC%' or sql\_text like '%CM\_RES\_LIFECYCLE%';

SQL\_TEXT         EXECUTIONS    ELAPSED\_TIME

/\* SQL Analyze(5663,0) \*/ select t.\* from ad.CA\_BNK\_ZDZ\_DS\_PAY\_REC t where t.TRANSACTION\_ID=:1  and t.ACTION\_DATE=:2          2       42796162

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_0 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6507

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_1 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6261

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_10 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         2095

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_11 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6513

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_12 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         2986

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_13 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6113

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_14 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6320

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_15 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6451

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_16 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6236

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_17 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6554

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_18 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6350

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_19 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6497

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_2 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6658

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_20 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6808

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_21 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6344

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_22 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         2069

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_23 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6280

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_24 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         9791

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_25 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         2560

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_26 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6221

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_27 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6219

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_28 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6398

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_29 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6314

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_3 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6579

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_30 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6549

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_31 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6203

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_32 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         7464

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_33 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6522

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_34 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6321

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_35 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6236

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_36 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6225

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_37 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6455

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_38 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         7576

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_39 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6273

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_4 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6463

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_40 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6286

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_41 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6522

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_42 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6284

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_43 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6734

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_44 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6463

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_45 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6571

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_46 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6511

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_47 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         5889

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_48 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6297

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_49 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2 10         6310

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_5 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6242

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_6 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6537

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_7 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6240

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_8 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6480

/\* SQL Analyze(5663,0) \*/ select t.\* from CD.CM\_RES\_LIFECYCLE\_9 t where t.RESOURCE\_ID=:1  and t.EXPIRE\_DATE>:2    10         6323

\--基于前面两次采样结果，执行性能比较任务

declare

begin

DBMS\_SQLPA.EXECUTE\_ANALYSIS\_TASK(task\_name => 'chhtask1',execution\_type => 'COMPARE PERFORMANCE', execution\_name => 'chhexec\_comp1', execution\_params =>dbms\_advisor.arglist('EXECUTION\_NAME1','chhexec\_pre1','EXECUTION\_NAME2','chhexec\_post1'));

end;

\--测试库上执行结果比对生成报告，报告支持text、html、xml、active四种方式

\*\*\*生成报告格式为HTML

set serveroutput on

set heading off

set pagesize 2000

set long 20000

spool /home/oracle/spa\_chhexec\_comp1.html

select DBMS\_SQLPA.REPORT\_ANALYSIS\_TASK('chhtask1','HTML','ALL','ALL') from dual;

spool off

\*\*报告首部的概要部分：各次执行开始结束时间、比较的指标缺省是elapsed\_time，性能影响超过1%的情况才会体现在报告中

![53956_1413516175ueqe.png][]

or变差，负值表示变差，sql语句对于整体性能的影响，plan change=y表示执行计划发生了变化

![53956_1413516291V21r.png][]

\*\*\*生成报告格式为ACTIVE，这种格式的报告是图形界面的，看效果有点像OEM，它在数据库关闭时依然能查看，前提是打开html文件的机器必须能连接到http://download.oracle.com/otn\_software才能动态加载内容

set serveroutput on

set heading off

set pagesize 2000

set long 20000

spool /home/oracle/spa\_chhexec\_comp1\_active.html

select DBMS\_SQLPA.REPORT\_ANALYSIS\_TASK('chhtask1','ACTIVE','ALL','ALL') from dual;

spool off

\*\*\*ACTIVE格式报告界面如下

![53956_1413516381u2ZD.png][]

--阅读报告，对于有问题的sql语句sql\_id=f7qmfbcgqjy0p进行下钻分析，object\_id=153是从report summary中获得的，生成html格式的报告

set serveroutput on

set heading off

set pagesize 2000

set long 20000

spool /home/oracle/spa\_chhexec\_comp1\_detail.html

SELECT DBMS\_SQLPA.REPORT\_ANALYSIS\_TASK(task\_name => 'chhtask1',type=>'HTML',object\_id =>153) from dual;

spool off

<span "="" style="word-wrap: break-word; font-size: 10.5pt;">--报告中显示了这条语句的各项性能参数及其对整体性能的影响

![53956_1413516421BORr.png][]

\--tune regressed sql，针对spa结果中性能下降的语句创建调优任务

declare

v\_result varchar2(1000);

begin

v\_result:=DBMS\_SQLTUNE.CREATE\_TUNING\_TASK(spa\_task\_name => 'chhtask1',spa\_task\_owner => 'SYSTEM',task\_name=>'tune\_regre1',spa\_compare\_exec => 'chhexec\_comp1');

end;

\--生成优化建议

declare

begin

DBMS\_SQLTUNE.EXECUTE\_TUNING\_TASK(task\_name => 'tune\_regre1');

end;

\--通过dba\_advisor\_系列视图了解优化建议

\*\*Sql Tuning Advisor推荐的调优方法

col task\_name format a20

set linesize 120

select task\_name,finding\_id,type from dba\_advisor\_recommendations where task\_name='tune\_regre1';

TASK\_NAME            FINDING\_ID TYPE

\-------------------- ---------- ------------------------------

tune\_regre1                   1 PARALLEL EXECUTION

tune\_regre1                   2 INDEX

tune\_regre1                   3 ALTERNATE PLAN

\*\*实现调优所要执行的操作，oracle推荐使用并行执行的sql profile来提高语句的执行效率，也推荐使用sql Access Advisor来为CA\_BNK\_ZDZ\_DS\_PAY\_REC表选择合适的索引，我们当然选择后者

col attr1 format a10

col attr2 format a10

col attr3 format a10

col message format a50

col command format a30

set linesize 160

set pagesize 200

select task\_name,execution\_name,command,attr1,attr2,attr3,message from DBA\_ADVISOR\_actions where task\_name='tune\_regre1';

TASK\_NAME  EXECUTION\_NAME       COMMAND                        ATTR1      ATTR2      ATTR3      MESSAGE

\---------- -------------------- ------------------------------ ---------- ---------- ---------- --------------------------------------------------

tune\_regre EXEC\_139802          ACCEPT SQL PROFILE             tune\_regre 2          PARALLEL E Consider accepting the recommended SQL profile to

1                                                              1                     XECUTION   use parallel execution for this statement.

tune\_regre EXEC\_139802          CREATE INDEX                   AD.IDX$$\_2            AD.CA\_BNK\_ Consider running the Access Advisor to improve the

1                                                              F3230001              ZDZ\_DS\_PAY  physical schema design or creating the recommende

\_REC       d index.

tune\_regre EXEC\_139802          UNDEFINED                                                       All alternative plans other than the Original Plan

1         could not be reproduced in the current environment

\--下面来生成实现上述调优操作所需的命令

set long 2000

SELECT DBMS\_SQLTUNE.SCRIPT\_TUNING\_TASK('tune\_regre1','ALL') FROM DUAL;

DBMS\_SQLTUNE.SCRIPT\_TUNING\_TASK('TUNE\_REGRE1','ALL')

\--------------------------------------------------------------------------------

\-----------------------------------------------------------------

\-- Script generated by DBMS\_SQLTUNE package, advisor framework --

\-- Use this script to implement some of the recommendations    --

\-- made by the SQL tuning advisor.                             --

\--                                                             --

\-- NOTE: this script may need to be edited for your system     --

\--       (index names, privileges, etc) before it is executed. --

\-----------------------------------------------------------------

create index AD.IDX$$\_2F3230001 on AD.CA\_BNK\_ZDZ\_DS\_PAY\_REC("TRANSACTION\_ID","ACTION\_DATE");

\--重新建上索引

create index AD.IDX$$\_2F3230001 on AD.CA\_BNK\_ZDZ\_DS\_PAY\_REC("TRANSACTION\_ID","ACTION\_DATE");

\--第三次进行SQL语句的执行采样

declare

begin

DBMS\_SQLPA.EXECUTE\_ANALYSIS\_TASK(task\_name=>'chhtask1',execution\_type=>'TEST EXECUTE',execution\_name=>'chhexec\_post2',execution\_params=>dbms\_advisor.arglist('APPLY\_CAPTURED\_COMPILENV', 'NO','TIME\_LIMIT','1200','basic\_filter','sql\_text like ''%select t.\* from ad.CA\_BNK\_ZDZ\_DS\_PAY\_REC%'' or sql\_text like ''%CM\_RES\_LIFECYCLE%'''));

end;

declare

begin

DBMS\_SQLPA.EXECUTE\_ANALYSIS\_TASK(task\_name => 'chhtask1',execution\_name=>'chhcomp\_comp1',execution\_type=>'compare performance',execution\_params=>dbms\_advisor.arglist('EXECUTION\_NAME1','chhexec\_pre1','EXECUTION\_NAME2','chhexec\_post2','WORKLOAD\_IMPACT\_THRESHOLD',0,'SQL\_IMPACT\_THRESHOLD',0));

end;

\--第二次生成比较报告

set long 20000

set pagesize 2000

spool /home/oracle/spa\_2nd\_comp.html

select DBMS\_SQLPA.REPORT\_ANALYSIS\_TASK('chhtask1','HTML','ALL','ALL') from dual;

spool off

\--性能问题解决了

![53956_1413516497YZ5W.png][]

\--最后附上一些关于task的有用的视图

\*\*dba\_advisor\_executions包含了task明细

select task\_name,advisor\_name,execution\_type from dba\_advisor\_tasks;

TASK\_NAME            ADVISOR\_NAME                   EXECUTION\_TYPE

\-------------------- ------------------------------ --------------------

chhtask1             SQL Performance Analyzer       COMPARE PERFORMANCE

tune\_regre1          SQL Tuning Advisor             TUNE SQL

\*\*dba\_advisor\_executions包含了task里每次执行的任务状态

alter session set nls\_date\_format='yyyymmdd hh24:mi:ss';

col task\_name format a10

col execution\_name format a20

col execution\_type format a20

set linesize 140

select task\_name,execution\_name,execution\_type,execution\_start,execution\_end,advisor\_name,status from dba\_advisor\_executions

TASK\_NAME  EXECUTION\_NAME       EXECUTION\_TYPE       EXECUTION\_START   EXECUTION\_END     ADVISOR\_NAME                   STATUS

\---------- -------------------- -------------------- ----------------- ----------------- ------------------------------ -----------

chhtask1   chhexec\_post1        TEST EXECUTE         20141016 14:27:17 20141016 14:27:22 SQL Performance Analyzer       COMPLETED

chhtask1   chhexec\_pre1         TEST EXECUTE         20141016 13:44:11 20141016 13:44:47 SQL Performance Analyzer       COMPLETED

\*\*dba\_advisor\_log包含了每个任务的执行日志记录，当前进度等

col task\_name format a30

select task\_name,execution\_start,execution\_end,status from dba\_advisor\_log

TASK\_NAME                      EXECUTION\_START   EXECUTION\_END     STATUS

\------------------------------ ----------------- ----------------- -----------

chhtask1                       20141016 19:51:27 20141016 19:51:28 COMPLETED

tune\_regre1                    20141016 19:59:11 20141016 20:01:41 COMPLETED

[53956_1413516175ueqe.png]: http://blog.itpub.net/attachment/201410/17/53956_1413516175ueqe.png
[53956_1413516291V21r.png]: http://blog.itpub.net/attachment/201410/17/53956_1413516291V21r.png
[53956_1413516381u2ZD.png]: http://blog.itpub.net/attachment/201410/17/53956_1413516381u2ZD.png
[53956_1413516421BORr.png]: http://blog.itpub.net/attachment/201410/17/53956_1413516421BORr.png
[53956_1413516497YZ5W.png]: http://blog.itpub.net/attachment/201410/17/53956_1413516497YZ5W.png