proactive maintenance n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Proactive Maintenance PowerPoint Presentation
Download Presentation
Proactive Maintenance

Loading in 2 Seconds...

play fullscreen
1 / 28

Proactive Maintenance - PowerPoint PPT Presentation


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


Download Now 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

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