les-06使用自动工作量知识(40)课件

1、Using Automatic Workload Repository第1页，共42页。ObjectivesAfter completing this lesson, you should be able to do the following:Create and manage Automatic Workload Repository (AWR) snapshotsGenerate AWR reportsCreate snapshot sets and compare periodsGenerate ADDM reportsGenerate ASH reports第2页，共42页。Auto

2、matic Workload Repository: OverviewSGAV$DBA_*ADDMSelf-tuningcomponentSelf-tuningcomponentInternal clientsExternal clientsEMSQL*PlusEfficientin-memorystatisticscollectionAWRsnapshotsMMON第3页，共42页。Automatic Workload Repository DataBase statistics:SQL and optimizerstatisticsOS statisticsWait classesTime

3、 statisticsMetricsActive Session HistoryAdvisor resultsSnapshot statisticsDatabase feature usageV$SYSSTATV$SQLV$SEGMENT_STATISTICSV$SYS_TIME_MODELV$SYSMETRIC_HISTORYV$SYSTEM_WAIT_CLASSV$OSSTATV$ACTIVE_SESSION_HISTORYDBA_ADVISOR_*DBA_HIST_*DBA_FEATURE_*DBA_HIGH_WATER_MARK_*DBA_TAB_STATS_HISTORY第4页，共4

4、2页。Workload RepositorySYSAUXSGAIn-memorystatistics6:00 a.m.7:00 a.m.8:00 a.m.Snapshot 1Snapshot 2Snapshot 3Snapshot 49:00 a.m.9:30 a.m.ADDM finds top problems.MMON第5页，共42页。AWR Snapshot SetsDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER , end_snap_id IN NUMBER , baseline_name IN

5、 VARCHAR2);Relevant periodin the past第6页，共42页。AWR Snapshot Purging PolicySYSAUX tablespacesys schemaSnapshotSnapshotSnapshotSnapshot7daysSnapshotMMONEvery night60 min第7页，共42页。AWR Snapshot SettingsDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( - retention IN NUMBER DEFAULT NULL, interval IN NUMB

6、ER DEFAULT NULL, topnsql IN NUMBER DEFAULT NULL);第8页，共42页。Database Control and AWR第9页，共42页。Generating AWR Reports in EM第10页，共42页。Generating AWR Reports in SQL*Plus第11页，共42页。Snapshot Sets and Period Comparisons第12页，共42页。Compare Periods: BenefitsDBADBA第13页，共42页。Compare Periods: General第14页，共42页。Compar

7、e Periods: Report第15页，共42页。AWR DataAdvisorresultsAWRSQLstatisticsBasestatisticsMetricsASH第16页，共42页。DBMS_WORKLOAD_REPOSITORY PackageProcedure to modify the snapshot settingsMODIFY_SNAPSHOT_SETTINGSProcedure to drop a single AWR baselineDROP_BASELINEProcedure to create a single AWR baselineCREATE_BASE

8、LINEProcedure to drop a range of snapshotsDROP_SNAPSHOT_RANGEProcedure to create a manual snapshot immediatelyCREATE_SNAPSHOTDescriptionProcedure Name第17页，共42页。ADDM Performance MonitoringSnapshotsADDMIn-memorystatisticsAWRSGA60 minutesADDM resultsMMON第18页，共42页。ADDM and Database TimeWide area network

9、Application serverLocal area networkOracle databaseUser sendsa request.User getsa response.User 1User 2User nConnectExecuteFetchFetchFetchExecuteDatabase timeWall-clock time第19页，共42页。DBTime-graph and ADDM MethodologyUserconnectRoot nodeSQLoptimizationSQLexecutionUndersizedbuffer cacheI/OcapacityData

10、baselocksInsufficient I/ObandwidthCPUcapacityDimension 1Dimension 2SymptomsRoot causes第20页，共42页。Notes only slide第21页，共42页。Top Performance Issues DetectedExcessive logon/logoffMemory undersizingHot blocks and objects w/SQLRAC service issuesLocks and ITL contentionCheckpointing causesPL/SQL, Java time

11、Streams, AQ, and RMANTop SQLI/O issuesParsingConfiguration issuesApplication usageNot detectedby StatspackADDM identifiestop issues.第22页，共42页。Database Control and ADDM Findings第23页，共42页。ADDM Analysis Results123第24页，共42页。ADDM Recommendations第25页，共42页。Database Control and ADDM Task 第26页，共42页。Changing

12、ADDM Attributes1.Ensure that STATISTICS_LEVEL is set to TYPICAL or ALL.2.ADDM analysis of I/O performance depends on the expected speed of the I/O subsystem:a.Measure your I/O subsystem speed.b.Set the expected speed.SQL exec DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(- ADDM, DBIO_EXPECTED, 8000);SELEC

13、T parameter_value, is_defaultFROM dba_advisor_def_parametersWHERE advisor_name = ADDM AND parameter_name = DBIO_EXPECTED;第27页，共42页。Retrieving ADDM Reports by Using SQLSELECT dbms_advisor.GET_TASK_REPORT(task_name)FROM dba_advisor_tasksWHERE task_id = ( SELECT max(t.task_id) FROM dba_advisor_tasks t,

14、 dba_advisor_log l WHERE t.task_id = l.task_id AND t.advisor_name = ADDM AND l.status = COMPLETED);SQL ?/rdbms/admin/addmrptEnter value for begin_snap: 8Enter value for end_snap: 10Enter value for report_name: Generating the ADDM report for this analysis .第28页，共42页。Active Session History: OverviewSt

15、ores the history of database timeSamples session activity in the system including:SQL identifier of a SQL statement Object number, file number, and block number Wait event identifier and parameters Session identifier and session serial number Module and action name Client identifier of the session S

16、ervice hash identifierBlocking sessionIs always on for first fault analysisNo need to replay the workload第29页，共42页。Active Session History: MechanicsSGAStatisticsV$SESSIONASHRecent historyRollingbufferWorkload repository1sec1sec1secEvery60 minutesMMONMMNLWhen 66% fullV$ACTIVE_SESSION_HISTORYWRH$_ACTI

17、VE_SESSION_HISTORY (partitioned)No useof SQLDirect pathinsertsDBA_HIST_ACTIVE_SESSION_HISTORY1 outof 10Viewers gounlatched.第30页，共42页。ASH Sampling: ExampleSession1Wait I/OWait LockWait BlockWait I/OWait I/OWait LockWait I/OWait BlockSess1 Wait I/OSess1 Wait I/OSess1 Wait BlockSession2activeSession3ac

18、tiveSessionnactiveActiveInactivesessions1sec1secASHV$ACTIVE_SESSION_HISTORYTime第31页，共42页。Accessing ASH DataDump to trace fileV$ACTIVE_SESSION_HISTORYDBA_HIST_ACTIVE_SESS_HISTORYASH reportEM Diagnostic Pack performance pagesADDM第32页，共42页。Dump ASH to FileThe generated file contains ASH samples for the

19、 last number of minutes specified:SQL oradebug setmypidSQL oradebug dump ashdump 10DBID, 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

20、_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_ID1090770270,1,1317127,08-26-2005 01:11:40.505471000, 162, 1, 0, , 0, 0, 165959219, 2, 0, 4294967295, 0,0,0,512

21、9,1,11242,86156091,1664,0,0,0,0,64620,0,oracleedrsr14p1 (CJQ0),1090770270,1,1317116,08-26-2005 01: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,emagentedrsr14p1 (TNS V1-V3),emagentedr

22、sr14p1 (TNS V1-V3),第33页，共42页。Sifting the ASHesGROUP BY and COUNTProxy for nonidle elapsed timeProportions of actual time spentCan analyze any time sliceExample: Returns most active SQL in the past minuteSELECT sql_id, count(*), round(count(*)/sum(count(*) over (), 2) pctloadFROM v$active_session_his

23、toryWHERE sample_time sysdate -1/24/60 and session_type BACKGROUNDGROUP BY sql_idORDER BY count(*) desc;第34页，共42页。Generating ASH Reports第35页，共42页。ASH Report ScriptSQL define dbid = ;SQL define inst_num = ;SQL define report_type = html;SQL define begin_time = 09:00;SQL define duration = 480;SQL define report_name = /tmp/sql_ashrpt.txt;SQL define slot_width = ;SQL define target_session_id = ;SQL define target_sq