《Oracle AWR 报告分析实例讲解6424.docx》由会员分享，可在线阅读，更多相关《Oracle AWR 报告分析实例讲解6424.docx（166页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、WORKLOAD REPOSITORY report for DB NameeDB IdInstancceInst nuumReleaseeRACHostICCI13140988396ICCI1110.2.0.3.0YESHPGICCII1Snap IddSnap TiimeSessionnsCursorss/SesssionBegin SSnap:267825-Dec-08 144:04:550241.5End Snaap:268025-Dec-08 155:23:337261.5Elapsedd:78.79 (mins)DB Timee:11.05 (mins)DB Timee不包括Ora
2、acle后台台进程消耗的的时间。如果果DB Tiime远远小小于Elappsed时间间，说明数据据库比较空闲闲。在79分钟里（其其间收集了33次快照数据据），数据库库耗时11分钟，RDAA数据中显示示系统有8个逻辑CPUU（4个物理CPUU），平均每每个CPU耗时1.4分钟，CPUU利用率只有有大约2%（1.4/779）。说明明系统压力非非常小。可是对于批量系系统，数据库库的工作负载载总是集中在在一段时间内内。如果快照照周期不在这这一段时间内内，或者快照照周期跨度太太长而包含了了大量的数据据库空闲时间间，所得出的的分析结果是是没有意义的的。这也说明明选择分析时时间段很关键键，要选择能能够代表性
3、能能问题的时间间段。Report SummaaryCache SSizes BeginEndBuffer Cachee:3,344M3,344MStd Bloock Siize:8KShared Pool Size:704M704MLog Bufffer:14,352KK显示SGA中每每个区域的大大小（在AMMM改变它们们之后），可可用来与初始始参数值比较较。shared pool主主要包括liibraryy cachhe和dictiionaryy cachhe。libraary caache用来来存储最近解解析（或编译译）后SQLL、PL/SQQL和Java classses等。librra
4、ry ccache用用来存储最近近引用的数据据字典。发生生在librrary ccache或或dictiionaryy cachhe的cachee misss代价要比发发生在bufffer ccache的的代价高得多多。因此shhared pool的的设置要确保保最近使用的的数据都能被被cachee。Load PrrofileePer SeccondPer TraansacttionRedo siize:918,8055.72775,9122.72Logicall readds:3,521.7772,974.006Block cchangees:1,817.9951,535.222Physic
5、aal reaads:68.2657.64Physicaal wriites:362.59306.20User caalls:326.69275.88Parses:38.6632.65Hard paarses:0.030.03Sorts:0.610.51Logons:0.010.01Executees:354.34299.23Transacctionss:1.18% Blockks chaanged per RRead:51.62Recursiive Caall %:51.72Rollbacck perr trannsactiion %:85.49Rows peer Sorrt:#显示数据库负
6、载载概况，将之之与基线数据据比较才具有有更多的意义义，如果每秒秒或每事务的的负载变化不不大，说明应应用运行比较较稳定。单个个的报告数据据只说明应用用的负载情况况，绝大多数数据并没有一一个所谓“正确”的值，然而而Logonns大于每秒秒12个、Hardd parsses大于每每秒100、全部部parsees超过每秒秒300表明可可能有争用问问题。Redo siize：每秒秒/每事务产生生的redoo大小（单位位字节），可可标志数据库库任务的繁重重程序。Logicall readds：每秒/每事务逻辑辑读的块数Block cchangees：每秒/每事务修改改的块数Physicaal reaads
7、：每秒秒/每事务物理理读的块数Physicaal wriites：每每秒/每事务物理理写的块数User caalls：每每秒/每事务用户户call次数数Parses：SQL解析的的次数Hard paarses：其中硬解析析的次数，硬硬解析太多，说说明SQL重用率率不高。Sorts：每每秒/每事务的排排序次数Logons：每秒/每事务登录录的次数Executees：每秒/每事务SQLL执行次数Transacctionss：每秒事务务数Blocks changged peer Reaad：表示逻逻辑读用于修修改数据块的的比例Recursiive Caall：递归归调用占所有有操作的比率率Rollb
8、acck perr trannsactiion：每事事务的回滚率率Rows peer Sorrt：每次排排序的行数注:Oracle的的硬解析和软软解析 提到软解析析(softt parsse)和硬解析(hhard pparse)，就不能不不说一下Orracle对对sql的处理理过程。当你你发出一条ssql语句交交付Oraccle，在执执行和获取结结果前，Orracle对对此sql将进行行几个步骤的的处理过程：1、语法检检查(synntax ccheck)检查此sqql的拼写是是否语法。2、语义检检查(semmanticc checck)诸如检查ssql语句中中的访问对象象是否存在及及该用户是否
9、否具备相应的的权限。3、对sqql语句进行行解析(parsee)利用内部算算法对sqll进行解析，生生成解析树(parsee treee)及执行计计划(exeecutioon plaan)。4、执行ssql，返回回结果(exxecutee and returrn)其中，软、硬硬解析就发生生在第三个过过程里。Oraclle利用内部部的hashh算法来取得得该sql的hash值，然然后在libbrary cachee里查找是否否存在该haash值；假设存在，则则将此sqll与cachee中的进行比比较；假设“相同同”，就将利用用已有的解析析树与执行计计划，而省略略了优化器的的相关工作。这这也就是软
10、解解析的过程。诚然，如果果上面的2个假设中任任有一个不成成立，那么优优化器都将进进行创建解析析树、生成执执行计划的动动作。这个过过程就叫硬解解析。创建解析树树、生成执行行计划对于ssql的执行行来说是开销销昂贵的动作作，所以，应应当极力避免免硬解析，尽尽量使用软解解析。Instancce Effficienncy Peercenttages (Targget 1000%) Buffer Nowaiit %:100.00Redo NooWait %:100.00Buffer Hit %:98.72In-memoory Soort %:99.86Libraryy Hit %:99.97Soft P
11、aarse %:99.92Executee to PParse %:89.09Latch HHit %:99.99Parse CCPU too Parsse Elaapsd %:7.99% Non-PParse CPU:99.95本节包含了Orracle关关键指标的内内存命中率及及其它数据库库实例操作的的效率。其中中Buffeer Hitt Ratiio 也称Cachhe Hitt Ratiio，Libraary Hiit rattio也称Librrary CCache Hit rratio。同同Load Profiile一节相相同，这一节节也没有所谓谓“正确”的值，而只只能根据应用用的特点判
12、断断是否合适。在一个使用直接读执行大型并行查询的DSS环境，20%的Buffer Hit Ratio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。根据Oracle的经验，对于OLTPT系统，Buffer Hit Ratio理想应该在90%以上。Buffer Nowaiit表示在内内存获得数据据的未等待比比例。buffer hit表示示进程从内存存中找到数据据块的比率，监监视这个值是是否发生重大大变化比这个个值本身更重重要。对于一一般的OLTTP系统，如如果此值低于于80%，应该该给数据库分分配更多的内内存。Redo NooWait表表示在LOGG缓冲区获得得BUFFEER的未等待
13、待比例。如果果太低（可参参考90%阀值），考考虑增加LOOG BUFFFER。libraryy hit表表示Oraccle从Libraary Caache中检检索到一个解解析过的SQQL或PL/SQQL语句的比比率，当应用用程序调用SSQL或存储储过程时，OOraclee检查Librrary CCache确确定是否存在在解析过的版版本，如果存存在，Oraacle立即即执行语句；如果不存在在，Oraccle解析此此语句，并在在Libraary Caache中为为它分配共享享SQL区。低的librrary hhit raatio会导导致过多的解解析，增加CCPU消耗，降降低性能。如如果librra
14、ry hhit raatio低于于90%，可能能需要调大ssharedd pooll区。Latch HHit：Latchh是一种保护护内存结构的的锁，可以认认为是SERRVER进程程获取访问内内存数据结构构的许可。要要确保Lattch Hiit99%，否则意味味着Sharred Poool laatch争用用，可能由于于未共享的SSQL，或者者Libraary Caache太小小，可使用绑绑定变更或调调大Sharred Poool解决。Parse CCPU too Parsse Elaapsd：解解析实际运行行时间/(解析实际际运行时间+解析中等待待资源时间)，越高越好。Non-Parrse
15、CPPU ：SQL实际运运行时间/(SQL实际际运行时间+SQL解析析时间)，太低表示解解析消耗时间间过多。Executee to PParse：是语句执行行与分析的比比例，如果要要SQL重用率率高，则这个个比例会很高高。该值越高高表示一次解解析后被重复复执行的次数数越多。In-memoory Soort：在内内存中排序的的比率，如果果过低说明有有大量的排序序在临时表空空间中进行。考考虑调大PGGA。Soft Paarse：软软解析的百分分比（soffts/soofts+hhards），近似当作作sql在共享享区的命中率率，太低则需需要调整应用用使用绑定变变量。Shared Pool Stat
16、iisticss BeginEndMemory Usagee %:47.1947.50% SQL wwith eexecuttions1:88.4879.81% Memorry forr SQL w/exeec1:79.9973.52Memory Usagee %：对于于一个已经运运行一段时间间的数据库来来说，共享池池内存使用率率，应该稳定定在75%-90%间，如如果太小，说说明Sharred Poool有浪费费，而如果高高于90，说明共共享池中有争争用，内存不不足。SQL witth exeecutioons1：执行次数大大于1的sql比率，如果此此值太小，说说明需要在应应用中更多使使用绑定
17、变量量，避免过多多SQL解析。Memory for SSQL w/exec1：执行次次数大于1的SQL消耗内内存的占比。Top 5 TTimed Eventts EventWaitsTime(s)Avg Waiit(ms)% Totall Calll TimeeWait CllassCPU timme51577.6SQL*Nett moree dataa fromm clieent27,3196429.7Networkklog fille parrallell writte5,4974797.1System I/Odb filee sequuentiaal reaad7,9003545.3Us
18、er I/Odb filee paraallel writee4,8063475.1System I/O这是报告概要的的最后一节，显显示了系统中中最严重的55个等待，按按所占等待时时间的比例倒倒序列示。当当我们调优时时，总希望观观察到最显著著的效果，因因此应当从这这里入手确定定我们下一步步做什么。例例如如果buffeer bussy waiit是较严严重的等待事事件，我们应应当继续研究究报告中Buuffer Wait和和File/Tableespacee IO区的的内容，识别别哪些文件导导致了问题。如果最严重的等待事件是I/O事件，我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量
19、I/O，并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，就需要察看详细的LATCH统计识别哪些LATCH产生的问题。在这里，logg filee paraallel writee是相对比较较多的等待，占占用了7%的CPU时间。通常，在没有问问题的数据库库中，CPUU timee总是列在第第一个。更多的等待事件件，参见本报报告 的Wait Eventts一节。RAC StaatistiicsBeginEndNumber of Innstancces:22Global Cachee Loadd Proffile Per SeccondPer Traansac
20、ttionGlobal Cachee bloccks reeceiveed:4.163.51Global Cachee bloccks seerved:5.975.04GCS/GESS messsages receiived:408.47344.95GCS/GESS messsages sent:258.03217.90DBWR Fuusion writees:0.050.05Estd Inntercoonnectt trafffic (KB)211.16Global Cachee Effiicienccy Perrcentaages (Targeet loccal+reemote 100%)
21、 Buffer accesss - llocal cachee %:98.60Buffer accesss - rremotee cachhe %:0.12Buffer accesss - ddisk %:1.28Global Cachee and Enqueeue Seervicees - WWorklooad Chharactteristtics Avg gloobal eenqueuue gett timee (ms):0.1Avg gloobal ccache cr bllock rreceivve timme (mss):1.1Avg gloobal ccache curreent
22、bllock rreceivve timme (mss):0.8Avg gloobal ccache cr bllock bbuild time (ms):0.0Avg gloobal ccache cr bllock ssend ttime (ms):0.0Global cachee log flushhes foor cr blockks serrved %:3.5Avg gloobal ccache cr bllock fflush time (ms):3.9Avg gloobal ccache curreent bllock ppin tiime (mms):0.0Avg glooba
23、l ccache curreent bllock ssend ttime (ms):0.0Global cachee log flushhes foor currrent blockks serrved %:0.4Avg gloobal ccache curreent bllock fflush time (ms):3.0Global Cachee and Enqueeue Seervicees - MMessagging SStatisstics Avg messsage sent queuee timee (ms):0.0Avg messsage sent queuee timee on
24、kksxp (ms):0.3Avg messsage receiived qqueue time (ms):0.5Avg GCSS messsage pprocesss timme (mss):0.0Avg GESS messsage pprocesss timme (mss):0.0% of diirect sent messaages:14.40% of inndirecct sennt messsagess:77.04% of fllow coontrollled mmessagges:8.56Main Reeport Wait Evvents Statiisticss SQL Staa
25、tistiics Instancce Acttivityy Stattisticcs IO Statts Buffer Pool Statiisticss Advisorry Staatistiics Wait Sttatisttics Undo Sttatisttics Latch SStatisstics Segmentt Stattisticcs Dictionnary CCache Statiisticss Libraryy Cachhe Staatistiics Memory Statiisticss Streamss Stattisticcs Resourcce Limmit St
26、tatisttics init.orra Parrameteers Wait Evvents Statiisticss Time Moodel SStatisstics Wait Cllass Wait Evvents Backgroound WWait EEventss Operatiing Syystem Statiisticss Servicee Stattisticcs Servicee Waitt Classs Staats Back too TopTime Moodel SStatisstics Total ttime iin dattabasee userr-callls (DB
27、B Timee): 6663s Statisttics iincludding tthe woord bbackgrround meassure bbackgrround proceess tiime, aand soo do nnot coontribbute tto thee DB ttime sstatisstic Orderedd by % or DDB timme dessc, Sttatisttic naame Statisttic NaameTime (ss)% of DBB TimeeDB CPU514.5077.61sql exeecute elapssed tiime482
28、.2772.74parse ttime eelapseed3.760.57PL/SQL execuution elapssed tiime0.500.08hard paarse eelapseed timme0.340.05connecttion mmanageement call elapssed tiime0.080.01hard paarse (shariing crriteriia) ellapsedd timee0.000.00repeateed binnd elaapsed time0.000.00PL/SQL compiilatioon elaapsed time0.000.00
29、failed parsee elappsed ttime0.000.00DB timee662.97backgroound eelapseed timme185.19backgroound ccpu tiime67.48此节显示了各种种类型的数据据库处理任务务所占用的CCPU时间。Back too Waitt Evennts Sttatisttics Back to ToopWait Cllass s - seccond cs - ceentiseecond - 1000th off a seecond ms - miilliseecond - 10000th oof a ssecondd u
30、s - miicroseecond - 10000000tth of a seccond orderedd by wwait ttime ddesc, waitss descc Wait CllassWaits%Time -outsTotal WWait TTime (s)Avg waiit (mss)Waits /txnUser I/O66,8370.00120211.94System I/O28,2950.009335.05Networkk1,571,44500.00660280.72Clusterr210,54880.0029037.61Other81,78371.8228014.61A
31、pplicaation333,15550.0016059.51Concurrrency5,1820.04510.93Commit9190.00440.16Configuuratioon25,42799.46104.54Back too Waitt Evennts Sttatisttics Back to ToopWait Evvents s - seccond cs - ceentiseecond - 1000th off a seecond ms - miilliseecond - 10000th oof a ssecondd us - miicroseecond - 10000000tth
32、 of a seccond orderedd by wwait ttime ddesc, waitss descc (idlle eveents llast) EventWaits%Time -outsTotal WWait TTime (s)Avg waiit (mss)Waits /txnSQL*Nett moree dataa fromm clieent27,3190.006424.88log fille parrallell writte5,4970.004790.98db filee sequuentiaal reaad7,9000.003541.41db filee paraall
33、el writee4,8060.003470.86db filee scattteredd readd10,3100.003131.84direct path writee42,7240.003017.63reliablle messsage3552.8218490.06SQL*Nett breaak/resset too clieent333,08440.0016059.50db filee paraallel read3,7320.001340.67gc currrent mmulti blockk requuest175,71000.0010031.39controll filee se
34、quuentiaal reaad15,9740.001012.85direct path read temp1,8730.00950.33gc cr mmulti blockk requuest20,8770.00803.73log fille synnc9190.00440.16gc cr bblock busy5260.00360.09enq: FBB - coontenttion10,3840.00301.85DFS locck hanndle3,5170.00310.63controll filee paraallel writee1,9460.00310.35gc currrent
35、bblock 2-wayy4,1650.00200.74libraryy cachhe locck4320.00240.08name-seervicee calll waitt220.002760.00row cacche loock3,8940.00200.70gcs logg flussh synnc1,25942.02210.22os threead sttartupp185.562890.00gc cr bblock 2-wayy3,6710.00200.66gc currrent bblock busy1130.001120.02SQL*Nett messsage tto cliie
36、nt1,544,11150.0010275.83gc bufffer buusy156.671700.00gc cr ddisk rread3,2720.00100.58direct path writee tempp1590.00150.03gc currrent ggrant busy8980.00110.16log fille swiitch ccompleetion290.001170.01CGS waiit forr IPC msg48,73999.87008.71gc currrent ggrant 2-wayy1,1420.00000.20kjbdrmccvtq llmon dd
37、rm quuiescee: pinng commpletiion90.000190.00enq: USS - coontenttion5670.00000.10direct path read1380.00010.02enq: WFF - coontenttion140.00090.00ksxr pooll reemote instaances13,29158.45002.37libraryy cachhe pinn2110.00010.04ges gloobal rresourrce diirectoory too be ffrozenn9100.000100.00wait foor scn
38、n ack5830.00000.10log fille seqquentiial reead360.00020.01undo seegmentt exteensionn25,34299.79004.53rdbms iipc reeply2790.00000.05ktfbtgeex6100.000100.00enq: HWW - coontenttion440.00010.01gc cr ggrant 2-wayy1580.00000.03enq: TXX - inndex ccontenntion10.000340.00enq: CFF - coontenttion640.00010.01PX
39、 Deq: Signnal ACCK3721.62010.01latch ffree30.000100.00buffer busy waitss6250.16000.11KJC: Waait foor msgg sendds to compllete1540.00000.03log bufffer sspace110.00020.00enq: PSS - coontenttion460.00010.01enq: TMM - coontenttion700.00000.01IPC sennd commpletiion syync40100.00000.01PX Deq: reapp creddi
40、t1,54499.81000.28log fille sinngle wwrite360.00000.01enq: TTT - coontenttion460.00000.01enq: TDD - KTTF dummp enttries120.00010.00read byy otheer sesssion10.000120.00LGWR waait foor reddo coppy5400.00000.10PX Deq Crediit: seend bllkd175.88000.00enq: TAA - coontenttion140.00000.00latch: ges rresourrc
41、e haash liist440.00000.01enq: PII - coontenttion80.00000.00write ccompleete waaits10.00020.00enq: DRR - coontenttion30.00000.00enq: MWW - coontenttion30.00000.00enq: TSS - coontenttion30.00000.00PX qreff latcch150100.00000.03enq: MDD - coontenttion20.00000.00latch: KCL ggc eleement parennt lattch110
42、.00000.00enq: JSS - joob runn lockk - syynchroonize10.00010.00SQL*Nett moree dataa to cclientt160.00000.00latch: cachee bufffers llru chhain10.00000.00enq: ULL - coontenttion10.00000.00gc currrent ssplit10.00000.00enq: AFF - taask seerialiizatioon10.00000.00latch: objecct queeue heeader operaation30.00000.00latch: cachee bufffers cchainss10.00000.00latch: enqueeue haash chhains20.00000.00SQL