一次SQL Performance Analyzer的使用过程

最新推荐文章于 2023-08-12 22:46:36 发布
最新推荐文章于 2023-08-12 22:46:36 发布
阅读量1.7k 收藏
点赞数
分类专栏： 工具

数据库版本升级、变更系统参数前使用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%的情况才会体现在报告中


or变差，负值表示变差，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

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



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

Richard-Han
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
使用 SQL Performance Analyzer 进行数据库性能高级分析
05-29
本专题讲座将介绍如何使用量度扩展 (Metric Extension) 来诊断性能问题。量度扩展是 Oracle Enterprise Manager Cloud Control 12c 的一个新特性，具有丰富的功能，如多关键量度、设计和部署生命周期以及稳定可靠的...
Oracle定位不知源头的SQL
关注系统性能调优
03-11 3251
接到现场数据库维护厂商发出的警告，有一条 insert语句执行了几千秒。        开发排查，从select * from dba_source s where s.text like '%XXX%';查到是一个存储过程，于是找到存储过程，将其改名。        但问题没有解决，隔天又收到警告。        难道是存储过程改名有问题，我远程到现场看了下，并没有问题。再次查看数据库报告
收集表的统计信息时并发过高
huangliang0703的专栏
12-11 2558
一台数据库服务器报 cpu loader 过高，查看当前等待事件，发现对应sql： /* SQL Analyze(1) */ select /*+ full(t) parallel(t,64) parallel_index(t,64) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no
SQL 优化——一般步骤、索引问题、优化方法(ANALYZE、CHECK、OPTIMIZE)、常用 SQL 的优化
qq_39478853的博客
03-22 810
一、优化 SQL 语句的一般步骤1 通过 show status 命令了解各种 SQL 的执行频率　　MySQL 客户端连接成功后,通过【 show [session|global] status 】命令可以提供服务器状态信息,也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。show [session|global] status 可以根据需要加上参...
关于tomcat被锁后 ,报错could not delete的解决方法
wodetian1225的博客
07-13 9574
修改jsp文件tomcat发布失败（Could not delete May be locked by another process）突然项目修改jsp文件后，tomcat不能发布,Publishing failed with multiple errors  Could not delete D:/Tomcat 6.0/webapps/Server/WEB-INF/platform/confi...
第二章 SQL命令参考-ANALYZE
kygoal的博客
10-12 3982
ANALYZE Collectsstatistics about a database. 概要 ANALYZE [VERBOSE][ROOTPARTITION [ALL]] [table[(column [,...])]] 描述 分析有关的表的数据库中的系统表pg_statistic里的内容，并存储该结果收集统计信息。随后，Greenplum数据引擎使用这些数据来帮助判断查询的最有效的
oracle11g新特性--SQL Performance Analyzer
06-14
oracle11g新特性--SQL Performance Analyzer
Zabbix监控SQL Server服务状态的方法详解
12-14
SQL Server数据库的服务（SQL Server (MSSQLSERVER)、SQL Server Agent (MSSQLSERVER)等服务）一般配置成自动启动，但是有时候有些服务...以前我们用Solarwinds的Database Performance Analyzer监控SQL Sever服务状态，
sqlserver 性能分析工具大全
05-06
sqlserver 性能分析工具大全,包括uml,MPSreport,SQL Server 2005 Best Practices Analyzer ,SQL Server 2005 Performance Dashboard Reports,SQL Server Web Data Administrator,sqlnexus 2.5.0.7,Relog Tool,...
微软内部资料-SQL性能优化3
11-27
Intent locks improve performance because SQL Server examines intent locks only at the table level to determine whether a transaction can safely acquire a lock on that table. This removes the ...
oracle官方文档--DBMS_SQLPA
mengxiangfeiyang的专栏
06-17 2881
DBMS_SQLPA The DBMS_SQLPA package provides the interface SQL Performance Analyzer. The chapter contains the following topics: Using DBMS_SQLPA Overview Security Model S
Analyze 命令的使用方法
wenbingcai的专栏
10-16 2874
1、三大功能搜集和删除索引、表和簇的统计信息验证表、索引和簇的结构鉴定表和簇和行迁移和行联接针对analyze的搜集和删除统计信息功能而言，oracle推荐使用DBMS_STATS包来搜集优化信息，DBMS_STATS可以并行的搜集信息，可以搜集分区表的全局信息，进一步来说，按成本的优化器只会使用DBMS_STATS包所统计出来的信息。2、先决条件必须在你自己的方案中或有ANALYZE ANY s
Oracle的学习心得和知识总结（十一）|Oracle数据库Real Application Testing之DBMS_SQLPA包技术详解
孤傲小二~阿沐的博客
03-23 5022
Oracle数据库Real Application Testing之DBMS_SQLPA包技术详解
postgresql数据库中analyze统计信息详解
最新发布
weixin_73350116的博客
08-12 1237
关于analyze在使用详解。
【SQL引擎 - analyze.cpp分析(二)】
m0_63216173的博客
08-24 425
open
oracle 函数
rgb_rgb的博客
07-20 1804
/* SQL Analyze(776,1) */ SELECT /*+ full(t) no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ TO_CHAR (COUNT ("SCOREMU
Oracle分析表和索引(analyze)
热门推荐
junmail的专栏
12-19 1万+
analyze table tablename compute statistics;analyze index indexname compute statistics; 对于使用CBO很有好处，可以使用更可靠的table信息，从而执行计划也可以更准确一些，在10g会自动analyze，之前的版本需要手动定期 生成统计信息,,选择合理的执行计划..Oracle的onl
analyze和dbms_stats相互作用和准确度
逝鸿的博客
03-29 347
analyze和dbms_stats可以删除对方手机的统计信息吗？验证可以。SQL&gt; analyze table WORKER compute statistics for table; Table analyzed. SQL&gt; select table_name, blocks, empty_blocks, num_rows 2 from dba_tables 3 w...
windows performance analyzer
03-16
Windows性能分析器是一种用于分析Windows操作系统性能的工具。它可以帮助用户识别和解决系统性能问题，包括CPU、内存、磁盘和网络等方面的问题。用户可以使用Windows性能分析器来收集和分析系统性能数据，以便更好地...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值