高消耗SQL之AWR报告导出与分析.docx
《高消耗SQL之AWR报告导出与分析.docx》由会员分享，可在线阅读，更多相关《高消耗SQL之AWR报告导出与分析.docx（9页珍藏版）》请在冰豆网上搜索。
高消耗SQL之AWR报告导出与分析
高消耗SQL之AWR报告导出与分析
ＢＳＳ测试部
邹家勇（cx0069）
前言
由于现网ＢＯＳＳ及ＮＧＢＯＳＳ均出现高消耗ＳＱＬ现象，导致服务器性能下降，甚至超时，无法受理业务现象，为了防止这种问题再出现在生产环境。
我们可以定时（每天/每两天）抓取内部测试环境或ＵＡＴ测试环境的高消耗ＳＱＬ，进行分析、优化，使高消耗ＳＱＬ不再上生产环境，下面是高消耗ＳＱＬ的获取方法，实用于所有基于ＯＲＡＣＬＥ_10G及以上数据库版本的系统。
ＡＷＲ报告导出
AWR由ORACLE自动产生的（oracle_10及以上版本），是oracle性能分析的利器，很好很强大，默认1小时采集一次（采集时间可修改，采集时间不宜太小，对ＯＲＡＣＬＥ本身有性能影响），保留７天的记录。
但是也可以通过DBMS_WORKLOAD_REPOSITORY包来手工创建、删除和修改。
使用脚本awrrpt.sql或awrrpti.sql来导出AWR报告，报告格式保存为文本文件或HTML文件。
生成AWR报告的步骤如下：
前提：
登录要有oracle_dba权限
1.cd$ORACLE_HOME/RDBMS/ADMIN
2.exportORACLE_SID='要导出报告的数据库实例名称'如testdb
3.sqlplus用户名/密码@testdb
4.SQL>@awrrpt.sql （注：
调用awrrpt.sql文件）
5.输入report_type的值:
html（注：
确定报告的格式）
6.输入num_days的值:
1（注：
选择快照的天数）
7.输入begin_snap的值:
425（注：
起始快照）
8.输入end_snap的值:
437（注：
结束快照）
9.输入report_name的值:
testdb_20121224_425_427.html（注：
报告生成的名称和位置,默认放在当前路径下）
10.将报告下载到本地，用浏览器打开。
获取高消耗ＳＱＬ语句
ＡＷＲ报告中有很多性能分析数据，是ＤＢＡ常用的性能工具之一，我们主要是获取报告中的ＳＱＬ语句。
打开我们刚才生成的“testdb_20121224_425_427.html”，找到“SQLStatistics”这一节。
列表
说明
SQLorderedbyElapsedTime
SQL语句执行用总时长，此排序就是按照这个字段进行的。
注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。
单位时间为秒。
ElapsedTime=CPUTime+WaitTime
SQLorderedbyCPUTime
为SQL语句执行时CPU占用时间总时长，
此时间会小于等于ElapsedTime时间。
单位时间为秒
SQLorderedbyUserI/O
WaitTime
为ＩＯ等待最长的ＳＱＬ语句top
SQLorderedbyGets
记录了执行占总buffergets（逻辑IO）的TOPSQL
SQLorderedbyReads
记录了执行占总磁盘物理读（物理IO）的TOPSQL
SQLorderedbyPhysicalReads
（UnOptimized）
记录了执行占总磁盘物理读（物理IO）的TOPSQL
（系统优化统计，１１Ｇ新功能）
SQLorderedbyExecutions
SQL语句在监控范围内的执行次数总计（哪些ＳＱＬ执行的次数最多，如果是生产环境的话代表业务较频繁）。
SQLorderedbyParseCalls
SQL的软解析次数的TOPSQL
SQLorderedbySharableMemory
录了SQL占用librarycache的大小的TOPSQL
SQLorderedbyVersionCount
记录了SQL的打开子游标的TOPSQL
SQLorderedbyClusterWaitTime
记录了集群的等待时间的SQL
CompleteListofSQLText
sql语句列表
ＳＱＬ语句分析方法：
1.查看SQL执行时间：
Settimingon
SQL语句
例：
SQL>settimingon
SQL>InsertIntoHscdiff.Hc_Subs_Product_Fs_Ch_d
（Subsid,Region,Subsprodid,Prodid,Packageid,Packageprodid,Tariffitemid,Standardtariff,Price,Startdate,
Enddate,Status,Changedate,Seqnum,Groupid）
SelectSubsid,Region,Subsprodid,Prodid,Packageid,Packageprodid,Tariffitemid,Standardtariff,Price,Startdate,
Enddate,Status,Changedate,Seqnum,Groupid
FromFshsc.Hsc_Subs_Product2002
WhereTariffitemidIsNotNullAnd
TariffitemidIn
（SelectTariffitemid
FromFshsc.Hsc_Ps_Ratingrelationa
Wherea.TariffitemcodeIn（SelectDisccodeFromFshsc.Hsc_Ps_RatingdisccodeWhereLength（Disccode）=4））And
Rownum<5;
4rowscreated.
Elapsed:
00:
00:
01.04
上面这个sql语句执行时间为1.04秒。
（实现同样的功能，上面的语句可以优化，当然这语句消耗不算太高，想想哦）
2.查看SQL执行计划
explainplanfor
SQL语句
select*fromTABLE（dbms_xplan.display（））;//查询执行结果。
例：
SQL>explainplanfor
InsertIntoHscdiff.Hc_Subs_Product_Fs_Ch_d
（Subsid,Region,Subsprodid,Prodid,Packageid,Packageprodid,Tariffitemid,Standardtariff,Price,Startdate,
Enddate,Status,Changedate,Seqnum,Groupid）
SelectSubsid,Region,Subsprodid,Prodid,Packageid,Packageprodid,Tariffitemid,Standardtariff,Price,Startdate,
Enddate,Status,Changedate,Seqnum,Groupid
FromFshsc.Hsc_Subs_Product2002
WhereTariffitemidIsNotNullAnd
TariffitemidIn
（SelectTariffitemid
FromFshsc.Hsc_Ps_Ratingrelationa
Wherea.TariffitemcodeIn（SelectDisccodeFromFshsc.Hsc_Ps_RatingdisccodeWhereLength（Disccode）=4））And
Rownum<5;
Explained.
―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――
SQL>select*fromTABLE（dbms_xplan.display（））
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
Planhashvalue:
1406724898
-----------------------------------------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|
-----------------------------------------------------------------------------------------------------------------
|0|INSERTSTATEMENT||4|452|5632
（1）|00:
01:
08|
|1|LOADTABLECONVENTIONAL|HC_SUBS_PRODUCT_FS_CH_D|||||
|*2|COUNTSTOPKEY||||||
|3|NESTEDLOOPS||||||
|4|NESTEDLOOPS||395K|42M|5632
（1）|00:
01:
08|
|5|VIEW|VW_NSO_1|27|486|92（3）|00:
00:
02|
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
|6|SORTUNIQUE||27|1107|||
|7|NESTEDLOOPS||27|1107|92（3）|00:
00:
02|
|8|INDEXFASTFULLSCAN|PK_HSC_PS_RATINGRELATION|35464|1246K|90（0）|00:
00:
02|
|*9|INDEXUNIQUESCAN|PK_HSC_PS_RATINGDISCCODE|1|5|0（0）|00:
00:
01|
|*10|INDEXRANGESCAN|IDX_SUBSPRODUCT_TARIITEMID2002|15377||65（0）|00:
00:
01|
|11|TABLEACCESSBYINDEXROWID|HSC_SUBS_PRODUCT2002|14662|1360K|1415
（1）|00:
00:
17|
-----------------------------------------------------------------------------
升级会员 