oracle 10g performance tuning 06.pptVIP

Using Automatic Workload Repository Objectives After completing this lesson, you should be able to do the following: Create and manage Automatic Workload Repository (AWR) snapshots Generate AWR reports Create snapshot sets and compare periods Generate ADDM reports Generate ASH reports Automatic Workload Repository: Overview Automatic Workload Repository Data Base statistics: SQL and optimizerstatistics OS statistics Wait classes Time statistics Metrics Active Session History Advisor results Snapshot statistics Database feature usage Workload Repository AWR Snapshot Sets AWR Snapshot Purging Policy AWR Snapshot Settings Database Control and AWR Generating AWR Reports in EM Generating AWR Reports in SQL*Plus Snapshot Sets and Period Comparisons Compare Periods: Benefits Compare Periods: General Compare Periods: Report AWR Data DBMS_WORKLOAD_REPOSITORY Package ADDM Performance Monitoring ADDM and Database Time DBTime-graph and ADDM Methodology Top Performance Issues Detected Database Control and ADDM Findings ADDM Analysis Results ADDM Recommendations Database Control and ADDM Task Changing ADDM Attributes 1. 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. Retrieving ADDM Reports by Using SQL Active Session History: Overview Stores the history of database time Samples 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 Service hash identifier Blocking session Is always on for first fault analysis No need to replay the workload Active Session History: Mechanics ASH Sampling: Example Accessing ASH Data Dump to trace file V$ACTIVE_SESSION_HISTORY DBA_HIST_ACTIVE_SESS_HISTORY ASH report EM Diagnostic Pack