Oracle英文版培训课件之Performance Tuning：les-03Statistics and Wait Events

StatisticsandWaitEventsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:IdentifydynamicperformanceviewsusefulintuningIdentifythekeytuningcomponentsofthealertlogfileIdentifythekeytuningcomponentsofusertracefilesUsedynamicperformanceviewstoviewstatisticsandwaiteventsPerformanceTuningDataTypeofdatagathered:Cumulativestatistics:WaiteventswithtimeinformationTimemodelMetrics:StatisticratesSampledstatistics:ActivesessionhistoryStatisticsbysessionStatisticsbySQLStatisticsbyserviceOtherdimensionsPerformanceTuningToolsToolsthatareavailable:Basic:EnterpriseManagerpagesAlertlogTracefilesDynamicperformanceviewsandtablesAdd-in:StatspackOptions:DiagnosticspackTuningpackAccessingtheDatabaseHomePagehttp://hostname:1158/emEMPerformancePagesViewingtheAlertLogDatabaseHomepage>RelatedLinksregion>

AlertLogContentUsingAlertLogInformation

toAidinTuningThealertlogfilecontainsthefollowinginformation

thatcanbeusedtoaidintuningthedatabase:IncompletecheckpointsTimetoperformarchivingInstancerecoverystartandcompletetimesDeadlockandtimeouterrorsCheckpointstartandendtimesUserTraceFilesServer-processtracingcanbeenabledordisabledatthesessionorinstancelevel.AusertracefilecontainsstatisticsfortracedSQLstatementsinthatsession.Usertracefilesarecreatedonaperserverprocessbasis.Usertracefilescanalsobecreatedby:PerformingaBACKUP

CONTROL

FILE

TO

TRACEProcesserrorsBackgroundProcessTraceFilesTheOracledatabaseserverdumpsinformationabouterrorsdetectedbyanybackgroundprocess

intotracefiles.OracleSupportusesthesetracefilestodiagnose

andtroubleshoot.Thesefilesdonotusuallycontaintuninginformation.DynamicPerformanceViewsDynamicperformanceviewsprovideaccesstoinformationaboutchangingstatesandconditionsintheinstance.SessiondataWaiteventsMemoryallocationsRunningSQLUNDOusageOpencursorsRedologusageAndsoonOracleinstanceDynamicPerformanceViews:

UsageExamplesSQL>SELECTsql_text,executions2FROMv$sqlstats3WHEREcpu_time>200000;SQL>SELECT*FROMv$session2WHEREmachine='EDRSR9P1'and3logon_time>SYSDATE-1;SQL>SELECTsid,ctime2FROMv$lockWHEREblock>0;abcDynamicPerformanceViews:ConsiderationsTheseviewsareownedbySYS.Differentviewsareavailableatdifferenttimes:Theinstancehasbeenstarted.Thedatabaseismounted.Thedatabaseisopen.YoucanqueryV$FIXED_TABLEtoseealltheviewnames.Theseviewsareoftenreferredtoas“v-dollarviews.”Readconsistencyisnotguaranteedontheseviewsbecausethedataisdynamic.StatisticLevelsSTATISTICS_LEVELBASICTYPICALALLRecommended

defaultvalueAdditionalstatistics

formanual

SQLdiagnosticsDisableallself-tuning

capabilitiesV$STATISTICS_LEVELStatisticsandWaitEventsStatisticsWaiteventsReportsDynamicperformanceviewsSystemStatisticClassesCacheDebugRACSQLRedoUserEnqueueOSV$SYSSTATV$SESSTATV$STATNAMESystem

statistic

classesDisplayingSystemwideStatisticsV$SYSSTATSTATISTIC#NAMECLASSVALUEV$SGASTATPOOLNAMEBYTESV$EVENT_NAMEEVENTNUMBERNAMEPARAMETER1PARAMETER2PARAMETER3

V$SYSTEM_EVENTEVENTTOTAL_WAITSTOTAL_TIMEOUTSTIME_WAITEDAVERAGE_WAITDisplayingSession-RelatedStatisticsV$STATNAMESTATISTIC#NAMECLASSV$SESSTATSIDSTATISTIC#VALUEV$SESSION_WAITSIDSEQ#EVENTP1/2/3P1/2/3TEXTP1/2/3RAWWAITTIMESECONDS_IN_WAITSTATEV$SESSIONSIDSERIAL#USERNAMEOSUSERV$SESSION_EVENTSIDEVENTTOTAL_WAITSTOTAL_TIMEOUTSTIME_WAITEDAVERAGE_WAITMAX_WAITV$EVENT_NAMEEVENT#NAMEPARAMETER1PARAMETER2PARAMETER3DisplayingService-RelatedStatisticsV$SERVICE_STATSSERVICE_NAME_HASHSERVICE_NAMESTAT_IDSTAT_NAMEVALUEV$SESSION_WAIT_CLASSSERVICE_NAMESERVICE_NAME_HASHWAIT_CLASS_IDWAIT_CLASS#WAIT_CLASSTOTAL_WAITSTIME_WAITEDV$SERVICESSERVICE_IDNAMENAME_HASHNETWORK_NAMECREATION_DATEV$SERVICE_EVENTSERVICE_NAMESERVICE_NAME_HASHEVENTEVENT_IDTOTAL_WAITSTOTAL_TIMEOUTSTIME_WAITEDAVERAGE_WAITMAX_WAITTIME_WAITED_MICROWaitEventsAcollectionofwaiteventsprovidesinformation

aboutthesessionsthathadtowaitormustwaitfordifferentreasons.TheseeventsarelistedintheV$EVENT_NAMEview,whichhasthefollowingcolumns:EVENT#NAMEPARAMETER1PARAMETER2PARAMETER3CommonlyObservedWaitEventsOver-commit,I/OLogfilesyncLogbufferI/OLogbufferspaceLatchesLibrarycachewaitsLocksEnqueuewaits(enq:)I/O,SQLTuningDbfilescatteredread,DbfilesequentialreadBuffercache,DBWR,I/OFreebufferwaitsBuffercache,DBWRBufferbusywaitsAreaWaitEventWaitClassesSchedulerClusterConfigurationCommitApplicationAdministrativeNetworkUserI/OConcurrencySystemI/OOtherIdleBackground

processesI/OForeground

processesI/ODBA

commandsShouldbe

rareInadequatedatabase/

instanceconfigurationResourcemanagerRAC

resourcesUser

application

codeInactive

sessionsNetwork

messaginglogfilesyncInternaldatabaseresourcesV$SESSION_WAIT_CLASSV$SERVICE_WAIT_CLASSV$SYSTEM_WAIT_CLASSV$EVENT_NAMEWait

classesUsingtheV$EVENT_NAMEViewNAMEPARAMETER1PARAMETER2PARAMETER3

PL/SQLlocktimerdurationaltersystemsetmts_dispatcherwaitedbufferbusywaitsfile#block#idlibrarycachepinhandleaddrpinaddress0*mode+namelogbufferspacelogfileswitch(checkpointincomplete)transactionundoseg#wrap#count...286rowsselected.SQL>SELECTname,parameter1,parameter2,parameter32FROMv$event_name;WaitEventStatisticsTheseviewsholdsession-levelwaiteventstatistics:V$SESSION_EVENT:SessionwaitsbyeventforeachsessionthathadtowaitV$SESSION_WAIT:SessionwaitsbyeventforcurrentactivesessionsthatarewaitingV$SYSTEM_EVENT:Totalwaitsforanevent,allsessionstogetherUsingtheV$SESSION_EVENTView

SIDEVENTTOTAL_WAITSAVERAGE_WAIT

10bufferbusywaits12510dbfilesequentialread129010fileopen1010SQL*Netmessagetoclient77010SQL*Netmoredatatoclient2010SQL*Netmessagefromclient760SQL>SELECTsid,event,total_waits,average_wait2FROMv$session_event3WHEREsid=10;UsingtheV$SESSION_WAITViewSIDSEQ# EVENT WAIT STATE TIME --- 1 1284 pmontimer 0 WAITING 2 1697 rdbmsipcmessage 0 WAITING 3 183 rdbmsipcmessage 0 WAITING 4 4688 rdbmsipcmessage 0 WAITING 5 114 smontimer 0 WAITING 6 14 SQL*Netmessagefromclient -1 WAITED SHORT TIMESQL>SELECTsid,seq#,event,wait_time,state2FROMv$session_wait;UsingtheV$SYSTEM_EVENTViewEVENT TOTAL_ TOTAL_ TIME_ AVERAGE_ WAITS TIMEOUTS WAITED WAIT

latchfree 5 5 5 1pmontimer 932 535 254430 272.993562processstartup 3 8 2.66666667bufferbusywaits 12 0 5 5...34rowsselected.SQL>SELECTevent,total_waits,total_timeouts,2time_waited,average_wait3FROMv$system_event;

PrecisionofSystemStatisticsViewsthatincludemicrosecondtimings:V$SESSION_WAIT,V$SYSTEM_EVENT,V$SERVICE_EVENT,

V$SESSION_EVENT(TIME_WAITED_MICROcolumn)V$SQL,V$SQLAREA(CPU_TIME,ELAPSED_TIMEcolumns)V$LATCH,V$LATCH_PARENT,V$LATCH_CHILDREN(WAIT_TIMEcolumn)V$SQL_WORKAREA,V$SQL_WORKAREA_ACTIVE(ACTIVE_TIMEcolumn)Viewsthatincludemillisecondtimings:V$ENQUEUE_STAT(CUM_WAIT_TIMEcolumn)TimeModel:OverviewThetimemodelisasetofstatisticsthatgiveanoverviewofwheretimeisspentinsidetheOracledatabase.Allstatisticsusethesamedimension:time.Thestatisticsareaccessiblethrough:V$SYS_TIME_MODELV$SESS_TIME_MODELDBtimerepresentsthetotal

timespentindatabasecalls.TuninggoalistoreduceDBtime.UsingDBtime,youcangaugetheperformanceimpactofanyentityofthedatabase.SQLPLSQLConnectParseJavaDBtimeTimeModelStatisticsHierarchyFailedparse(outofsharedmemory)elapsedtimeDBtimeD