oracle学习11g性能调优官方PPTless_07

Copyright©2008,Oracle.Allrightsreserved.UsingAWR-BasedToolsCopyright©2008,Oracle.Allrightsreserved.7-2ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Describetuningautomaticmaintenancetasks•GenerateADDMreports•GenerateActiveSessionHistory(ASH)reportsCopyright©2008,Oracle.Allrightsreserved.7-3AutomaticMaintenanceTasksCopyright©2008,Oracle.Allrightsreserved.7-4MaintenanceWindows10PM–2AMMontoFri6AM–2AMSattoSunMaintenanceWindowGroupCopyright©2008,Oracle.Allrightsreserved.7-5DefaultMaintenancePlanSQLSELECTnameFROMV$RSRC_PLAN2WHEREis_top_plan='TRUE';NAME--------------------------------DEFAULT_MAINTENANCE_PLANCopyright©2008,Oracle.Allrightsreserved.7-6AutomatedMaintenanceTaskPrioritiesRunJob1withurgentpriority.RunJob2withurgentpriority.RunJob3withhighpriority.……MMONABPJobnJob1…DBA_AUTOTASK_TASKStatsSpaceSQLurgenthighmediumRunJob3withmediumpriority.RunJob4withmediumpriority.MaintenancewindowCopyright©2008,Oracle.Allrightsreserved.7-7TuningAutomaticMaintenanceTasksServerAutomaticMaintenanceTasksConfigureCopyright©2008,Oracle.Allrightsreserved.7-8ADDMPerformanceMonitoringSnapshotsADDMIn-memorystatisticsAWRSGA60minutesADDMresultsMMONCopyright©2008,Oracle.Allrightsreserved.7-9ADDMandDatabaseTimeWideareanetworkApplicationserverLocalareanetworkOracledatabaseUsersendsarequest.Usergetsaresponse.User1User2UsernConnectExecuteFetchFetchFetchExecuteDatabasetimeWall-clocktimeCopyright©2008,Oracle.Allrightsreserved.7-10DBTime-GraphandADDMMethodologyUserconnectRootnodeSQLoptimizationSQLexecutionUndersizedbuffercacheI/OcapacityDatabaselocksInsufficientI/ObandwidthCPUcapacityDimension1Dimension2SymptomsRootcausesCopyright©2008,Oracle.Allrightsreserved.7-12TopPerformanceIssuesDetectedExcessivelogon/logoffMemoryundersizingHotblocksandobjectsw/SQLRACserviceissuesLocksandITLcontentionCheckpointingcausesPL/SQL,JavatimeStreams,AQ,andRMANTopSQLI/OissuesParsingConfigurationissuesApplicationusageNotdetectedbyStatspackADDMidentifiestopissues.Copyright©2008,Oracle.Allrightsreserved.7-13DatabaseControlandADDMFindingsCopyright©2008,Oracle.Allrightsreserved.7-14ADDMAnalysisResults123Copyright©2008,Oracle.Allrightsreserved.7-15ADDMRecommendationsCopyright©2008,Oracle.Allrightsreserved.7-16DatabaseControlandADDMTaskCopyright©2008,Oracle.Allrightsreserved.7-17ChangingADDMAttributes1.EnsurethatSTATISTICS_LEVELissettoTYPICALorALL.2.ADDManalysisofI/OperformancedependsontheexpectedspeedoftheI/Osubsystem:a.MeasureyourI/Osubsystemspeed.b.Settheexpectedspeed.SQLexecDBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(-'ADDM','DBIO_EXPECTED',8000);SELECTparameter_value,is_defaultFROMdba_advisor_def_parametersWHEREadvisor_name='ADDM'ANDparameter_name='DBIO_EXPECTED';Copyright©2008,Oracle.Allrightsreserved.7-18RetrievingADDMReportsbyUsingSQLSELECTdbms_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...Copyright©2008,Oracle.Allrightsreserved.7-19ActiveSessionHistory:Overview•Storesthehistoryofdatabasetime•Samplessessionactivityinthesystemincluding:–SQLidentifierofaSQLstatement–Objectnumber,filenumber,andblocknumber–Waiteventidentifierandparameters–Sessionidentifierandsessionserialnumber–Moduleandactionname–Clientidentifierofthesession–Servicehashidentifier–Blockingsession•Isalwaysonforfirstfaultanalysis•NoneedtoreplaytheworkloadCopyright©2008,Oracle.Allrightsreserved.7-20ActiveSessionHistory:MechanicsSGAStatisticsV$SESSIONASHRecenthistoryRollingbufferWorkloadrepository1sec1sec1secEvery60minutesMMONMMNLWhen66%fullV$ACTIVE_SESSION_HISTORYWRH$_ACTIVE_SESSION_HISTORY(partitioned)NouseofSQLDirectpathinsertsDBA_HIST_ACTIVE_SESSION_HISTORY1outof10Viewersgounlatched.Copyright©2008,Oracle.Allrightsreserved.7-21ASHSampling:ExampleSession1WaitI/OWaitLockWaitBlockWaitI/OWaitI/OWaitLockWaitI/OWaitBlock……Sess1WaitI/OSess1WaitI/OSess1WaitBlockSession2activeSession3activeSessionnactive…ActiveInactivesessions1sec1secASH…V$ACTIVE_SESSION_HISTORYTimeCopyright©2008,Oracle.Allrightsreserved.7-22AccessingASHData•Dumptotracefile•V$ACTIVE_SESSION_HISTORY•DBA_HIST_ACTIVE_SESS_HISTORY•ASHreport•EMDiagnosticPackperformancepagesCopyright©2008,Oracle.Allrightsreserved.7-23DumpASHtoFileThegeneratedfilecontainsASHsamplesforthelastnumberofminutesspecified.SQLoradebugsetmypidSQLoradebugdumpashdump10ACTIVESESSIONHISTORY-PROCESSTRACEDUMPHEADERBEGINDBID,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_IDACTIVESESSIONHISTORY-PROCESSTRACEDUMPHEADERENDACTIVESESSIONHIS