Less12 Advisors Mb

12

Proactive Maintenance

Copyright © 2005, Oracle. All rights reserved.

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

12-2

Copyright © 2005, Oracle. All rights reserved.

Proactive Maintenance Automated tasks

Automatic

Server alerts

Proactive

Advisory framework

Automatic Workload Repository

Data warehouse of the database 12-3

Efficient

Automatic collection of important statistics Copyright © 2005, Oracle. All rights reserved.

Direct memory access

Introduction of Concepts and Related > Baseline Tasks Alert • • • • • • • • •

12-4

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

Copyright © 2005, Oracle. All rights reserved.

Introducing Terminology • • •

12-5

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

Copyright © 2005, Oracle. All rights reserved.

Cyclic Nature of Workloads

End of day 1

Value

End of day 2

End of day 3

… Metric OLTP

12-6

DW

Time

Copyright © 2005, Oracle. All rights reserved.

Overview of Thresholds and Metric Baselines: Fixed Thresholds

End of day 1

Value

End of day 2

End of day 3

Fixed thresholds Critical Warning

Metric OLTP

12-7

DW

Deviations missed

Copyright © 2005, Oracle. All rights reserved.

Time

Overview of Thresholds and Metric Baselines: Adaptive Thresholds

Value

Normal

End of baseline

Critical

Metric

Warning

Grouping

12-8

Adaptive thresholds

Deviations captured

Copyright © 2005, Oracle. All rights reserved.

Time

Metric Baselines and Thresholds: Concepts Time grouped

Metric threshold Automatically computed baseline statistics

Baseline period

Static baseline

12-9

Moving window

Adaptive

Percent of MAX

Copyright © 2005, Oracle. All rights reserved.

Fixed

Significance level

Metric Baseline and Threshold Full Notes Page

12-10

Copyright © 2005, Oracle. All rights reserved.

Editing Thresholds

12-11

Copyright © 2005, Oracle. All rights reserved.

Enabling Metric Baselining

12-12

Copyright © 2005, Oracle. All rights reserved.

Creating Static Metric Baselines

12-13

Copyright © 2005, Oracle. All rights reserved.

Server-Generated Alerts

Baseline > Alert AWR ADDM Advisors AutoTasks

Enterprise Manager

Server alerts queue.

Oracle instance Metric exceeds threshold.

AWR

12-14

Copyright © 2005, Oracle. All rights reserved.

Server-Generated Alert Types Metric based Threshold (stateful) alerts

97% Critical

Cleared

85% Warning

Cleared

MMON

DBA_OUTSTANDING_ALERTS

Nonthreshold (stateless) alerts

12-15

Snapshot Too Old

Alert

DBA_ALERT_HISTORY Resumable Session Suspended

Recovery Area Low On Free Space

Event based

Copyright © 2005, Oracle. All rights reserved.

Default Server-Generated Alerts 97% Critical 85% Warning Tablespace

Database Control: SYSTEM metrics

Resumable Session Suspended 12-16

Tablespace space usage

Recovery Area Low On Free Space Copyright © 2005, Oracle. All rights reserved.

Snapshot Too Old

Creating and Testing an Alert 1. Specify a threshold. 2. Create a test case. 3. Check for an alert. 2

1

3

12-17

Copyright © 2005, Oracle. All rights reserved.

Where We Are

• • • • •

12-18

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

Copyright © 2005, Oracle. All rights reserved.

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 In-memory statistics

MMON

60 minutes

SGA

12-19

Baseline Alert > AWR ADDM Advisors AutoTasks

Snapshots

AWR

Copyright © 2005, Oracle. All rights reserved.

AWR Infrastructure External clients EM

SQL*Plus …

SGA Efficient in-memory statistics collection

Internal clients

12-20

V$

DBA_* MMON

ADDM

AWR snapshots

Self-tuning … Self-tuning component component

Copyright © 2005, Oracle. All rights reserved.

AWR Snapshot Sets Relevant period in the past

DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE ( start_snap_id IN NUMBER , end_snap_id IN NUMBER , baseline_name IN VARCHAR2);

12-21

Copyright © 2005, Oracle. All rights reserved.

Enterprise Manager and AWR

12-22

Copyright © 2005, Oracle. All rights reserved.

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)

12-23

Copyright © 2005, Oracle. All rights reserved.

Automatic Database Diagnostic Monitor Baseline (ADDM) Alert • • •

Runs after each AWR snapshot Monitors the instance; detects bottlenecks Stores results within the AWR

AWR > ADDM Advisors AutoTasks

Snapshots

EM

ADDM ADDM results AWR

12-24

Copyright © 2005, Oracle. All rights reserved.

ADDM Findings

1

2

3

12-25

Copyright © 2005, Oracle. All rights reserved.

ADDM Recommendations

12-26

Copyright © 2005, Oracle. All rights reserved.

Advisory Framework

SQL Tuning Advisor

PGA

PGA Advisor Buffer Cache Advisor

Memory ADDM

SGA SQL Access Advisor

Space

Baseline Alert AWR ADDM > Advisors AutoTasks

Library Cache Advisor Segment Advisor Undo Advisor

Backup 12-27

Copyright © 2005, Oracle. All rights reserved.

MTTR Advisor

Advisors Full Notes Page

12-28

Copyright © 2005, Oracle. All rights reserved.

SQL Tuning and Access Advisors How can I make it run faster?

DBA Poorly performing SQL statement

Restructured SQL statement Add indexes or materialized views. Rewrite the SQL statement.

SQL Tuning Advisor 12-29

Correct setup flaws.

Copyright © 2005, Oracle. All rights reserved.

Memory Advisors

12-30

• • •

Shared pool Database buffer cache Program Global Area (PGA)

•

Tip: Enable ASMM.

Copyright © 2005, Oracle. All rights reserved.

Segment Advisor • •

12-31

Entire tablespace Individual schema objects

Copyright © 2005, Oracle. All rights reserved.

Enterprise Manager and Advisors

12-32

Copyright © 2005, Oracle. All rights reserved.

The DBMS_ADVISOR Package Procedure

12-33

Description

CREATE_TASK

Creates a new task in the repository

DELETE_TASK

Deletes a task from the repository

EXECUTE_TASK

Initiates execution of the task

INTERRUPT_TASK

Suspends a task that is currently executing

GET_TASK_REPORT

Creates and returns a text report for the specified task

RESUME_TASK

Causes a suspended task to resume

UPDATE_TASK_ATTRIBUTE S SET_TASK_PARAMETER

Updates task attributes

MARK_RECOMMENDATION

Marks one or more recommendations as accepted, rejected, or ignored

GET_TASK_SCRIPT

Creates a script of all the recommendations that are accepted

Modifies a task parameter

Copyright © 2005, Oracle. All rights reserved.

Automated Maintenance Tasks • • •

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

12-34

Copyright © 2005, Oracle. All rights reserved.

Baseline Alert AWR ADDM Advisors > AutoTasks

Proactive Maintenance A review of major elements working together: Automatic

Automated tasks

Server alerts

Advisory framework

Automatic Workload Repository

12-35

Proactive

Efficient

Copyright © 2005, Oracle. All rights reserved.

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

12-36

Copyright © 2005, Oracle. All rights reserved.

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

12-37

Copyright © 2005, Oracle. All rights reserved.

12-38

Copyright © 2005, Oracle. All rights reserved.