Proactive Maintenance

<back to oca>

Gather optimizer statistics

This is pretty automatic.

Manage the Automatic Workload Repository

AWR is the name of the repository owner by SYSMAN and stored in SYSAUX tablespace. This repository contains e set of snapshots of statistics gathered by default every 60 minutes and retained for 7 days. Gather interval and retention are modifiable with pl/sql procedure MODIFY_SNAPSHOT_SETTINGS of the package DBMS_WORKLOAD_REPOSITORY. Obviously this can easily made with EM Console

Use the Automatic Database Diagnostic Monitor (ADDM)

This is an helper that analizes AWR and can give you suggestions. It populates same dictionary views:

  • DBA_ADVISOR_FINDINGS
  • DBA_ADVISOR_OBJECTS
  • DBA_ADVISOR_RECOMMENDATIONS
  • DBA_ADVISOR_RATIONALE

There are some “ADVISORS” part of the ADDM:

  • SQL Tuning Advisor: helps you to find critical or improvable statements
  • SQL Access Advisor: gives you suggestion about possible indexes or materialized view that can improve performance of a statement or of a set of statements.
  • Memory Advisor: controls SGA usage and performance
  • MTTR Advisor: this checks checkpointing attivity, sintetically estimates MTTR. Check FAST_START_MTTR_TARGET parameter
  • Segment Advisor
  • Undo Advisor

Set warning and critical alert thresholds

You can define some baselines and on those baselines are based warning and critical thresholds.

React to performance issues

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: