Transcript Lesson 12

Proactive Maintenance
Copyright © 2005, Oracle. All rights reserved.
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
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
Introducing Terminology
•
•
•
•
•
12-4
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
Copyright © 2005, Oracle. All rights reserved.
Optimizer Statistics
Optimizer statistics are:
• Not real time
• Persistent across instance restarts
• Collected automatically
> Statistics
AWR
ADDM
Advisors
Alerts
AutoTasks
SQL> SELECT COUNT(*) FROM hr.employees;
COUNT(*)
---------214
SQL> SELECT num_rows FROM dba_tables
2 WHERE owner='HR' AND table_name = 'EMPLOYEES';
NUM_ROWS
---------107
12-5
Copyright © 2005, Oracle. All rights reserved.
Using the Manage Optimizer
Statistics Page
12-7
Copyright © 2005, Oracle. All rights reserved.
Statistic Levels
STATISTICS_LEVEL
BASIC
TYPICAL
ALL
Self-tuning
capabilities disabled
Recommended
default value
Additional statistics
for manual
SQL diagnostics
12-9
Copyright © 2005, Oracle. All rights reserved.
Automatic Workload Repository
(AWR)
•
•
•
Statistics
> AWR
.
ADDM
Advisors
Alerts
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
In-memory
statistics
MMON
60 minutes
Snapshots
SGA
AWR
12-10
Copyright © 2005, Oracle. All rights reserved.
AWR Infrastructure
External clients
EM
SQL*Plus …
SGA
Efficient
in-memory
statistics
collection
Internal clients
12-11
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-12
Copyright © 2005, Oracle. All rights reserved.
Enterprise Manager and AWR
12-13
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-14
Copyright © 2005, Oracle. All rights reserved.
Automatic Database Diagnostic
Monitor (ADDM)
•
•
•
Statistics
AWR
.
> ADDM
Advisors
Alerts
AutoTasks
Runs after each AWR snapshot
Monitors the instance; detects bottlenecks
Stores results within the AWR
Snapshots
EM
ADDM
ADDM results
AWR
12-15
Copyright © 2005, Oracle. All rights reserved.
ADDM Findings
1
2
3
12-16
Copyright © 2005, Oracle. All rights reserved.
ADDM Recommendations
12-17
Copyright © 2005, Oracle. All rights reserved.
Advisory Framework
SQL Tuning
Advisor
PGA
PGA Advisor
Buffer Cache
Advisor
Memory
ADDM
Statistics
AWR
ADDM
> Advisors
Alerts
AutoTasks
SGA
SQL Access
Advisor
Library Cache
Advisor
Segment Advisor
Space
Undo Advisor
Backup
12-18
Copyright © 2005, Oracle. All rights reserved.
MTTR Advisor
Enterprise Manager and Advisors
12-20
Copyright © 2005, Oracle. All rights reserved.
The DBMS_ADVISOR Package
Procedure
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_ATTRIBUTES Updates task attributes
12-21
SET_TASK_PARAMETER
Modifies a task parameter
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
Copyright © 2005, Oracle. All rights reserved.
Server-Generated Alerts
Statistics
AWR
ADDM
Advisors
> Alerts
AutoTasks
Enterprise Manager
Server
alerts
queue.
Oracle
instance
Metric exceeds
threshold.
AWR
12-22
Copyright © 2005, Oracle. All rights reserved.
Default Server-Generated Alerts
97% Critical
85% Warning
Tablespace
Database Control:
SYSTEM metrics
Resumable
Session
Suspended
12-23
Tablespace
Space Usage
Recovery Area
Low On
Free Space
Copyright © 2005, Oracle. All rights reserved.
Snapshot
Too Old
Setting Thresholds
12-24
Copyright © 2005, Oracle. All rights reserved.
Creating and Testing an Alert
1. Specify a threshold.
2. Create a test case.
3. Check for an alert.
1
2
3
12-25
Copyright © 2005, Oracle. All rights reserved.
Alerts Notification
12-26
Copyright © 2005, Oracle. All rights reserved.
Reacting to Alerts
•
•
•
12-28
If needed, gather more input, for example, by
running ADDM or another advisor.
Take corrective measures.
Acknowledge alerts, which are not automatically
cleared.
Copyright © 2005, Oracle. All rights reserved.
Alert Types and Clearing Alerts
Metric based
Threshold
(stateful)
alerts
97% Critical
Cleared
85% Warning
Cleared
MMON
DBA_OUTSTANDING_ALERTS
Nonthreshold
(stateless)
alerts
12-29
Snapshot
Too Old
Alert
DBA_ALERT_HISTORY
Resumable
Session
Suspended
Recovery Area
Low On
Free Space
Event based
Copyright © 2005, Oracle. All rights reserved.
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
12-30
Copyright © 2005, Oracle. All rights reserved.
Statistics
. AWR
ADDM
Advisors
Alerts
> AutoTasks
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
12-31
Copyright © 2005, Oracle. All rights reserved.
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
12-32
Copyright © 2005, Oracle. All rights reserved.