WIXLIB

AWR Report Detailed AnalysisMike AultOracle GuruTexas Memory SystemsA Texas Memory Systems Presentation

Michael R. AultOracle Guru- Nuclear Navy 6 years- Nuclear Chemist/Programmer 10 years- Kennedy Western University Graduate- Bachelors Degree Computer Science- Certified in all Oracle Versions Since 6- Oracle DBA, author, since 1990A Texas Memory Systems Presentation

Books by Michael R. AultA Texas Memory Systems Presentation

Statspackanalyzer.comFree Statspack/AWR AnalysisSponsored by Texas Memory Systems-Looks for IO bottlenecks and otherconfiguration issues.-Straightforward tuning adviceA Texas Memory Systems Presentation

IntroductionStatspack was introduced in 8.1.7z AWR came out in Oracle10gz Both are very similarz Both provide a top-down look atperformance statisticszA Texas Memory Systems Presentation

What Is AWRA background processz A set of tablesz A set of reportsz Takes snapshots of statistics every hourz Takes snapshot of high-cost SQL everyhourzA Texas Memory Systems Presentation

Preparation for AnalysisKnow your systems normal performancefingerprintz Be familiar with Concepts and TuningGuidesz Have “normal” AWR/Statspacks forcomparisonzA Texas Memory Systems Presentation

Top-Down ApproachReport starts with settings overviewz Next provides Top-5 waitsz Use the Waits to guide furtherinvestigationzA Texas Memory Systems Presentation

AWR Report HeaderWORKLOAD REPOSITORY report forDB NameDB IdInstanceInst Num Startup TimeReleaseRAC------------ ----------- ------------ -------- --------------- ----------- --AULTDB4030696936 aultdb11 04-Aug-08 10:16 11.1.0.6.0 YESHost NamePlatformCPUs Cores Sockets Memory(GB)---------------- -------------------------------- ---- ----- ------- ---------aultlinux3Linux IA (32-bit)2112.97Snap IdSnap TimeSessions Curs/Sess--------- ------------------- -------- --------Begin Snap:91 04-Aug-08 12:00:15411.2End Snap:92 04-Aug-08 13:00:28471.1Elapsed:60.22 (mins)DB Time:139.52 (mins)Cache SizesBeginEnd ---------- ---------Buffer Cache:1,312M1,312M Std Block Size:8KShared Pool Size:224M224MLog Buffer:10,604KA Texas Memory Systems Presentation

Know Your Load Type!zOnline Transaction ProcessingzzzzzDecision Support/Data WarehousezzzzzFew readsMany writesMany small transactionsLook for redo/undo and sequential read issuesMany readsFew writes (other then possible temp)Few transactionsLook for sort/workarea and scattered read issuesMixed or HybridA Texas Memory Systems Presentation

Load Profile SectionLoad Profile DB Time(s):DB CPU(s):Redo size:Logical reads:Block changes:Physical reads:Physical writes:User calls:Parses:Hard parses:W/A MB er .92.22.00.0932,965.40.13.70.10.3Per TransactionPer ExecPer Call--------------- ---------- 8,317.8267.36.86.10.12,868,990.90.211.30.3A Texas Memory Systems Presentation

What Are Your EfficienciesShould be close to 100%z Parse issues usually are a result of:zBad bind variable usagez Insufficient memoryz Will also be co-indicated by low percentage ofmemory for multiple SQL executionzA Texas Memory Systems Presentation

Load Profile SectionInstance Efficiency Percentages (Target 100%) Buffer Nowait %: 100.00Redo NoWait %:Buffer Hit%:96.09In-memory Sort %:Library Hit%:98.17Soft Parse %:Execute to Parse %:45.80Latch Hit %:Parse CPU to Parse Elapsd %:0.00% Non-Parse CPU:Shared Pool StatisticsBeginEnd------ -----Memory Usage %:81.5385.39% SQL with executions 1:79.2979.48% Memory for SQL w/exec 1:76.7378.1999.97100.0097.8899.9599.77A Texas Memory Systems Presentation

Top 5 Waits SectionCritical to look closely at this sectionz Use highest wait times to guideinvestigationzDB FILE type waits – physical IOz BUFFER type waits – Logical IOz LOG type waits – Redo relatedz PX – Parallel Queryz GC – Global Cache (RAC related)zA Texas Memory Systems Presentation

Top 5 Waits SectionTop 5 Timed Foreground Events EventWaits------------------------------ -----------db file sequential read465,020DB CPUdb file parallel read2,251db file scattered read15,268gc current block 2-way108,739Avgwait% DBTime(s)(ms)time Wait Class----------- ------ ------ ---------3,969947.4 User I/O99511.93221433.8 User I/O153101.8 User I/O11611.4 ClusterA Texas Memory Systems Presentation

CPU and MemoryWatch for number of CPUsz Pay attention to changes in Memory sizez An idle CPU can be a bad thingz Always look at IO Wait verses CPU usagez If the system is IO bound CPU will be idle!zA Texas Memory Systems Presentation

CPU and Memory SectionsHost CPU (CPUs:2 Cores:1 Sockets:1) Load AverageBeginEnd%User%System%WIO%Idle--------- --------- --------- --------- --------- --------0.373.0510.66.745.382.6Instance CPU % of total CPU for Instance: 14.8% of busy CPU for Instance: 85.0%DB time waiting for CPU - Resource Mgr:0.0Memory Statistics BeginEndHost Mem (MB):3,041.43,041.4SGA use (MB):1,584.01,584.0PGA use (MB):169.0301.7% Host Mem used for SGA PGA:57.6457.64A Texas Memory Systems Presentation

RAC Specific SectionsIf you are on a Real Application Clusterthese show upz If not on RAC they don’tz Show health of Global Cache (GC)z Show health of Global Enqueue (GES)z Show health of interconnect (Latency,send, receive times)z 1gb interconnect 100 MB/sec (approx)zA Texas Memory Systems Presentation

RAC Load ProfilesRAC StatisticsDB/Inst: AULTDB/aultdb1 Snaps: 91-92BeginEnd----- ----Number of Instances:22Global Cache Load Profile Per SecondPer Transaction----------------------------Global Cache blocks received:26.5181.54Global Cache blocks served:26.0280.01GCS/GES messages received:156.31480.68GCS/GES messages sent:157.74485.06DBWR Fusion writes:0.010.04Estd Interconnect traffic (KB)481.59Global Cache Efficiency Percentages (Target local remote 100%) Buffer access - local cache %:95.44Buffer access - remote cache %:0.65Buffer access disk %:3.91A Texas Memory Systems Presentation

Global Cache and Enqueue WorkloadWatch for timingsz If interconnect latency IO subsystemlatency, RAC is a bottleneckz Components of time can show whereissue is locatedzA Texas Memory Systems Presentation

Global Cache and EnqueueGlobal Cache and Enqueue Services - Workload Characteristics Avg global enqueue get time (ms):0.2Avg global cache cr block receive time (ms):1.8Avg global cache current block receive time (ms):1.8Avg global cache cr block build time (ms):0.0Avg global cache cr block send time (ms):0.1Global cache log flushes for cr blocks served %:0.8Avg global cache cr block flush time (ms):17.5Avg global cache current block pin time (ms):0.0Avg global cache current block send time (ms):0.1Global cache log flushes for current blocks served %:0.0Avg global cache current block flush time (ms):20.0Global Cache and Enqueue Services - Messaging Statistics Avg message sent queue time (ms):1.1Avg message sent queue time on ksxp (ms):1.3Avg message received queue time (ms):0.1Avg GCS message process time (ms):0.0Avg GES message process time (ms):0.0% of direct sent messages:35.13% of indirect sent messages:64.34% of flow controlled ------------------------A Texas Memory Systems Presentation

Global Cache and EnqueueThe most important statistics in this entire section are:Avg global cache cr block receive time (ms):Avg global cache current block receive time (ms):1.81.8These should be compared to an AWR report run on theother instance:Avg global cache cr block receive time (ms):Avg global cache current block receive time (ms):2.11.7If the numbers on both or all RCA instances aren’t similarthen this could indicate a problem with the interconnect eitherat the OS buffer level or the NIC or interface cardsthemselves.A Texas Memory Systems Presentation

Time Model StatisticsIn early versions had to do this manuallyz Some Statspack won’t have thisz Shows where system is spending its timez Generally you want SQL processing timehigh, parsing and other stuff lowz If SQL time DB CPU time then probablyhave IO issueszA Texas Memory Systems Presentation

Time Model StatisticsTime Model StatisticsDB/Inst: AULTDB/aultdb1 Snaps: 91-92- Total time in database user-calls (DB Time): 8371.3s- Statistics including the word "background" measure background processtime, and so do not contribute to the DB time statistic- Ordered by % or DB time desc, Statistic nameStatistic NameTime (s) % of DB Time------------------------------------------ ------------------ -----------sql execute elapsed time8,145.597.3DB CPU995.111.9parse time elapsed7.4.1hard parse elapsed time5.2.1PL/SQL execution elapsed time4.8.1Java execution elapsed time0.7.0hard parse (sharing criteria) elapsed time0.2.0sequence load elapsed time0.1.0repeated bind elapsed time0.1.0PL/SQL compilation elapsed time0.0.0failed parse elapsed time0.0.0hard parse (bind mismatch) elapsed time0.0.0DB time8,371.3background elapsed time214.7background cpu -------------------A Texas Memory Systems Presentation

Operating System StatisticsWhat you get depends on OSz Some may not give IO timingszA Texas Memory Systems Presentation

Operating System StatisticsOperating System StatisticsDB/Inst: AULTDB/aultdb1 Snaps: 91-92- *TIME statistic values are diffed.All others display actual values. End Value is displayed if different- ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), NameStatisticValueEnd Value------------------------- ---------------------- ---------------BUSY TIME126,029IDLE TIME597,505IOWAIT TIME327,861NICE TIME766SYS TIME48,452USER TIME76,784LOAD03PHYSICAL MEMORY BYTES3,189,190,656NUM CPUS2NUM CPU CORES1NUM CPU SOCKETS1GLOBAL RECEIVE SIZE MAX4,194,304GLOBAL SEND SIZE MAX262,144TCP RECEIVE SIZE DEFAULT87,380TCP RECEIVE SIZE MAX1,048,576TCP RECEIVE SIZE MIN4,096TCP SEND SIZE DEFAULT65,536TCP SEND SIZE MAX1,048,576TCP SEND SIZE MIN4,096A Texas Memory Systems Presentation

Operating System --------------------Operating System Statistics - DetailDB/Inst: AULTDB/aultdb1 Snaps: 91-92Snap TimeLoad%busy%user%sys%idle %iowait--------------- -------- -------- -------- -------- -------- -------04-Aug 12:00:150.4N/AN/AN/AN/AN/A04-Aug -----------------------------------------A Texas Memory Systems Presentation

Foreground Wait eventsForeground user processesz Usually most importantz Usual source for top 5 wait eventsz 2 sections – classes and eventsz Classes are the rolled up sums for waitszA Texas Memory Systems Presentation

Foreground Wait ClassesForeground Wait ClassDB/Inst: AULTDB/aultdb1 Snaps: 91-92- s - second, ms - millisecond 1000th of a second- ordered by wait time desc, waits desc- %Timeouts: value of 0 indicates value was .5%. Value of null is truly 0- Captured Time accounts for68.9% of Total DB time8,371.33 (s)- Total FG Wait Time:4,770.85 (s) DB CPU time:995.13 (s)Avg%TimeTotal WaitwaitWait ClassWaits -outsTime (s)(ms) %DB time-------------------- ---------------- ----- ---------------- -------- --------User I/O518,26704,449953.1DB tion7400000.0System --------A Texas Memory Systems Presentation

Foreground Wait EventsForeground Wait EventsDB/Inst: AULTDB/aultdb1 Snaps: 91-92- s - second, ms - millisecond 1000th of a second- Only events with Total Wait Time (s) .001 are shown- ordered by wait time desc, waits desc (idle events last)- %Timeouts: value of 0 indicates value was .5%. Value of null is truly 0Avg%Time Total WaitwaitWaits% DBEventWaits -outsTime (s)(ms)/txntime-------------------------- ------------ ----- ---------- ------- -------- -----db file sequential read465,02003,9699395.847.4db file parallel read2,25103221431.93.8db file scattered read15,26801531013.01.8gc current block 2-way108,73911116192.51.4PX Deq: reap credit3,247,7031001070 2,764.01.3gc cr grant 2-way57,265728048.7.3gc cr multi block request22,451623119.1.3enq: BF - allocation conte1493149830.0.2PX qref latch555,84310090473.1.1IPC send completion sync1,07052880.9.1gc remaster22052210.0.1A Texas Memory Systems Presentation

Background Wait EventsSMON, PMON, DBWR, LMON, LMS, etcprocess waitsz Usually not a big contributorz Types of waits should correspond toforeground waitszA Texas Memory Systems Presentation