15 copyright © 2004, oracle. all rights reserved. proactive maintenance
TRANSCRIPT
![Page 1: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/1.jpg)
15Copyright © 2004, Oracle. All rights reserved.
Proactive Maintenance
![Page 2: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/2.jpg)
15-2 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
![Page 3: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/3.jpg)
15-3 Copyright © 2004, Oracle. All rights reserved.
Server Generated Alerts
Serveralertsqueue
Metric exceeds
threshold
AWR
Enterprise Manager
Oracle instance
Oracle instance
![Page 4: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/4.jpg)
15-4 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
![Page 5: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/5.jpg)
15-5 Copyright © 2004, Oracle. All rights reserved.
Setting Thresholds
Enterprise Manager’s Manage Metrics property page provides access to threshold settings.
![Page 6: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/6.jpg)
15-6 Copyright © 2004, Oracle. All rights reserved.
Baseline Measurements
Baseline measurements provide threshold recommendations based on actual performance data.
![Page 7: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/7.jpg)
15-7 Copyright © 2004, Oracle. All rights reserved.
Using Baselines
To activate a stored baseline:
1. Click Copy Thresholds
From Baseline.
2. Select the appropriate threshold.
![Page 8: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/8.jpg)
15-8 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
![Page 9: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/9.jpg)
15-10 Copyright © 2004, Oracle. All rights reserved.
SQL Tuning and Access Advisors
DBA
Poorly performingSQL statement
How can I make it run
faster?
SQL TuningAdvisor
RestructuredSQL statement
Add indexes or materialized views
Rewrite the SQL statement
Correct setup flaws
![Page 10: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/10.jpg)
15-11 Copyright © 2004, Oracle. All rights reserved.
Memory Advisors
• Shared pool
• Database buffer cache
• Program Global Area (PGA)
![Page 11: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/11.jpg)
15-12 Copyright © 2004, Oracle. All rights reserved.
Segment Advisor
• Entire tablespace
• Individual schema objects
![Page 12: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/12.jpg)
15-13 Copyright © 2004, Oracle. All rights reserved.
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
SnapshotsMMONIn-memorystatistics
AWRSGA
60 minutes
![Page 13: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/13.jpg)
15-14 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)
![Page 14: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/14.jpg)
15-15 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
ADDM
AWR
EMADDM results
![Page 15: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/15.jpg)
15-16 Copyright © 2004, Oracle. All rights reserved.
ADDM Findings
![Page 16: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/16.jpg)
15-17 Copyright © 2004, Oracle. All rights reserved.
ADDM Recommendations
![Page 17: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/17.jpg)
15-18 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
![Page 18: 15 Copyright © 2004, Oracle. All rights reserved. Proactive Maintenance](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e395503460f94b2a98a/html5/thumbnails/18.jpg)
15-19 Copyright © 2004, Oracle. All rights reserved.
Practice 15: Proactive Maintenance
This practice covers configuring your database for proactive maintenance.