ITPub博客

首页 > 数据库 > Oracle > 一次SQL Performance Analyzer的使用过程

一次SQL Performance Analyzer的使用过程

原创 Oracle 作者:oliseh 时间:2014-10-17 11:29:02 1 删除 编辑


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

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

 

--将生产库awr中的负荷loadsql tuning set

declare

begin

DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name=>'chhsts1',description=>'chhsts1',sqlset_owner=>'SYSTEM');

end;

/

 

--加了commit_rows参数之后可以在load时观察DBA_SQLSETstatement_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内容exportCHHSTS1_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_RECCM_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_tasksessionid可以看到每条语句的执行次数都为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_RECCM_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 trialSQLPA执行的语句,发现对于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;

/

 

--测试库上执行结果比对生成报告,报告支持texthtmlxmlactive四种方式

***生成报告格式为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%的情况才会体现在报告中


***后面紧接着的是报告中的量化数据,就是这部分数据反应了性能变好or变差,负值表示变差,impact on workload表明这条sql语句对于整体性能的影响,impact on sql表明语句本身的性能变化情况,plan change=y表示执行计划发生了变化


***生成报告格式为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格式报告界面如下



  --
阅读报告,对于有问题的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

--报告中显示了这条语句的各项性能参数及其对整体性能的影响



--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


--性能问题解决了



--最后附上一些关于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


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

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1641734