1 / 28

Proactive Maintenance

Proactive Maintenance. Objectives. After completing this lesson, you should be able to do the following: Use statistics Manage the Automatic Workload Repository (AWR) Use the Automatic Database Diagnostic Monitor (ADDM) Describe the advisory framework Set alert thresholds

len
Download Presentation

Proactive Maintenance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Proactive Maintenance

  2. Objectives • After completing this lesson, you should be able to do the following: • Use statistics • Manage the Automatic Workload Repository (AWR) • Use the Automatic Database Diagnostic Monitor (ADDM) • Describe the advisory framework • Set alert thresholds • Use server-generated alerts • Use automated tasks

  3. Proactive Maintenance Automatedtasks Automatic Proactive Serveralerts Advisoryframework AutomaticWorkload Repository Efficient Data warehouseof the database Direct memoryaccess Automatic collection of important statistics

  4. Introducing Terminology • Automatic Workload Repository (AWR): Infrastructure for data gathering, analysis, and solutions recommendations • Baseline: Data gathered of a “normal running database” for performance comparison • Metric: Rate of change in a cumulative statistic • Statistics: Data collections used for optimizing internal operations, such as execution of a SQL statement • Threshold: A boundary value against which metric values are compared

  5. > Statistics AWR ADDM Advisors Alerts AutoTasks Optimizer Statistics • Optimizer statistics are: • Not real time • Persistent across instance restarts • Collected automatically SQL> SELECT COUNT(*) FROM hr.employees; COUNT(*) ---------- 214 SQL> SELECT num_rows FROM dba_tables 2WHERE owner='HR'ANDtable_name='EMPLOYEES'; NUM_ROWS ---------- 107

  6. Using the Manage Optimizer Statistics Page

  7. Statistic Levels STATISTICS_LEVEL BASIC TYPICAL ALL Self-tuningcapabilities disabled Recommendeddefault value Additional statisticsfor manualSQL diagnostics

  8. In-memorystatistics MMON 60 minutes Snapshots SGA AWR Statistics > AWR ADDM Advisors Alerts AutoTasks Automatic Workload Repository.(AWR) • Built-in repository of performance information • Snapshots of database metrics taken every 60 minutes and retained for 7 days • Foundation for all self-management functions

  9. AWR Infrastructure External clients EM SQL*Plus … SGA V$ DBA_* Efficientin-memorystatisticscollection AWRsnapshots MMON Self-tuningcomponent Self-tuningcomponent ADDM … Internal clients

  10. AWR Snapshot Sets Relevant periodin the past DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( - start_snap_id IN NUMBER , end_snap_id IN NUMBER , baseline_name IN VARCHAR2);

  11. Enterprise Manager and AWR

  12. Managing the AWR • Retention period • The default is 7 days • Consider storage needs • Collection interval • The default is 60 minutes • Consider storage needs and performance impact • Collection level • Basic (disables most of ADDM functionality) • Typical (recommended) • All (adds additional SQL tuning information to snapshots)

  13. Snapshots Statistics AWR > ADDM Advisors Alerts AutoTasks Automatic Database Diagnostic.Monitor (ADDM) • Runs after each AWR snapshot • Monitors the instance; detects bottlenecks • Stores results within the AWR EM ADDM ADDM results AWR

  14. ADDM Findings 1 2 3

  15. ADDM Recommendations

  16. Statistics AWR ADDM > Advisors Alerts AutoTasks Advisory Framework PGA Advisor PGA SQL Tuning Advisor Buffer CacheAdvisor Memory SGA ADDM SQL Access Advisor Library CacheAdvisor Segment Advisor Space Undo Advisor MTTR Advisor Backup

  17. Enterprise Manager and Advisors

  18. The DBMS_ADVISOR Package

  19. Oracle instance Statistics AWR ADDM Advisors > Alerts AutoTasks Server-Generated Alerts Enterprise Manager Serveralertsqueue. Metric exceeds threshold. AWR

  20. Default Server-Generated Alerts 97% Critical 85% Warning Tablespace TablespaceSpace Usage Database Control:SYSTEM metrics ResumableSessionSuspended Recovery Area Low On Free Space SnapshotToo Old

  21. Setting Thresholds

  22. Creating and Testing an Alert • Specify a threshold. • Create a test case. • Check for an alert. 1 2 3

  23. Alerts Notification

  24. Reacting to Alerts • If needed, gather more input, for example, by running ADDM or another advisor. • Take corrective measures. • Acknowledge alerts, which are not automatically cleared.

  25. Alert Types and Clearing Alerts Metric based 97% Critical Cleared Threshold(stateful)alerts 85% Warning Cleared MMON DBA_OUTSTANDING_ALERTS DBA_ALERT_HISTORY Recovery Area Low On Free Space ResumableSessionSuspended SnapshotToo Old Nonthreshold (stateless)alerts Alert Event based

  26. Statistics AWR ADDM Advisors Alerts > AutoTasks Automated Maintenance Tasks . • Scheduler initiates jobs • Jobs run in the default maintenance window • Limit maintenance impact on normal operation by using Resource Manager • Examples of maintenance: • Gathering optimizer statistics • Gathering segment information • Backing up database

  27. Summary • In this lesson, you should have learned how to: • Use statistics • Manage the Automatic Workload Repository • Use the Automatic Database Diagnostic Monitor • Describe the advisory framework • Set alert thresholds • Use server-generated alerts • Use automated tasks

  28. Practice Overview: Proactive Maintenance • This practice covers the following topics: • Proactively managing your database by using ADDM • Setting up an issue for analysis • Reviewing your database performance • Implementing a solution

More Related