高消耗SQL之AWR报告导出与分析

1、高消耗SQL之AWR报告导出与分析B S S测试部邹家勇（CX0069）目录 TOC o 1-5 h z HYPERLINK l bookmark0 o Current Document 高消耗SQL之AWR报告导出与分析1前言3 HYPERLINK l bookmark4 o Current Document AWR报告导出 3 HYPERLINK l bookmark6 o Current Document 获取高消耗SQL语句 3 HYPERLINK l bookmark8 o Current Document SQL语句分析方法： 4查看SQL执行时间： 4查看SQL执行计划 5 HY

2、PERLINK l bookmark10 o Current Document 查看 trace 信息（最详细） 7 HYPERLINK l bookmark12 o Current Document SQL语句编写注意事项 9 HYPERLINK l bookmark14 o Current Document 性能优化9刖百由于现网BOS S及NGBOS S均出现高消耗SQL现象，导致服务器性能下降， 甚至超时，无法受理业务现象，为了防止这种问题再出现在生产环境。我们可以定时（每天/每两天）抓取内部测试环境或U A T测试环境的高消耗SQL,进行分析、优化，使高消耗SQL不再上生产环境， 下

3、面是高消耗S QL的获取方法，实用于所有基于OR AC L E _10G及以上数据库版本的系统。AWR报告导由AWR由ORACL由动产生的（oracle_10及以上版本），是oracle性能分析的利器，很好 很强大，默认1小时采集一次（采集时间可修改，采集时间不宜太小，对ORACLE本身 有性能影响），保留7天的记录。但是也可以通过DBMS_WORKLOAD_REPOSITORY来手工创建、删除和修改。使用脚本awrrpt.sql或awrrpti.sql来导出AWR报告，报告格式保存为文 本文件或HTML文件。生成AWR报告的步骤如下：前提：登录要有 oracle_dba权限cd $ORACL

4、E_HOME/RDBMS/ADMINexport ORACLE_SID兴导出报告的数据库实例名称如testdbsqlplus 用户名 /密码 testdbSQLawrrpt.sql （注：调用 awrrpt.sql 文件）输入report_type的值:html （注：确定报告的格式）输入num_days的值:1 （注：选择快照的天数）输入begin_snap的值:425 （注：起始快照）输入end_snap的值:437 （注：结束快照）输入report_name 的值:testdb_20121224_425_427.html（注：报告生成的名称和位置，默认放在当前路径下）将报告下载到本地，用

5、浏览器打开。获取高消耗S QL语句AWR报告中有很多性能分析数据，是D BA常用的性能工具之一，我们主要是获取报告中的S QL语句。打开我们刚才生成的 “ testdb_20121224_425_427.html ”，找至U SQL Statistic 这一节。SQL StatisticsS-L ordered b】, Elm目vd TirriH5-L、rdEcd bfCPU Tim/EQL odf匚Gd mu$ul Q W日it TirneBUL 0d&red by G，T5$!. ordEFed M Rad普5-1 口rdBcd bY Phusicall R巴哥ds fUnODtimkec

6、t)!- orcIsecI Ijv EjtecuHun园EQLoEered W Par占匕 口ills沟L deed by $haable PJwmarYEQL /rdcred by Count4 ! 口dEfEd by Clu导tmrJait Fee ompl可吧 Li&t o) 0L Text列表SQL ordered by Elapsed Time说明SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。 单位时间为秒。日apsed Time = CPUTime + Wait TimeSQL ordered by

7、CPU Time为SQL语句执行时CPU占用时间总时长， 此时间会小于等丁 Elapsed Time 时间。单位时 间为秒SQL ordered by User I/OWait Time为I O等待最长的S QL语句 topSQL ordered by Gets记录了执行占总 buffer gets（ 逻辑IO ）的TOP SQLSQL ordered by Reads记录了执行占总磁盘物理读（物理IO ）的TOPSQLSQL ordered by Physical Reads (UnOptimized)记录了执行占总磁盘物理读（物理IO ）的TOPSQL（系统优化统计，11 G新功能）SQL

8、 ordered by ExecutionsSQL语句在监控范围内的执行次数总计（哪些S QL执行的次数最多，如果是生产环境的话代表 业务较频繁）。SQL ordered by Parse CallsSQL的软解析次数 的TOP SQLSQL ordered by Sharable Memory录了 SQL 占用 library cache 的大小 的 TOPSQLSQL ordered by Version Count记录了 SQL的打开子游标 的TOP SQLSQL ordered by Cluster Wait Time记录了集群的等待时间的 SQLComplete List of SQ

9、L Textsql语句列表S QL语句分析方法:1.查看SQL执行时间:Set timing onSQL语句例：SQL set timing onSQL Insert Into Hscdiff.Hc_Subs_Product_Fs_Ch_d(Subsid, Region, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Standardtariff, Price, Startdate,Enddate, Status, Changedate, Seqnum, Groupid)Select Subsid, Region, Su

10、bsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Standardtariff, Price, Startdate,Enddate, Status, Changedate, Seqnum, GroupidFrom Fshsc.Hsc_Subs_Product2002Where Tariffitemid Is Not Null AndTariffitemid In(Select TariffitemidFrom Fshsc.Hsc_Ps_Ratingrelation aWhere a.Tariffitemcode In (Sele

11、ct DisccodeFrom Fshsc.Hsc_Ps_Ratingdisccode Where Length(Disccode) = 4) AndRownum explain plan forInsert Into Hscdiff.Hc_Subs_Product_Fs_Ch_d(Subsid, Region, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Standardtariff, Price, Startdate,Enddate, Status, Changedate, Seqnum, Groupid)Sele

12、ct Subsid, Region, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Standardtariff, Price, Startdate,Enddate, Status, Changedate, Seqnum, GroupidFrom Fshsc.Hsc_Subs_Product2002Where Tariffitemid Is Not Null AndTariffitemid In(Select TariffitemidFrom Fshsc.Hsc_Ps_Ratingrelation aWhere a.Ta

13、riffitemcode In (Select Disccode FromFshsc.Hsc_Ps_Ratingdisccode Where Length(Disccode) = 4) AndRownum select * from TABLE(dbms_xplan.display()PLAN_TABLE_OUTPUTPlan hash value: 1406724898| Id | Operation| Name| Rows |Bytes | Cost (%CPU)| Time |0 | INSERTSTATEMENT|452 | 5632(1)| 00:01:08 |1 | LOAD TA

14、BLE CONVENTIONAL|*2 | COUNT STOPKEY|3 | NESTEDLOOPS|4 |NESTEDLOOPS395K|42M|5632(1)| 00:01:08 |5 | VIEW27 |486 |92(3)| 00:00:02 |4|HC_SUBS_PRODUCT_FS_CH_D|VW_NSO_1|6 | SORTUNIQUE|1107 |7 |NESTEDLOOPS|1107 |92(3)| 00:00:02 |8 |INDEX FASTFULL SCAN1246K|90(0)| 00:00:02 |*9 |INDEX UNIQUE SCAN|5 |0(0)| 00

15、:00:01 |* 10 |INDEX RANGE SCANPLAN_TABLE_OUTPUT TOC o 1-5 h z |27|27| PK_HSC_PS_RATINGRELATION | 35464 | PK_HSC_PS_RATINGDISCCODE |1| IDX_SUBSPRODUCT_TARIITEMID2002 | 15377 |65(0)| 00:00:01 |11 | TABLE ACCESS BY INDEX ROWID| HSC_SUBS_PRODUCT2002 | 14662|1360K|1415(1)| 00:00:17 |Predicate Information

16、 (identified by operation id):PLAN_TABLE_OUTPUT2 - filter(ROWNUM set autotrace traceonlySQL Insert Into Hscdiff.Hc_Subs_Product_Fs_Ch_d(Subsid, Region, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Standardtariff, Price, Startdate,Enddate, Status, Changedate, Seqnum, Groupid)Select Sub

17、sid, Region, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Standardtariff, Price, Startdate,Enddate, Status, Changedate, Seqnum, GroupidFrom Fshsc.Hsc_Subs_Product2002Where Tariffitemid Is Not Null AndTariffitemid In(Select TariffitemidFrom Fshsc.Hsc_Ps_Ratingrelation aWhere a.Tariffit

18、emcode In (Select Disccode FromFshsc.Hsc_Ps_Ratingdisccode Where Length(Disccode) = 4) AndRownum 5;Execution PlanPlan hash value: 1406724898| Rows |0 | INSERTSTATEMENT|452 | 5632(1)| 00:01:08 |1 | LOAD TABLE CONVENTIONAL|*2 | COUNT STOPKEY|3 | NESTEDLOOPS|4 |NESTED LOOPS395K|42M|5632(1)| 00:01:08 |5

19、 |VIEW27 |486 |92(3)| 00:00:02 |6 | SORTUNIQUE|1107 |7 |NESTEDLOOPS|1107 |92(3)| 00:00:02 |8 |INDEX FASTFULL SCAN1246K|90(0)| 00:00:02 |*9 |INDEX UNIQUE SCAN|5 |0(0)| 00:00:01 |* 10 |INDEX RANGE SCAN|65(0)| 00:00:01 |4|HC_SUBS_PRODUCT_FS_CH_D|VW_NSO_1|27|27| PK_HSC_PS_RATINGRELATION | 35464 | PK_HSC

20、_PS_RATINGDISCCODE |1| IDX_SUBSPRODUCT_TARIITEMID2002 | 15377 | Id | Operation| NameBytes | Cost (%CPU)| Time |11 | TABLE ACCESS BY INDEX ROWID| HSC_SUBS_PRODUCT2002 | 14662 |1360K|1415(1)| 00:00:17 |Predicate Information (identified by operation id):2 - filter(ROWNUM5)9 - access(A.TARIFFITEMCODE=DI

21、SCCODE) filter(LENGTH(DISCCODE)=4)Statistics0 recursive calls3 db block gets10 - access(TARIFFITEMID=TARIFFITEMID) filter(TARIFFITEMID IS NOT NULL)/没有从磁盘读取数据字典（?i有足够的空间来保存row记录时，就会产生）/当前请求的块数目1780 consistent gets/数据请求总数在回滚段 Buffer中的数据一致性读所需要的数据块（逻辑读）0 physical reads/ 物理读712 redo size/重做日志大小-执彳f SQL的过程中，产生的重做日志的大小（插入数据引起）383 bytes sent via SQL*Net to client / 通过 sql*net 发送给客户端的字节数1140 bytes received via SQL*Net from client/ 通过 sql*net 接受客户端的字节数3 SQL*Net roundtrips to/from client2 sorts （memory）/ 内存排序0 sorts （disk）/硬排序（磁盘排序）4 rows processed/结果的记录数SQL语句编写注意事项摘