Oracle英文版培訓課件之Performance Tuning：les-06Using Automatic Workload Repository

UsingAutomaticWorkloadRepositoryObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:CreateandmanageAutomaticWorkloadRepository(AWR)snapshotsGenerateAWRreportsCreatesnapshotsetsandcompareperiodsGenerateADDMreportsGenerateASHreportsAutomaticWorkloadRepository:OverviewSGAV$DBA_*ADDMSelf-tuning

componentSelf-tuning

component…InternalclientsExternalclientsEMSQL*Plus…Efficient

in-memory

statistics

collectionAWR

snapshotsMMONAutomaticWorkloadRepositoryDataBasestatistics:SQLandoptimizer

statisticsOSstatisticsWaitclassesTimestatisticsMetricsActiveSessionHistoryAdvisorresultsSnapshotstatisticsDatabasefeatureusageV$SYSSTATV$SQLV$SEGMENT_STATISTICSV$SYS_TIME_MODELV$SYSMETRIC_HISTORY

V$SYSTEM_WAIT_CLASSV$OSSTATV$ACTIVE_SESSION_HISTORYDBA_ADVISOR_*DBA_HIST_*DBA_FEATURE_*

DBA_HIGH_WATER_MARK_*DBA_TAB_STATS_HISTORYWorkloadRepositorySYSAUXSGAIn-memory

statistics6:00a.m.7:00a.m.8:00a.m.Snapshot1Snapshot2Snapshot3Snapshot49:00a.m.9:30a.m.ADDMfinds

topproblems.MMONAWRSnapshotSetsDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(-

start_snap_idINNUMBER,

end_snap_idINNUMBER,baseline_nameINVARCHAR2);Relevantperiod

inthepastAWRSnapshotPurgingPolicySYSAUXtablespacesysschemaSnapshotSnapshotSnapshotSnapshot7

daysSnapshotMMONEverynight60minAWRSnapshotSettingsDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(-

retentionINNUMBERDEFAULTNULL,

intervalINNUMBERDEFAULTNULL,

topnsqlINNUMBERDEFAULTNULL);DatabaseControlandAWRGeneratingAWRReportsinEMGeneratingAWRReportsinSQL*PlusSnapshotSetsandPeriodComparisonsComparePeriods:BenefitsDBA≠DBA≠ComparePeriods:GeneralComparePeriods:ReportAWRDataAdvisor

resultsAWRSQL

statisticsBase

statisticsMetricsASHDBMS_WORKLOAD_REPOSITORYPackageProceduretomodifythesnapshotsettingsMODIFY_SNAPSHOT_SETTINGSProceduretodropasingleAWRbaselineDROP_BASELINEProceduretocreateasingleAWRbaselineCREATE_BASELINEProceduretodroparangeofsnapshotsDROP_SNAPSHOT_RANGEProceduretocreateamanualsnapshotimmediatelyCREATE_SNAPSHOTDescriptionProcedureNameADDMPerformanceMonitoringSnapshotsADDMIn-memory

statisticsAWRSGA60minutesADDMresultsMMONADDMandDatabaseTimeWideareanetworkApplicationserverLocalareanetworkOracledatabaseUsersends

arequest.Usergets

aresponse.User1User2UsernConnectExecuteFetchFetchFetchExecuteDatabasetimeWall-clocktimeDBTime-graphandADDMMethodologyUser

connectRootnodeSQL

optimizationSQL

executionUndersized

buffercacheI/O

capacityDatabase

locksInsufficientI/O

bandwidthCPU

capacityDimension1Dimension2SymptomsRootcausesNotesonlyslideTopPerformanceIssuesDetectedExcessivelogon/logoffMemoryundersizingHotblocksandobjectsw/SQLRACserviceissuesLocksandITLcontentionCheckpointingcausesPL/SQL,JavatimeStreams,AQ,andRMANTopSQLI/OissuesParsingConfigurationissuesApplicationusageNotdetected

byStatspackADDM

identifies

topissues.DatabaseControlandADDMFindingsADDMAnalysisResults123ADDMRecommendationsDatabaseControlandADDMTaskChangingADDMAttributes1. EnsurethatSTATISTICS_LEVELissettoTYPICALorALL.2. ADDManalysisofI/OperformancedependsontheexpectedspeedoftheI/Osubsystem:a. MeasureyourI/Osubsystemspeed.b. Settheexpectedspeed.SQL>execDBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(-'ADDM','DBIO_EXPECTED',8000);SELECTparameter_value,is_defaultFROMdba_advisor_def_parametersWHEREadvisor_name='ADDM'ANDparameter_name='DBIO_EXPECTED';RetrievingADDMReportsbyUsingSQLSELECTdbms_advisor.GET_TASK_REPORT(task_name)FROMdba_advisor_tasksWHEREtask_id=(SELECTmax(t.task_id)FROMdba_advisor_taskst,dba_advisor_loglWHEREt.task_id=l.task_idANDt.advisor_name='ADDM'ANDl.status='COMPLETED');SQL>@?/rdbms/admin/addmrpt…Entervalueforbegin_snap:8Entervalueforend_snap:10…Entervalueforreport_name:GeneratingtheADDMreportforthisanalysis...ActiveSessionHistory:OverviewStoresthehistoryofdatabasetimeSamplessessionactivityinthesystemincluding:SQLidentifierofaSQLstatementObjectnumber,filenumber,andblocknumberWaiteventidentifierandparametersSessionidentifierandsessionserialnumberModuleandactionnameClientidentifierofthesessionServicehashidentifierBlockingsessionIsalwaysonforfirstfaultanalysisNoneedtoreplaytheworkloadActiveSessionHistory:MechanicsSGAStatisticsV$SESSIONASHRecenthistoryRolling

bufferWorkloadrepository1sec1sec1secEvery

60minutesMMONMMNLWhen66%fullV$ACTIVE_SESSION_HISTORYWRH$_ACTIVE_SESSION_HISTORY(partitioned)Nouse

ofSQLDirectpath

insertsDBA_HIST_ACTIVE_SESSION_HISTORY1out

of10Viewersgo

unlatched.ASHSampling:ExampleSession1WaitI/OWaitLockWaitBlockWaitI/OWaitI/OWaitLockWaitI/OWaitBlock……Sess1WaitI/OSess1WaitI/OSess1WaitBlockSession2

activeSession3

activeSessionn

active…ActiveInactive

sessions1sec1secASH…V$ACTIVE_SESSION_HISTORYTimeAccessingASHDataDumptotracefileV$ACTIVE_SESSION_HISTORYDBA_HIST_ACTIVE_SESS_HISTORYASHreportEMDiagnosticPackperformancepagesADDMDumpASHtoFileThegeneratedfilecontainsASHsamplesforthelastnumberofminutesspecified:SQL>oradebugsetmypidSQL>oradebugdumpashdump10<<<ACTIVESESSIONHISTORY-PROCESSTRACEDUMPHEADERBEGIN>>>DBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME,SESSION_ID,SESSION_SERIAL#,USER_ID,SQL_ID,SQL_CHILD_NUMBER,SQL_PLAN_HASH_VALUE,SERVICE_HASH,SESSION_TYPE,SQL_OPCODE,BLOCKING_SESSION,BLOCKING_SESSION_SERIAL#QC_SESSION_ID,QC_INSTANCE_ID,XID,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,EVENT_ID,SEQ#,P1,P2,P3,WAIT_TIME,TIME_WAITED,FORCE_MATCHING_SIGNATURE,PROGRAM,MODULE,ACTION,CLIENT_ID<<<ACTIVESESSIONHISTORY-PROCESSTRACEDUMPHEADEREND>>><<<ACTIVESESSIONHISTORY-PROCESSTRACEDUMPBEGIN>>>1090770270,1,1317127,"08-26-200501:11:40.505471000",162,1,0,"",0,0,165959219,2,0,4294967295,0,0,0,,5129,1,11242,86156091,1664,0,0,0,0,64620,0,"oracle@edrsr14p1(CJQ0)","","","",1090770270,1,1317116,"08-26-200501:11:29.505471000",142,20,24,"0hbv80w9ypy0n",0,3304045827,3427055676,1,3,4294967291,0,0,0,,8751,3,2486,1421975091,24975,1413697536,1,0,620,0,17258348159868772889,"emagent@edrsr14p1(TNSV1-V3)","emagent@edrsr14p1(TNSV1-V3)","",""…<<<ACTIVESESSIONHISTORY-PROCESSTRACEDUMPEND>>>SiftingtheASHes

GROUP

BYandCOUNTProxyfornonidleelapsedtimeProportionsofactualtimespentCananalyzeanytimesliceExample:ReturnsmostactiveSQLinthepastminuteSELECTsql_id,count(*),

round(count(*)/sum(count(*))over(),2)pctloadFROMv$active_session_historyWHEREsample_time>sysdate-1/24/60andsession_type<>'BACKGROUND'GROUPBYsql_idORDERBYcount(*)desc;GeneratingASHReportsASHReportScriptSQL>definedbid='';SQL>defineinst_num='';SQL>definereport_type='html';SQL>definebegin_time='09:00';SQL>defineduration=480;SQL>definereport_name='/tmp/sql_ashrpt.txt';SQL>defineslot_width