Proactive maintenance
This presentation is the property of its rightful owner.
Sponsored Links
1 / 28

Proactive Maintenance PowerPoint PPT Presentation


  • 80 Views
  • Uploaded on
  • Presentation posted in: General

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

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.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


Proactive maintenance

Proactive Maintenance


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


Using the manage optimizer statistics page

Using the Manage Optimizer Statistics Page


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);


Enterprise manager and awr

Enterprise Manager and AWR


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


Addm findings

ADDM Findings

1

2

3


Addm recommendations

ADDM Recommendations


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


Enterprise manager and advisors

Enterprise Manager and Advisors


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


Setting thresholds

Setting Thresholds


Creating and testing an alert

Creating and Testing an Alert

  • Specify a threshold.

  • Create a test case.

  • Check for an alert.

1

2

3


Alerts notification

Alerts Notification


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


  • Login