1、高消耗SQL之AWR報告導(dǎo)出與分析B S S測試部鄒家勇（CX0069）目錄 TOC o 1-5 h z HYPERLINK l bookmark0 o Current Document 高消耗SQL之AWR報告導(dǎo)出與分析1前言3 HYPERLINK l bookmark4 o Current Document AWR報告導(dǎo)出 3 HYPERLINK l bookmark6 o Current Document 獲取高消耗SQL語句 3 HYPERLINK l bookmark8 o Current Document SQL語句分析方法： 4查看SQL執(zhí)行時間： 4查看SQL執(zhí)行計劃 5 HY

2、PERLINK l bookmark10 o Current Document 查看 trace 信息（最詳細(xì)） 7 HYPERLINK l bookmark12 o Current Document SQL語句編寫注意事項 9 HYPERLINK l bookmark14 o Current Document 性能優(yōu)化9刖百由于現(xiàn)網(wǎng)BOS S及NGBOS S均出現(xiàn)高消耗SQL現(xiàn)象，導(dǎo)致服務(wù)器性能下降， 甚至超時，無法受理業(yè)務(wù)現(xiàn)象，為了防止這種問題再出現(xiàn)在生產(chǎn)環(huán)境。我們可以定時（每天/每兩天）抓取內(nèi)部測試環(huán)境或U A T測試環(huán)境的高消耗SQL,進行分析、優(yōu)化，使高消耗SQL不再上生產(chǎn)環(huán)境， 下

3、面是高消耗S QL的獲取方法，實用于所有基于OR AC L E _10G及以上數(shù)據(jù)庫版本的系統(tǒng)。AWR報告導(dǎo)由AWR由ORACL由動產(chǎn)生的（oracle_10及以上版本），是oracle性能分析的利器，很好 很強大，默認(rèn)1小時采集一次（采集時間可修改，采集時間不宜太小，對ORACLE本身 有性能影響），保留7天的記錄。但是也可以通過DBMS_WORKLOAD_REPOSITORY來手工創(chuàng)建、刪除和修改。使用腳本awrrpt.sql或awrrpti.sql來導(dǎo)出AWR報告，報告格式保存為文 本文件或HTML文件。生成AWR報告的步驟如下：前提：登錄要有 oracle_dba權(quán)限cd $ORACL

4、E_HOME/RDBMS/ADMINexport ORACLE_SID興導(dǎo)出報告的數(shù)據(jù)庫實例名稱如testdbsqlplus 用戶名 /密碼 testdbSQLawrrpt.sql （注：調(diào)用 awrrpt.sql 文件）輸入report_type的值:html （注：確定報告的格式）輸入num_days的值:1 （注：選擇快照的天數(shù)）輸入begin_snap的值:425 （注：起始快照）輸入end_snap的值:437 （注：結(jié)束快照）輸入report_name 的值:testdb_20121224_425_427.html（注：報告生成的名稱和位置，默認(rèn)放在當(dāng)前路徑下）將報告下載到本地，用

5、瀏覽器打開。獲取高消耗S QL語句AWR報告中有很多性能分析數(shù)據(jù)，是D BA常用的性能工具之一，我們主要是獲取報告中的S QL語句。打開我們剛才生成的 “ testdb_20121224_425_427.html ”，找至U SQL Statistic 這一節(jié)。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導(dǎo)tmrJait Fee ompl可吧 Li&t o) 0L Text列表SQL ordered by Elapsed Time說明SQL語句執(zhí)行用總時長，此排序就是按照這個字段進行的。注意該時間不是單個SQL跑的時間，而是監(jiān)控范圍內(nèi)SQL執(zhí)行次數(shù)的總和時間。 單位時間為秒。日apsed Time = CPUTime + Wait TimeSQL ordered by

7、CPU Time為SQL語句執(zhí)行時CPU占用時間總時長， 此時間會小于等丁 Elapsed Time 時間。單位時 間為秒SQL ordered by User I/OWait Time為I O等待最長的S QL語句 topSQL ordered by Gets記錄了執(zhí)行占總 buffer gets（ 邏輯IO ）的TOP SQLSQL ordered by Reads記錄了執(zhí)行占總磁盤物理讀（物理IO ）的TOPSQLSQL ordered by Physical Reads (UnOptimized)記錄了執(zhí)行占總磁盤物理讀（物理IO ）的TOPSQL（系統(tǒng)優(yōu)化統(tǒng)計，11 G新功能）SQL

8、 ordered by ExecutionsSQL語句在監(jiān)控范圍內(nèi)的執(zhí)行次數(shù)總計（哪些S QL執(zhí)行的次數(shù)最多，如果是生產(chǎn)環(huán)境的話代表 業(yè)務(wù)較頻繁）。SQL ordered by Parse CallsSQL的軟解析次數(shù) 的TOP SQLSQL ordered by Sharable Memory錄了 SQL 占用 library cache 的大小 的 TOPSQLSQL ordered by Version Count記錄了 SQL的打開子游標(biāo) 的TOP SQLSQL ordered by Cluster Wait Time記錄了集群的等待時間的 SQLComplete List of SQ

9、L Textsql語句列表S QL語句分析方法:1.查看SQL執(zhí)行時間: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)/沒有從磁盤讀取數(shù)據(jù)字典（?i有足夠的空間來保存row記錄時，就會產(chǎn)生）/當(dāng)前請求的塊數(shù)目1780 consistent gets/數(shù)據(jù)請求總數(shù)在回滾段 Buffer中的數(shù)據(jù)一致性讀所需要的數(shù)據(jù)塊（邏輯讀）0 physical reads/ 物理讀712 redo size/重做日志大小-執(zhí)彳f SQL的過程中，產(chǎn)生的重做日志的大?。ú迦霐?shù)據(jù)引起）383 bytes sent via SQL*Net to client / 通過 sql*net 發(fā)送給客戶端的字節(jié)數(shù)1140 bytes received via SQL*Net from client/ 通過 sql*net 接受客戶端的字節(jié)數(shù)3 SQL*Net roundtrips to/from client2 sorts （memory）/ 內(nèi)存排序0 sorts （disk）/硬排序（磁盤排序）4 rows processed/結(jié)果的記錄數(shù)SQL語句編寫注意事項摘