Transcript 15 - Elte
Proactive Maintenance Copyright © 2004, Oracle. All rights reserved. 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 15-2 Copyright © 2004, Oracle. All rights reserved. Server Generated Alerts Enterprise Manager Server alerts queue Oracle instance Metric exceeds threshold AWR 15-3 Copyright © 2004, Oracle. All rights reserved. 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 ALERT 15-4 Copyright © 2004, Oracle. All rights reserved. Setting Thresholds Enterprise Manager’s Manage Metrics property page provides access to threshold settings. 15-5 Copyright © 2004, Oracle. All rights reserved. Baseline Measurements Baseline measurements provide threshold recommendations based on actual performance data. 15-6 Copyright © 2004, Oracle. All rights reserved. Using Baselines To activate a stored baseline: 1. Click Copy Thresholds From Baseline. 2. Select the appropriate threshold. 15-7 Copyright © 2004, Oracle. All rights reserved. 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 15-8 Copyright © 2004, Oracle. All rights reserved. SQL Tuning and Access Advisors How can I make it run faster? DBA Poorly performing SQL statement Restructured SQL statement Add indexes or materialized views Rewrite the SQL statement SQL Tuning Advisor 15-10 Correct setup flaws Copyright © 2004, Oracle. All rights reserved. Memory Advisors • • • 15-11 Shared pool Database buffer cache Program Global Area (PGA) Copyright © 2004, Oracle. All rights reserved. Segment Advisor • Entire tablespace • Individual schema objects 15-12 Copyright © 2004, Oracle. All rights reserved. Automatic Workload Repository (AWR) In-memory statistics 60 minutes MMON Snapshots SGA AWR • • • 15-13 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 Copyright © 2004, Oracle. All rights reserved. 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) 15-14 Copyright © 2004, Oracle. All rights reserved. Automatic Database Diagnostic Monitor (ADDM) • • • Runs after each AWR snapshot Monitors the instance and detects bottlenecks Results stored within the AWR Snapshots EM ADDM ADDM results AWR 15-15 Copyright © 2004, Oracle. All rights reserved. ADDM Findings 15-16 Copyright © 2004, Oracle. All rights reserved. ADDM Recommendations 15-17 Copyright © 2004, Oracle. All rights reserved. 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 15-18 Copyright © 2004, Oracle. All rights reserved. Practice 15: Proactive Maintenance This practice covers configuring your database for proactive maintenance. 15-19 Copyright © 2004, Oracle. All rights reserved.