Proactive maintenance
Download
1 / 28

Proactive Maintenance - PowerPoint PPT Presentation


  • 122 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Proactive Maintenance' - len


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Objectives
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


Proactive maintenance1
Proactive Maintenance

Automatedtasks

Automatic

Proactive

Serveralerts

Advisoryframework

AutomaticWorkload Repository

Efficient

Data warehouseof the database

Direct memoryaccess

Automatic collection of important statistics


Introducing terminology
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


Optimizer statistics

> 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



Statistic levels
Statistic Levels

STATISTICS_LEVEL

BASIC

TYPICAL

ALL

Self-tuningcapabilities disabled

Recommendeddefault value

Additional statisticsfor manualSQL diagnostics


Automatic workload repository awr

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


Awr infrastructure
AWR Infrastructure

External clients

EM

SQL*Plus

SGA

V$

DBA_*

Efficientin-memorystatisticscollection

AWRsnapshots

MMON

Self-tuningcomponent

Self-tuningcomponent

ADDM

Internal clients


Awr snapshot sets
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);



Managing the awr
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)


Automatic database diagnostic monitor addm

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




Advisory framework

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



The dbms advisor package
The DBMS_ADVISOR Package


Server generated alerts

Oracle

instance

Statistics

AWR

ADDM

Advisors

> Alerts

AutoTasks

Server-Generated Alerts

Enterprise Manager

Serveralertsqueue.

Metric exceeds

threshold.

AWR


Default server generated alerts
Default Server-Generated Alerts

97% Critical

85% Warning

Tablespace

TablespaceSpace Usage

Database Control:SYSTEM metrics

ResumableSessionSuspended

Recovery Area Low On Free Space

SnapshotToo Old



Creating and testing an alert
Creating and Testing an Alert

  • Specify a threshold.

  • Create a test case.

  • Check for an alert.

1

2

3



Reacting to alerts
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.


Alert types and clearing alerts
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


Automated maintenance tasks

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


  • Summary
    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


    Practice overview proactive maintenance
    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


    ad