Automatic Performance Statistics.docx
- 文档编号:17751716
- 上传时间:2023-08-03
- 格式:DOCX
- 页数:20
- 大小:26.30KB
Automatic Performance Statistics.docx
《Automatic Performance Statistics.docx》由会员分享,可在线阅读,更多相关《Automatic Performance Statistics.docx(20页珍藏版)》请在冰点文库上搜索。
AutomaticPerformanceStatistics
AutomaticPerformanceStatistics
Thischapterdiscussesthegatheringofperformancestatistics.Thischaptercontainsthefollowingtopics:
∙OverviewofDataGathering
∙AutomaticWorkloadRepository
SeeAlso:
OracleDatabase2DayDBAforinformationonmonitoringandtuningthedatabase
OverviewofDataGathering
Toeffectivelydiagnoseperformanceproblems,statisticsmustbeavailable.Oraclegeneratesmanytypesofcumulativestatisticsforthesystem,sessions,andindividualSQLstatements.Oraclealsotrackscumulativestatisticsonsegmentsandservices.Whenanalyzingaperformanceprobleminanyofthesescopes,youtypicallylookatthechangeinstatistics(deltavalue)overtheperiodoftimeyouareinterestedin.Specifically,youlookatthedifferencebetweenthecumulativevalueofastatisticatthestartoftheperiodandthecumulativevalueattheend.
Cumulativevaluesforstatisticsaregenerallyavailablethroughdynamicperformanceviews,suchastheV$SESSTATandV$SYSSTATviews.Notethatthecumulativevaluesindynamicviewsareresetwhenthedatabaseinstanceisshutdown.TheAutomaticWorkloadRepository(AWR)automaticallypersiststhecumulativeanddeltavaluesformostofthestatisticsatalllevelsexceptthesessionlevel.ThisprocessisrepeatedonaregulartimeperiodandtheresultiscalledanAWRsnapshot.Thedeltavaluescapturedbythesnapshotrepresentthechangesforeachstatisticoverthetimeperiod.See"AutomaticWorkloadRepository".
AnothertypeofstatisticcollectedbyOracleiscalledametric.Ametricisdefinedastherateofchangeinsomecumulativestatistic.Thatratecanbemeasuredagainstavarietyofunits,includingtime,transactions,ordatabasecalls.Forexample,thenumberdatabasecallspersecondisametric.MetricvaluesareexposedinsomeV$views,wherethevaluesaretheaverageoverafairlysmalltimeinterval,typically60seconds.AhistoryofrecentmetricvaluesisavailablethroughV$views,andsomeofthedataisalsopersistedbyAWRsnapshots.
AthirdtypeofstatisticaldatacollectedbyOracleissampleddata.Thissamplingisperformedbytheactivesessionhistory(ASH)sampler.ASHsamplesthecurrentstateofallactivesessions.ThisdataiscollectedintomemoryandcanbeaccessedbyaV$view.ItisalsowrittenouttopersistentstorebytheAWRsnapshotprocessing.See"ActiveSessionHistory(ASH)".
Apowerfultoolfordiagnosingperformanceproblemsistheuseofstatisticalbaselines.Astatisticalbaselineiscollectionofstatisticratesusuallytakenovertimeperiodwherethesystemisperformingwellatpeakload.Comparingstatisticscapturedduringaperiodofbadperformancetoabaselinehelpsdiscoverspecificstatisticsthathaveincreasedsignificantlyandcouldbethecauseoftheproblem.
AWRsupportsthecaptureofbaselinedatabyenablingyoutospecifyandpreserveapairorrangeofAWRsnapshotsasabaseline.Carefullyconsiderthetimeperiodyouchooseasabaseline;thebaselineshouldbeagoodrepresentationofthepeakloadonthesystem.Inthefuture,youcancomparethesebaselineswithsnapshotscapturedduringperiodsofpoorperformance.
OracleEnterpriseManageristherecommendedtoolforviewingbothrealtimedatainthedynamicperformanceviewsandhistoricaldatafromtheAWRhistorytables.EnterprisemanageralsoisabletocaptureoperatingsystemandnetworkstatisticaldatathatcanbecorrelatedwithAWRdata.
DatabaseStatistics
Databasestatisticsprovideinformationonthetypeofloadonthedatabase,aswellastheinternalandexternalresourcesusedbythedatabase.Thissectiondescribessomeofthemoreimportantstatistics.
WaitEvents
Waiteventsarestatisticsthatareincrementedbyaserverprocess/threadtoindicatethatithadtowaitforaneventtocompletebeforebeingabletocontinueprocessing.Waiteventdatarevealsvarioussymptomsofproblemsthatmightbeimpactingperformance,suchaslatchcontention,buffercontention,andI/Ocontention.
Toenableeasierhigh-levelanalysisofthewaitevents,theeventsaregroupedintoclasses.Thewaiteventclassesinclude:
Administrative,Application,Cluster,Commit,Concurrency,Configuration,Idle,Network,Other,Scheduler,SystemI/O,andUserI/O.
Thewaitclassesarebasedonacommonsolutionthatusuallyappliestofixingaproblemwiththewaitevent.Forexample,exclusiveTXlocksaregenerallyanapplicationlevelissueandHWlocksaregenerallyaconfigurationissue.
Thefollowinglistincludescommonexamplesofthewaitsinsomeoftheclasses:
∙Application:
lockswaitscausedbyrowlevellockingorexplicitlockcommands
∙Commit:
waitsforredologwriteconfirmationafteracommit
∙Idle:
waiteventsthatsignifythesessionisinactive,suchasSQL*Netmessagefromclient
∙Network:
waitsfordatatobesentoverthenetwork
∙UserI/O:
waitforblockstobereadoffadisk
SeeAlso:
OracleDatabaseReferenceformoreinformationaboutOraclewaitevents
TimeModelStatistics
WhentuninganOraclesystem,eachcomponenthasitsownsetofstatistics.Tolookatthesystemasawhole,itisnecessarytohaveacommonscaleforcomparisons.Becauseofthis,mostOracleadvisoriesandreportsdescribestatisticsintermsoftime.Inaddition,theV$SESS_TIME_MODELandV$SYS_TIME_MODELviewsprovidetimemodelstatistics.Usingthecommontimeinstrumentationhelpstoidentifyquantitativeeffectsonthedatabaseoperations.
ThemostimportantofthetimemodelstatisticsisDBtime.Thisstatisticsrepresentsthetotaltimespentindatabasecallsandisaindicatorofthetotalinstanceworkload.ItiscalculatedbyaggregatingtheCPUandwaittimesofallsessionsnotwaitingonidlewaitevents(non-idleusersessions).
DBtimeismeasuredcumulativelyfromthetimethattheinstancewasstarted.BecauseDBtimeitiscalculatedbycombiningthetimesfromallnon-idleusersessions,itispossiblethattheDBtimecanexceedtheactualtimeelapsedsincetheinstancestartedup.Forexample,ainstancethathasbeenrunningfor30minutescouldhavefouractiveusersessionswhosecumulativeDBtimeisapproximately120minutes.
TheobjectivefortuninganOraclesystemcouldbestatedasreducingthetimethatusersspendinperformingsomeactiononthedatabase,orsimplyreducingDBtime.Othertimemodelstatisticsprovidequantitativeeffects(intime)onspecificactions,suchaslogonoperationsandhardandsoftparses.
SeeAlso:
OracleDatabaseReferenceforinformationabouttheV$SESS_TIME_MODELandV$SYS_TIME_MODELviews
ActiveSessionHistory(ASH)
TheV$ACTIVE_SESSION_HISTORYviewprovidessampledsessionactivityintheinstance.ActivesessionsaresampledeverysecondandarestoredinacircularbufferinSGA.AnysessionthatisconnectedtothedatabaseandiswaitingforaneventthatdoesnotbelongtotheIdlewaitclassisconsideredasanactivesession.ThisincludesanysessionthatwasontheCPUatthetimeofsampling.
EachsessionsampleisasetofrowsandtheV$ACTIVE_SESSION_HISTORYviewreturnsonerowforeachactivesessionpersample,returningthelatestsessionsamplerowsfirst.BecausetheactivesessionsamplesarestoredinacircularbufferinSGA,thegreaterthesystemactivity,thesmallerthenumberofsecondsofsessionactivitythatcanbestoredinthecircularbuffer.ThismeansthatthedurationforwhichasessionsampleappearsintheV$view,orthenumberofsecondsofsessionactivitythatisdisplayedintheV$view,iscompletelydependentonthedatabaseactivity.
AspartoftheAutomaticWorkloadRepository(AWR)snapshots,thecontentofV$ACTIVE_SESSION_HISTORYisalsoflushedtodisk.BecausethecontentofthisV$viewcangetquitelargeduringheavysystemactivity,onlyaportionofthesessionsamplesiswrittentodisk.
Bycapturingonlyactivesessions,amanageablesetofdataisrepresentedwiththesizebeingdirectlyrelatedtotheworkbeingperformedratherthanthenumberofsessionsallowedonthesystem.UsingtheActiveSessionHistoryenablesyoutoexamineandperformdetailedanalysisonbothcurrentdataintheV$ACTIVE_SESSION_HISTORYviewandhistoricaldataintheDBA_HIST_ACTIVE_SESS_HISTORYview,oftenavoidingtheneedtoreplaytheworkloadtogatheradditionalperformancetracinginformation.ThedatapresentinASHcanberolleduponvariousdimensionsthatitcaptures,includingthefollowing:
∙SQLidentifierofSQLstatement
∙Objectnumber,filenumber,andblocknumber
∙Waiteventidentifierandparameters
∙Sessionidentifierandsessionserialnumber
∙Moduleandactionname
∙Clientidentifierofthesession
∙Servicehashidentifier
SeeAlso:
OracleDatabaseReferenceformoreinformationabouttheV$ACTIVE_SESSION_HISTORYview
SystemandSessionStatistics
AlargenumberofcumulativedatabasestatisticsareavailableonasystemandsessionlevelthroughtheV$SYSSTATandV$SESSTATviews.
SeeAlso:
OracleDatabaseReferenceforinformationabouttheV$SYSSTATandV$SESSTATviews
OperatingSystemStatistics
Operatingsystemstatisticsprovideinformationontheusageandperformanceofthemainhardwarecomponentsofthesystem,aswellastheperformanceoftheoperatingsystemitself.Thisinformationiscrucialfordetectingpotentialresourceexhaustion,suchasCPUcyclesandphysicalmemory,andfordetectingbadperformanceofperipherals,suchasdiskdrives.
Operatingsystemstatisticsareonlyanindicationofhowthehardwareandoperatingsystemareworking.Manysystemperformanceanalystsreacttoahardwareresourceshortagebyinstallingmorehardware.Thisisareactionaryresponsetoaseriesofsymptomsshownint
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Automatic Performance Statistics