欢迎来到淘文阁 - 分享文档赚钱的网站! | 帮助中心 好文档才是您的得力助手!
淘文阁 - 分享文档赚钱的网站
全部分类
  • 研究报告>
  • 管理文献>
  • 标准材料>
  • 技术资料>
  • 教育专区>
  • 应用文书>
  • 生活休闲>
  • 考试试题>
  • pptx模板>
  • 工商注册>
  • 期刊短文>
  • 图片设计>
  • ImageVerifierCode 换一换

    Oracle AWR 报告分析实例讲解.docx

    • 资源ID:44410442       资源大小:165.55KB        全文页数:45页
    • 资源格式: DOCX        下载积分:15金币
    快捷下载 游客一键下载
    会员登录下载
    微信登录下载
    三方登录下载: 微信开放平台登录   QQ登录  
    二维码
    微信扫一扫登录
    下载资源需要15金币
    邮箱/手机:
    温馨提示:
    快捷下载时,用户名和密码都是您填写的邮箱或者手机号,方便查询和重复下载(系统自动生成)。
    如填写123,账号就是123,密码也是123。
    支付方式: 支付宝    微信支付   
    验证码:   换一换

     
    账号:
    密码:
    验证码:   换一换
      忘记密码?
        
    友情提示
    2、PDF文件下载后,可能会被浏览器默认打开,此种情况可以点击浏览器菜单,保存网页到桌面,就可以正常下载了。
    3、本站不支持迅雷下载,请使用电脑自带的IE浏览器,或者360浏览器、谷歌浏览器下载即可。
    4、本站资源下载后的文档和图纸-无水印,预览文档经过压缩,下载后原文更清晰。
    5、试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓。

    Oracle AWR 报告分析实例讲解.docx

    WORKLOAD REPOSITORY report for DB NameDB IdInstanceInst numReleaseRACHostICCI1314098396ICCI1110.2.0.3.0YESHPGICCI1Snap IdSnap TimeSessionsCursors/SessionBegin Snap:267825-Dec-08 14:04:50241.5End Snap:268025-Dec-08 15:23:37261.5Elapsed: 78.79 (mins)  DB Time: 11.05 (mins)  DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间,说明数据库比较空闲。在79分钟里(其间收集了3次快照数据),数据库耗时11分钟,RDA数据中显示系统有8个逻辑CPU(4个物理CPU),平均每个CPU耗时1.4分钟,CPU利用率只有大约2%(1.4/79)。说明系统压力非常小。可是对于批量系统,数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内,或者快照周期跨度太长而包含了大量的数据库空闲时间,所得出的分析结果是没有意义的。这也说明选择分析时间段很关键,要选择能够代表性能问题的时间段。Report SummaryCache Sizes BeginEndBuffer Cache:3,344M3,344MStd Block Size:8KShared Pool Size:704M704MLog Buffer:14,352K显示SGA中每个区域的大小(在AMM改变它们之后),可用来与初始参数值比较。shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析(或编译)后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。Load ProfilePer SecondPer TransactionRedo size:918,805.72775,912.72Logical reads:3,521.772,974.06Block changes:1,817.951,535.22Physical reads:68.2657.64Physical writes:362.59306.20User calls:326.69275.88Parses:38.6632.65Hard parses:0.030.03Sorts:0.610.51Logons:0.010.01Executes:354.34299.23Transactions:1.18 % Blocks changed per Read:51.62Recursive Call %:51.72Rollback per transaction %:85.49Rows per Sort:#显示数据库负载概况,将之与基线数据比较才具有更多的意义,如果每秒或每事务的负载变化不大,说明应用运行比较稳定。单个的报告数据只说明应用的负载情况,绝大多数据并没有一个所谓“正确”的值,然而Logons大于每秒12个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。Redo size:每秒/每事务产生的redo大小(单位字节),可标志数据库任务的繁重程序。Logical reads:每秒/每事务逻辑读的块数Block changes:每秒/每事务修改的块数Physical reads:每秒/每事务物理读的块数Physical writes:每秒/每事务物理写的块数User calls:每秒/每事务用户call次数Parses:SQL解析的次数Hard parses:其中硬解析的次数,硬解析太多,说明SQL重用率不高。Sorts:每秒/每事务的排序次数Logons:每秒/每事务登录的次数Executes:每秒/每事务SQL执行次数Transactions:每秒事务数Blocks changed per Read:表示逻辑读用于修改数据块的比例Recursive Call:递归调用占所有操作的比率Rollback per transaction:每事务的回滚率Rows per Sort:每次排序的行数注:Oracle的硬解析和软解析 提到软解析(soft parse)和硬解析(hard parse),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:1、语法检查(syntax check)检查此sql的拼写是否语法。2、语义检查(semantic check)诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。3、对sql语句进行解析(parse)利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)。4、执行sql,返回结果(execute and return)其中,软、硬解析就发生在第三个过程里。Oracle利用内部的hash算法来取得该sql的hash值,然后在library cache里查找是否存在该hash值;假设存在,则将此sql与cache中的进行比较;假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。Instance Efficiency Percentages (Target 100%) Buffer Nowait %:100.00Redo NoWait %:100.00Buffer Hit %:98.72In-memory Sort %:99.86Library Hit %:99.97Soft Parse %:99.92Execute to Parse %:89.09Latch Hit %:99.99Parse CPU to Parse Elapsd %:7.99% Non-Parse CPU:99.95本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中Buffer Hit Ratio 也称Cache Hit Ratio,Library Hit ratio也称Library Cache Hit ratio。同Load Profile一节相同,这一节也没有所谓“正确”的值,而只能根据应用的特点判断是否合适。在一个使用直接读执行大型并行查询的DSS环境,20%的Buffer Hit Ratio是可以接受的,而这个值对于一个OLTP系统是完全不能接受的。根据Oracle的经验,对于OLTPT系统,Buffer Hit Ratio理想应该在90%以上。Buffer Nowait表示在内存获得数据的未等待比例。buffer hit表示进程从内存中找到数据块的比率,监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统,如果此值低于80%,应该给数据库分配更多的内存。Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低(可参考90%阀值),考虑增加LOG BUFFER。library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率,当应用程序调用SQL或存储过程时,Oracle检查Library Cache确定是否存在解析过的版本,如果存在,Oracle立即执行语句;如果不存在,Oracle解析此语句,并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析,增加CPU消耗,降低性能。如果library hit ratio低于90%,可能需要调大shared pool区。Latch Hit:Latch是一种保护内存结构的锁,可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%,否则意味着Shared Pool latch争用,可能由于未共享的SQL,或者Library Cache太小,可使用绑定变更或调大Shared Pool解决。Parse CPU to Parse Elapsd:解析实际运行时间/(解析实际运行时间+解析中等待资源时间),越高越好。Non-Parse CPU :SQL实际运行时间/(SQL实际运行时间+SQL解析时间),太低表示解析消耗时间过多。Execute to Parse:是语句执行与分析的比例,如果要SQL重用率高,则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。In-memory Sort:在内存中排序的比率,如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。Soft Parse:软解析的百分比(softs/softs+hards),近似当作sql在共享区的命中率,太低则需要调整应用使用绑定变量。Shared Pool Statistics BeginEndMemory Usage %:47.1947.50% SQL with executions>1:88.4879.81% Memory for SQL w/exec>1:79.9973.52Memory Usage %:对于一个已经运行一段时间的数据库来说,共享池内存使用率,应该稳定在75%-90%间,如果太小,说明Shared Pool有浪费,而如果高于90,说明共享池中有争用,内存不足。SQL with executions>1:执行次数大于1的sql比率,如果此值太小,说明需要在应用中更多使用绑定变量,避免过多SQL解析。Memory for SQL w/exec>1:执行次数大于1的SQL消耗内存的占比。Top 5 Timed Events EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU time 515 77.6 SQL*Net more data from client27,3196429.7Networklog file parallel write5,4974797.1System I/Odb file sequential read7,9003545.3User I/Odb file parallel write4,8063475.1System I/O这是报告概要的最后一节,显示了系统中最严重的5个等待,按所占等待时间的比例倒序列示。当我们调优时,总希望观察到最显著的效果,因此应当从这里入手确定我们下一步做什么。例如如果buffer busy wait是较严重的等待事件,我们应当继续研究报告中Buffer Wait和File/Tablespace IO区的内容,识别哪些文件导致了问题。如果最严重的等待事件是I/O事件,我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O,并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待,就需要察看详细的LATCH统计识别哪些LATCH产生的问题。在这里,log file parallel write是相对比较多的等待,占用了7%的CPU时间。通常,在没有问题的数据库中,CPU time总是列在第一个。更多的等待事件,参见本报告 的Wait Events一节。RAC StatisticsBeginEndNumber of Instances:22Global Cache Load Profile Per SecondPer TransactionGlobal Cache blocks received:4.163.51Global Cache blocks served:5.975.04GCS/GES messages received:408.47344.95GCS/GES messages sent:258.03217.90DBWR Fusion writes:0.050.05Estd Interconnect traffic (KB)211.16 Global Cache Efficiency Percentages (Target local+remote 100%) Buffer access - local cache %:98.60Buffer access - remote cache %:0.12Buffer access - disk %:1.28Global Cache and Enqueue Services - Workload Characteristics Avg global enqueue get time (ms):0.1Avg global cache cr block receive time (ms):1.1Avg global cache current block receive time (ms):0.8Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.0Global cache log flushes for cr blocks served %:3.5Avg global cache cr block flush time (ms):3.9Avg global cache current block pin time (ms):0.0Avg global cache current block send time (ms):0.0Global cache log flushes for current blocks served %:0.4Avg global cache current block flush time (ms):3.0Global Cache and Enqueue Services - Messaging Statistics Avg message sent queue time (ms):0.0Avg message sent queue time on ksxp (ms):0.3Avg message received queue time (ms):0.5Avg GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages:14.40% of indirect sent messages:77.04% of flow controlled messages:8.56Main Report · Wait Events Statistics · SQL Statistics · Instance Activity Statistics · IO Stats · Buffer Pool Statistics · Advisory Statistics · Wait Statistics · Undo Statistics · Latch Statistics · Segment Statistics · Dictionary Cache Statistics · Library Cache Statistics · Memory Statistics · Streams Statistics · Resource Limit Statistics · init.ora Parameters Wait Events Statistics · Time Model Statistics · Wait Class · Wait Events · Background Wait Events · Operating System Statistics · Service Statistics · Service Wait Class Stats Back to TopTime Model Statistics· Total time in database user-calls (DB Time): 663s · Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic · Ordered by % or DB time desc, Statistic name Statistic NameTime (s)% of DB TimeDB CPU514.5077.61sql execute elapsed time482.2772.74parse time elapsed3.760.57PL/SQL execution elapsed time0.500.08hard parse elapsed time0.340.05connection management call elapsed time0.080.01hard parse (sharing criteria) elapsed time0.000.00repeated bind elapsed time0.000.00PL/SQL compilation elapsed time0.000.00failed parse elapsed time0.000.00DB time662.97 background elapsed time185.19 background cpu time67.48 此节显示了各种类型的数据库处理任务所占用的CPU时间。Back to Wait Events Statistics Back to TopWait Class· s - second · cs - centisecond - 100th of a second · ms - millisecond - 1000th of a second · us - microsecond - 1000000th of a second · ordered by wait time desc, waits desc Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txnUser I/O66,8370.00120211.94System I/O28,2950.009335.05Network1,571,4500.00660280.72Cluster210,5480.0029037.61Other81,78371.8228014.61Application333,1550.0016059.51Concurrency5,1820.04510.93Commit9190.00440.16Configuration25,42799.46104.54Back to Wait Events Statistics Back to TopWait Events· s - second · cs - centisecond - 100th of a second · ms - millisecond - 1000th of a second · us - microsecond - 1000000th of a second · ordered by wait time desc, waits desc (idle events last) EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txnSQL*Net more data from client27,3190.006424.88log file parallel write5,4970.004790.98db file sequential read7,9000.003541.41db file parallel write4,8060.003470.86db file scattered read10,3100.003131.84direct path write42,7240.003017.63reliable message3552.8218490.06SQL*Net break/reset to client333,0840.0016059.50db file parallel read3,7320.001340.67gc current multi block request175,7100.0010031.39control file sequential read15,9740.001012.85direct path read temp1,8730.00950.33gc cr multi block request20,8770.00803.73log file sync9190.00440.16gc cr block busy5260.00360.09enq: FB - contention10,3840.00301.85DFS lock handle3,5170.00310.63control file parallel write1,9460.00310.35gc current block 2-way4,1650.00200.74library cache lock4320.00240.08name-service call wait220.002760.00row cache lock3,8940.00200.70gcs log flush sync1,25942.02210.22os thread startup185.562890.00gc cr block 2-way3,6710.00200.66gc current block busy1130.001120.02SQL*Net message to client1,544,1150.0010275.83gc buffer busy156.671700.00gc cr disk read3,2720.00100.58direct path write temp1590.00150.03gc current grant busy8980.00110.16log file switch completion290.001170.01CGS wait for IPC msg48,73999.87008.71gc current grant 2-way1,1420.00000.20kjbdrmcvtq lmon drm quiesce: ping completion90.000190.00enq: US - contention5670.00000.10direct path read1380.00010.02enq: WF - contention140.00090.00ksxr poll remote instances13,29158.45002.37library cache pin2110.00010.04ges global resource directory to be frozen9100.000100.00wait for scn ack5830.00000.10log file sequential read360.00020.01undo segment extension25,34299.79004.53rdbms ipc reply2790.00000.05ktfbtgex6100.000100.00enq: HW - contention440.00010.01gc cr grant 2-way1580.00000.03enq: TX - index contention10.000340.00enq: CF - contention640.00010.01PX Deq: Signal ACK3721.62010.01latch free30.000100.00buffer busy waits6250.16000.11KJC: Wait for msg sends to complete1540.00000.03log buffer space110.00020.00enq: PS - contention460.00010.01enq: TM - contention700.00000.01IPC send completion sync40100.00000.01PX Deq: reap credit1,54499.81000.28log file single write360.00000.01enq: TT - contention460.00000.01enq: TD - KTF dump entries120.00010.00read by other session10.000120.00LGWR wait for redo copy5400.00000.10PX Deq Credit: send blkd175.88000.00enq: TA - contention140.00000.00latch: ges resource hash list440.00000.01enq: PI - contention80.00000.00write complete waits10.00020.00enq: DR - contention30.00000.00enq: MW - contention30.00000.00enq: TS - contention30.00000.00PX qref latch150100.00000.03enq: MD - contention20.00000.00latch: KCL gc element parent latch110.00000.00enq: JS - job run lock - synchronize10.00010.00SQL*Net more data to client160.00000.00latch: cache buffers lru chain10.00000.00enq: UL - contention10.00000.00gc current split10.00000.00enq: AF - task serialization10.00000.00latch: object queue header operation30.00000.00latch: cache buffers chains10.00000.00latch: enqueue hash chains20.00000.00SQL*Net message from client1,544,1130.0012,6268275.83gcs remote message634,88498.649,20314113.41DIAG idle wait23,6280.004,6161954.22ges remote message149,59193.454,6123126.72Streams AQ: qmn slave idle wait1670.004,611276110.03Streams AQ: qmn coordinator idle wait35147.864,611131370.06Streams AQ: waiting for messages in the queue488100.004,60594360.09virtual circuit status157100.004,596292720.03PX Idle Wait1,07297.112,58124070.19jobq slave wait14597.9342028960.03Streams AQ: waiting for time management or cleanup tasks1100.002702697470.00PX Deq: Parse Reply4040.00030.01PX Deq: Execution Msg12126.45000.02PX Deq: Join ACK3842.11010.01PX Deq: Execute Reply3432.35000.01PX Deq: Msg Fragment160.00000.00Streams AQ: RAC qmn coordinator idle wait351100.00000.06class slave wait20.00000.00db file scattered read等待事件是当SESSION等待multi-block I/O时发生的,通过是由于full table scans或 index fast full scans。发生过多读操作的Segments可以在“Segments by Physical Reads”和 “SQL ordered by Reads”节中识别(在其它版本的报告中,可能是别的名称)。如果在OLTP应用中,不应该有过多的全扫描操作,而应使用选择性好的索引操作。DB file sequential read等待意味着发生顺序I/O读等待(通常是单块读取到连续的内存区域中),如果这个等待非常严重,应该使用上一段的方法确定执行读操作的热点SEGMENT,然后通过对大表进行分区以减少I/O量,或者优化执行计划(通过使用存储大纲或执行数据分析)以避免单块读操作引起的sequential read等待。通过在批量应用中,DB file sequential read是很影响性能的事件,总是应当设法避免。Log File Parallel Write事件是在等待LGWR进程将REDO记录从LOG 缓冲区写到联机日志文件时发生的。虽然写操作可能是并发的,但LGWR需要

    注意事项

    本文(Oracle AWR 报告分析实例讲解.docx)为本站会员(飞****)主动上传,淘文阁 - 分享文档赚钱的网站仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知淘文阁 - 分享文档赚钱的网站(点击联系客服),我们立即给予删除!

    温馨提示:如果因为网速或其他原因下载失败请重新下载,重复下载不扣分。




    关于淘文阁 - 版权申诉 - 用户使用规则 - 积分规则 - 联系我们

    本站为文档C TO C交易模式,本站只提供存储空间、用户上传的文档直接被用户下载,本站只是中间服务平台,本站所有文档下载所得的收益归上传人(含作者)所有。本站仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。若文档所含内容侵犯了您的版权或隐私,请立即通知淘文阁网,我们立即给予删除!客服QQ:136780468 微信:18945177775 电话:18904686070

    工信部备案号:黑ICP备15003705号 © 2020-2023 www.taowenge.com 淘文阁 

    收起
    展开