Proactive maintenance
Download
1 / 35

Proactive Maintenance - PowerPoint PPT Presentation


  • 57 Views
  • Uploaded on

Proactive Maintenance. Objectives. After completing this lesson, you should be able to do the following: Set warning and critical alert thresholds Use server-generated alerts Collect and use baseline metrics Manage the Automatic Workload Repository (AWR)

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' - forest


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:

    • Set warning and critical alert thresholds

    • Use server-generated alerts

    • Collect and use baseline metrics

    • Manage the Automatic Workload Repository (AWR)

    • Use the Automatic Database Diagnostic Monitor

    • Describe the advisory framework


Proactive maintenance1
Proactive Maintenance

Automatedtasks

Automatic

Proactive

Serveralerts

Advisoryframework

AutomaticWorkload Repository

Efficient

Data warehouseof the database

Direct memoryaccess

Automatic collection of important statistics


Introduction of concepts and related tasks
Introduction of Concepts and Related Tasks

> Baseline

Alert

AWR

ADDM

Advisors

AutoTasks

  • Thresholds and metric baselines

  • Enabling metric baselining

  • Server-generated alerts

  • Creating and testing an alert

  • Automatic Workload Repository (AWR)

  • Managing the AWR

  • Automatic Database Diagnostic Monitor (ADDM)

  • Advisory framework

  • Automated maintenance tasks


Introducing terminology
Introducing Terminology

  • Baseline: Data gathered of a “normal running database” for performance comparison

  • Metric: Rate of change in a cumulative statistic

  • Threshold: A boundary value against which metric values are compared


Cyclic nature of workloads

End of day 3

End of day 2

End of day 1

Value

Metric

OLTP

DW

Time

Cyclic Nature of Workloads


Overview of thresholds and metric baselines fixed thresholds

Fixedthresholds

End of day 3

End of day 2

End of day 1

Value

Critical

Warning

Metric

OLTP

DW

Time

Deviations missed

Overview of Thresholds and Metric Baselines: Fixed Thresholds


Overview of thresholds and metric baselines adaptive thresholds

Adaptive thresholds

End ofbaseline

Critical

Value

Normal

Metric

Warning

Grouping

Time

Deviations captured

Overview of Thresholds and Metric Baselines: Adaptive Thresholds


Metric baselines and thresholds concepts

Significancelevel

Percent ofMAX

Metric Baselines and Thresholds: Concepts

Timegrouped

Metric threshold

Automaticallycomputedbaselinestatistics

Baseline period

Adaptive

Fixed

Staticbaseline

Movingwindow





Server generated alerts

Oracle

instance

Server-Generated Alerts

Baseline

> Alert

AWR

ADDM

Advisors

AutoTasks

Enterprise Manager

Serveralertsqueue.

Metric exceeds

threshold.

AWR


Server generated alert types
Server-Generated Alert Types

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


Default server generated alerts
Default Server-Generated Alerts

97% Critical

85% Warning

Tablespace

Tablespacespace usage

Database Control:SYSTEM metrics

ResumableSessionSuspended

Recovery AreaLow 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


Where we are
Where We Are

Thresholds and metric baselines

Enabling metric baselining

Server-generated alerts

Creating and testing an alert

  • Automatic Workload Repository (AWR)

  • Managing the AWR

  • Automatic Database Diagnostic Monitor (ADDM)

  • Advisory framework

  • Automated maintenance tasks


Automatic workload repository awr

In-memorystatistics

MMON

60 minutes

Snapshots

SGA

AWR

Automatic Workload Repository (AWR)

Baseline

Alert

> AWR

ADDM

Advisors

AutoTasks

  • 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

Automatic Database Diagnostic Monitor (ADDM)

Baseline

Alert

AWR

> ADDM

Advisors

AutoTasks

  • Runs after each AWR snapshot

  • Monitors the instance; detects bottlenecks

  • Stores results within the AWR

EM

ADDM

ADDM results

AWR




Advisory framework
Advisory Framework

Baseline

Alert

AWR

ADDM

> Advisors

AutoTasks

PGA Advisor

PGA

SQL Tuning Advisor

Buffer CacheAdvisor

Memory

SGA

ADDM

SQL Access Advisor

Library CacheAdvisor

Segment Advisor

Space

Undo Advisor

MTTR Advisor

Backup


Sql tuning and access advisors
SQL Tuning and Access Advisors

How can I make it run faster?

DBA

Poorly performingSQL statement

RestructuredSQL statement

SQL TuningAdvisor


Memory advisors
Memory Advisors

  • Shared pool

  • Database buffer cache

  • Program Global Area (PGA)

  • Tip: Enable ASMM.


Segment advisor
Segment Advisor

  • Entiretablespace

  • Individualschemaobjects



The dbms advisor package
The DBMS_ADVISOR Package


Automated maintenance tasks
Automated Maintenance Tasks

  • Baseline

  • Alert

  • AWR

  • ADDM

  • Advisors

  • > AutoTasks

    • Scheduler initiates jobs.

    • Jobs run in default maintenance window.

    • Limit maintenance impact on normal operation by using Resource Manager.

  • Maintenance examples:

    • Gathering optimizer statistics

    • Gathering segment information

    • Backing up database


  • Proactive maintenance2

    Automatedtasks

    Automatic

    Proactive

    Serveralerts

    Advisoryframework

    AutomaticWorkload Repository

    Efficient

    Proactive Maintenance

    A review of major elements working together:


    Summary
    Summary

    • In this lesson, you should have learned how to:

      • Set warning and critical alert thresholds

      • Use server-generated alerts

      • Collect and use baseline metrics

      • Manage the Automatic Workload Repository (AWR)

      • Use the Automatic Database Diagnostic Monitor

      • Describe the advisory framework


    Practice overview proactive maintenance
    Practice Overview: Proactive Maintenance

    • This practice covers configuring your database for proactive maintenance by:

      • Configuring an alert and testing it

      • Enabling metric baselines

      • Creating a static metric baseline to compute statistics

      • Deleting the baseline


    ad