BSS测试部
邹家勇（CX0069）
目录
高消耗SQL之 AWF报告导出与分析 ......................................................... 1 前言 .................................................................................. 3 AWR报告导出 ......................................................................... 3 获取高消耗SQL语句 ................................................................... 3 SQL语句分析方法： .................................................................... 4
1. 2. 查看SQL执行时间： ........................................................... 4 查看SQL执行计划 ............................................................. 5 3.
查看trace 信息（最详细） ..................................................... SQL语句编写注意事项 .................................................................. 性能优化 .............................................................................. 7
9
9
刖言
由于现网BOSS及NGEOSS均出现高消耗SQL现象，
导致服务器性能下降， 甚
（每天/
至超时，无法受理业务现象，为了防止这种问题再出现在生产环境。我们可以定时
每两天）抓取内部测试环境或UAT测试环境的高消耗SQL，进行分析、优化，使高消耗 SQL不再上生产环境，下面是高消耗SQL的获取方法，实用于所有基于ORACLE _10G及以上数据库版本的系统。
AWR报告导出
AWF由ORACLE自动产生的（oracle_10及以上版本），是oracle性能分析的利器，很好 很强大，默认1
小时采集一次（采集时间可修改，采集时间不宜太小，对ORACLE本身 有性能影响），保留7天的记录。但是也可以通过 DBMS_WORKLOAD_REPOSIT包来手工创建、 删除和修改。使用脚本 awrrpt.sql 或awrrpti.sql 来导出AWF报告，报告格式保存为文本 文件或 HTML文件。 生成AWF报告的步骤如下： 前提：登录要有 oracle_dba权限
1. 2. 3. 4. 5. 6. 7. 8. 9.
cd $ORACLE_HOME/RDBMS/ADMIN
export ORACLE_SID='要导出报告的数据库实例名称'如testdb sqlplus 用户名 / 密码 @testdb SQL>@awrrpt.sql
（注：调用 awrrpt.sql 文件）
（注：确定报告的格式）
（注：选择快照的天数）
输入report_type 的值:html 输入num_days的值:1
输入begin_snap的值:425 （注：起始快照） 输入end_snap的值:437 （注：结束快照） 输入report_name 的值:testdb_20121224_425_427.html 将报告下载到本地，用浏览器打开。
（注：报告生成的名称和位置 ，
默认放在当前路径下）
10.
获取高消耗SQL语句
AWR报告中有很多性能分析数据， 告中的SQL语句。
打开我们刚才生成的“ testdb_20121224_425_427.html ”，找到“ SQLStatistics
”这一节。
是DBA常用的性能工具之一，
我们主要是获取报
SQL Statistics
t)¥ El日p吿已d TimE
SQL ardewd by CPU Time
5QIL(j「dEr~E(i b¥ User 丨：0 Tpait TIRIE SQL ordEr塔d bp gs SQL ordered by R亡目ds
SOIL o「dE「E(i by PhYEiual (UnODtimiziEdl SQL ondergd b* Execution昂 SQL order©计 b# Cmll$
EQIL 0「血「曲 fcw ShE「日blE Mlemurv SQL ordered b¥ VersiM Count
5QIL o「du「E(i b# Clustu〔VM TiEE Comol它tE List of SQL T它Kt
列表 说明 SQL语句执行用总时长，此排序就是按照这个字 段进SQL ordered by Elapsed Time 行的。注意该时间不是单个 SQL跑的时间， 而是监控范围内SQL执行次数的总和时间。 单位 时间为秒。Elapsed Time = CPUTime + Wait Time 为SQL语句执行时CPU占用时间总时长， 此时间会小SQL ordered by CPU Time SQL ordered by User I/O Wait Time SQL ordered by Gets SQL ordered by Reads 于等于 Elapsed Time 时间。单位时 间为秒 为IO等待最长的SQL语句 记录了执行占总 buffer gets（ SQL top 逻辑IO ）的TOP 记录了执行占总磁盘物理读（物理IO ）的TOP SQL 记录了执行占总磁盘物理读（物理IO ）的TOP SQL ordered by Physical Reads (UnO ptimized) SQL （系统优化统计，11G新功能） SQL语句在监控范围内的执行次数总计（哪些S QL执SQL ordered by Executi ons 行的次数最多，如果是生产环境的话代表 业务较频繁）。 SQL的软解析次数 的TOP SQL 录了 SQL 占用 library cache 的大小 的 TOPSQL 记录了 SQL的打开子游标 的TOP SQL 记录了集群的等待时间的 SQL sql语句列表 SQL ordered by Parse Calls SQL ordered by Sharable Memory SQL ordered by Versio n Count SQL ordered by Cluster Wait Time Complete List of SQL Text SQL语句分析方法:
1.查看SQL执行时间:
Set timing on SQL语句
例：
SQL> set tim ing on
SQL> In sert Into Hscdiff.Hc_Subs_Product_Fs_Ch_d
(Subsid,
Regi on, Subsprodid, Prodid, Packageid, Packageprodid,
Tariffitemid, Stan dardtariff, Price, Startdate,
En ddate, Status, Chan gedate, Seqnum, Groupid) Select
Subsid,
Regi on,
Subsprodid, Prodid, Packageid,
Packageprodid, Tariffitemid, Stan dardtariff, Price, Startdate,
En ddate,
Groupid
From Fshsc.Hsc_Subs_Product2002 Where Tariffitemid Is Not Null And
Tariffitemid In
(Select Tariffitemid
From Fshsc.Hsc_Ps_Rati ngrelati on a
Where
a.Tariffitemcode
In (Select
Status,
Chan gedate,
Seqnum,
Disccode From Fshsc.Hsc_Ps_Rati ngdisccode Where Len gth(Disccode) = 4)) And Rownum < 5;
4 rows created.
Elapsed: 00:00:01.04
上面这个sql语句执行时间为1.04秒。(实现同样的功能 然这语句消耗不算太高，想想哦 )
2.查看SQL执行计划 explain plan for SQL语句
select * from TABLE(dbms_xpla n.display()); //
，上面的语句可以优化，当
查询执行结果。
例：
SQL>expla in pla n for
In sert Into Hscdiff.Hc_Subs_Product_Fs_Ch_d
(Subsid, Regi on, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Stan dardtariff, Price, Startdate,
En ddate, Status, Chan gedate, Seqnum, Groupid)
Select Subsid, Regi on, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid, Stan dardtariff, Price, Startdate,
En ddate, Status, Chan gedate, Seqnum, Groupid
From Fshsc.Hsc_Subs_Product2002 Where Tariffitemid Is Not Null And
Tariffitemid In (Select Tariffitemid
From Fshsc.Hsc_Ps_Rati ngrelati on a
Where a.Tariffitemcode In (Select Disccode From
Fshsc.Hsc_Ps_Rat in gdisccode Where Len gth(Disccode) = 4)) And Rownum < 5;
Expla in ed.
SQL>select * from TABLE(dbms_xpla n.display())
PLAN_TABLE_OUTPUT
Plan hash value: 1406724898
1 Id | Operati on |
|
Name | Rows
Bytes | Cost (%CPU)| Time
| 0 | INSERT STATEMENT | 452 1 1 1 1 1* 21 1 1
| 5632 (1)| 00:01:08 | | LOAD TABLECONVENTIONAL
1 1
|
I 4
| HC_SUBS_PRODUCT_FS_CH_D
I I
|
I
I
COUNT STOPKEY 丨
1
丨 丨 丨 1 3丨 NESTED LOOPS
1 1 4 | 395K|
1
1 1
I
|
1 NESTED LOOPS
I
|
I
42M| 5632 (1)| 00:01:08 |
I 5
丨
VIEW
92 (3)| 00:00:02 |
| VW_NSO_1 I 27
| 486 |
PLAN_TABLE_OUTPUT
I 6 I I 1107 I I 7 I I 1107 I I 8 I
SORT UNIQUE
I I
I
I 27
NESTED LOOPS 92 (3)I 00:00:02 I
INDEX
I
I 27
FASTFULL SCAN I
PK_HSC_PS_RATINGRELATION I 35464 I
1246KI 90 (0)I 00:00:02 I
|* 9 | INDEX UNIQUESCAN | PK_HSC_PS_RATINGDISCCODE | 5 |
0 (0)| 00:00:01 |
IDX_SUBSPRODUCT_TARIITEMID2002 | 15377 |
1 |
|* 10 | INDEX RANGE SCAN | |
65 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| HSC_SUBS_PRODUCT2002 | 14662
| 1360K| 1415 (1)| 00:00:17 |
Predicate In formatio n (ide ntified by operati on id):
PLAN_TABLE_OUTPUT
2 - filter(ROWNUM<5)
9 - access(\"A\".\"TARIFFITEMCODE\"=\"DISCCODE\") filter(LENGTH(\"DISCCODE\")=4) 10 - access(\"TARIFFITEMID\"=\"TARIFFITEMID\") filter(\"TARIFFITEMID\" IS NOT NULL)
27 rows selected.
通过上面的执行执行计划， sql语句中有三个步骤(红色)是属于高消耗的。
3.查看trace信息(最详细)
有每个执行步骤的性能值统计 方法：
Set autotrace traceo nly Sql语句
(只分析，不执行)
例：
SQL> set autotrace trace only
SQL> In sert Into Hscdiff.Hc_Subs_Product_Fs_Ch_d
(Subsid,
Region, Subsprodid, Prodid, Packageid, Packageprodid, Tariffitemid,
Stan dardtariff, Price, Startdate,
En ddate, Status, Chan gedate, Seqnum, Groupid) Select
Subsid,
Regi on, Subsprodid, Prodid, Packageid, Packageprodid,
Tariffitemid, Stan dardtariff, Price, Startdate,
En ddate, Status, Chan gedate, Seqnum, Groupid
From Fshsc.Hsc_Subs_Product2002 Where Tariffitemid Is Not Null And
Tariffitemid In (Select Tariffitemid From Fshsc.Hsc_Ps_Rati ngrelati on a
Where a.Tariffitemcode In (Select Disccode From
Fshsc.Hsc_Ps_Rat in gdisccode Where Len gth(Disccode) = 4)) And Rownum < 5; Executi on Pla n
Plan hash value: 1406724898
| Id | Operation
|
Name Bytes | Cost (%CPU)| Time |
| 0 | INSERT STATEMENT | | 4
| 452 | 5632 (1)| 00:01:08 | | 1 | LOAD TABLECONVENTIONAL | HC_SUBS_PRODUCT_FS_CH_D
1 1 I
|
| |
|* 2 | COUNT STOPKEY |
|
1 1
1 | 1 | | I | 3 | NESTED LOOPS |
|
1 1
1 | 1 | |
I
| 4 | NESTED LOOPS
|
|
395K| 42M| 5632 (1)| 00:01:08 | | 5 |
VIEW
| VW_NSO_1
| 27
| 486 | 92 (3)| 00:00:02 | | 6 | SORT UNIQUE |
| 27
| 1107 | | | | 7 | NESTED LOOPS |
1 27
| 1107 | 92 (3)| 00:00:02 |
1 8 | INDEX FASTFULL SCAN |
PK_ HSC_PS_RATINGRELATION
1246K| 90 (0)| 00:00:02 | 1* 9 |
INDEX
UNIQUESCAN |
PK_ HSC_PS_RATINGDISCCODE
5 |
0 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN
|
IDX _SUBSPRODUCT_TARIITEMID2002 | 15377
| 65 (0)| 00:00:01 |
1 11 1 TABLE ACCESS BY
INDEX ROWID| HSC_SUBS_PRODUCT2002
1360K| 1415 (1)| 00:00:17 |
Predicate In formatio n (ide ntified by operati on id):
Rows |
35464 |
1 1 1
|
|14662 |
2 - filter(R0WNUM<5)
9 - access(\"A\".\"TARIFFITEMCODE\"=\"DISCCODE\") filter(LENGTH(\"DISCC0DE\")=4) 10 - access(\"TARIFFITEMID\"=\"TARIFFITEMID\") filter(\"TARIFFITEMID\" IS NOT NULL)
Statistics
0 recursive calls
//
没有从磁盘读取数据字典（没有足够的空间来保存
//当前请求的块数目
row记录时，就会产生）
3 db block gets
1780 consistent gets
//数据请求总数在回滚段 Buffer中的数据一致性读所需要的数据块（逻辑读）
0 physical reads
//物理读
//重做日志大小---执行SQL的过程中，产生的重做日志的大小
（插入数据引起）
712 redo size
383 bytes sent via SQL*Net to client // 1140 bytes received via SQL*Net from client//
3
SQL*Net roundtrips to/from client
通过sql*net发送给客户端的字节数
通过 sql*net 接受客户端的字节数
2 sorts (memory) 0 sorts (disk) 4
rows processed
// 内存排序
//硬排序(磁盘排序)
// 结果的记录数
SQL语句编写注意事项
摘自网络ITPUB
sql语句编写注意事
项.docx
性能优化
性能优化纲领：
首当其冲的是数据库，特别是全表扫描，物理读，硬排序，数据库参数调整, 其次是中间件部署及参数调节，
再次是程序代码流程，关键业务，逻辑架构， 最后调整是硬件。 性能测试优化方法见下图:
fit立正•的主曲.叶催口血引
^A»JI - ii耳分龍.业务咏is分离 甘国 顫刊（将丈捋■不幫用跑厲到佛 捷鬻童迪） 册隔离si別:里些记was—宦辻矍威不圖.可tJiff注阳分一現性•障硕瞬連 保证•勢歯五以从创下世星的悝机制. 雄串耶湘芙子■诲； ■丸樣厚堕料尽的催少的石樺虔s 酢略tRfll . »XltRffi?fl.iTW4fiSJBiffffifl6B1 压中 尽圮在wher昭當\"ite糸件.皈少年緒16回舲方 ^M^niRHE的于（少不毬養週^的剧 &.骨页存桔民堰 尢列直由賣脂也刃収剽用骨页存聲眩扳世到优阪«L 7,屯币奸（5哎帀.删曲，细时事弔你程口flttt届tft.ff对刊硕1即嘔15退行住!!的1K比 a.通右篇理.可以冗金去単字股.，龟尿音•* 虫血西關找，也可枫H内的XTJflSBiiJTHS中， 10.暑做岸務关哉刊・尽量EI注不村軽样喪中故豆s豪少苗卡碍计■盘 11.覽乐事事如跡性鼻娜*.冬■$甘田t计H 它St动｛■査闵 忧肋临.击露台£黑対曲曲注.昨革诳的邊旧.签耳.軒启■畴dft色醫计M 皿甲谓逗疥的不E整眄内好丹科 鼻抵内序建■ （ uiirig P Diighatt r WeakUHeraiCC , Findl険 遽网贱礬咔的夬宴悄.犬对章.丸JSifl环粳FS冗爭启.艮时flF血 便用議弱钛自的土文U丈时■ ■
号睾黃啰程植术
医腥區目的遏屯方式-融窿，和止莖
!«P磁与咨沖.盡劳的交互宜霰.餓W 工陆？I珅. HKft^wd.SErempnnglf交互 ■ffintt :!■时馳.-年SB
变此的.■有璧比.
轴斧计Hi.
理jtif退肝程.車若遥航山轨希秤方式
号SB韻內:対恋生拓常無Jfl修画耳UOI®扶.裁閒高科内坯横块.骨离耳托畫flfli徒
ift^htlpilJlt 邀空如
曲询I口甸柚諮妆环彊存忻整
Gip压鹫円刃：
专底甘布逆用.分布式存聲’収上所右仍擋梢革進国
afc>L4KikieW 便m柯砺和曲
便冃\"五 &pritR!E|f/fL
可◎便用晦冃31裁.融龟任用珀态同页
0,便用■性厲的少型砂处荐凶遣話
兀關存
力旳戛存卩潮 诚曜囲制
臨宵优优注进i旧鞫理贏面]
3-関曲离业刑|場•护it野“柱削|一林甜JIM 4, ^-WtbSiErvcr 5, W«bftRi3ft-F5 6. SOEit酚駡
叫便用谒®服列週抒客冲阿慌瞬^计冃
& W•阳布式,JS用甘布式..仙分布式
轧忖布武的荷烏fl!脑性加島||，扌里机卿
10.證升毗逼务謝可硒!8帶凉
菇牌伸1翳.cpu丄円存,yo
RU ：HSH®・WtfidDwsf]^ ■系険本身日苫
債JflrtomawM方n执咛时|虬JfctlMWIl的万还.t+WttOtW
5qlpMil曲耳幻尿时世凤.般对IS优化
看社p典込阖怎直彌时a師鮎加宜魁屉.
Perfimiancc Caunl .便用计證話.城计用盖性辟标
匸LRPehl■呦弔检覇工H
LaadRunner.圧力31武.境现H縄缺
因篇幅问题不能全部显示，请点此查看更多更全内容