210 likes | 382 Views
Proactive Maintenance. Objectives. After completing this lesson you should be able to do the following: Set warning and critical alert thresholds Collect and use baseline metrics Use tuning and diagnostic advisors Use the Automatic Database Diagnostic Monitor (ADDM)
E N D
Objectives • After completing this lesson you should be able to do the following: • Set warning and critical alert thresholds • Collect and use baseline metrics • Use tuning and diagnostic advisors • Use the Automatic Database Diagnostic Monitor (ADDM) • Manage the Automatic Workload Repository
Server Generated Alerts Enterprise Manager Serveralertsqueue Oracle instance Metric exceeds threshold AWR
ALERT Thresholds • Each metric can be assigned two thresholds: • Warning • Critical • When thresholds are reached, alerts are triggered by: • Notifications that appear in the Alerts region of the Database Control home page • Optional e-mail alerts
Setting Thresholds • Enterprise Manager’s Manage Metrics property page provides access to threshold settings.
Baseline Measurements • Baseline measurements provide threshold recommendations based on actual performance data.
Using Baselines • To activate a stored baseline: 1. Click Copy Thresholds From Baseline. 2. Select the appropriate threshold.
Tuning and Diagnostic Advisors • Oracle Database 10g provides several tuning and diagnostic advisors: • Automatic Database Diagnostic Monitor (ADDM) • SQL Tuning and Access Advisors • Memory Advisor • Mean-Time-To-Recover (MTTR) Advisor • Segment Advisor • Undo Management Advisor
SQL Tuning and Access Advisors How can I make it run faster? DBA Poorly performingSQL statement RestructuredSQL statement SQL TuningAdvisor
Memory Advisors • Shared pool • Database buffer cache • Program Global Area (PGA)
Segment Advisor • Entire tablespace • Individual schema objects
MMON Automatic Workload Repository (AWR) • Built-in repository of performance information • Snapshots of database metrics are taken every 60 minutes and retained for 7 days • Foundation for all self-management functions In-memorystatistics 60 minutes Snapshots SGA AWR
Managing the AWR • Retention period • Default 7 days • Consider storage needs • Collection interval • Default 30 minutes • Consider storage needs, performance impact • Collection level • Basic (disables most of ADDM functionality) • Typical (recommended) • All (adds additional SQL tuning information to snapshots)
Snapshots Automatic Database Diagnostic Monitor (ADDM) • Runs after each AWR snapshot • Monitors the instance and detects bottlenecks • Results stored within the AWR EM ADDM ADDM results AWR
Summary • In this lesson you should have learned how to: • Set warning and critical alert thresholds • Collect and use baseline metrics • Use tuning and diagnostic advisors • Use the Automatic Database Diagnostic Monitor (ADDM) • Manage the Automatic Workload Repository
Practice 15: Proactive Maintenance • This practice covers configuring your database for proactive maintenance.