Transcript Automatic Database Diagnostic Monitor

Be an Effective DBA using
Oracle 10g Automatic
Database Diagnostic Monitor
Edward Hayrabedian
Semantec Bulgaria OOD
Agenda
 Oracle 10g and Oracle 9i Database Manageability Comparison
 Self-Managing Database
 Automatic Workload Repository (AWR)
 Features
 Configuration
 Collection Process
 Database Statistics
 Metrics
 Automatic Database Diagnostic Monitor (ADDM)
 Features
 Problems which can be detected
 Using ADDM
 Drawbacks
 Test cases & Examples
 Using literals instead of bind variables
 Concurrent updates
Oracle 10g and Oracle 9i Database
Manageability Comparison
 How to compare?

A set of basic and typical DBA tasks are measured on a common set of metrics
 Used typical DBA tasks


A sample of 21 tasks covering all the key DBA functionality, grouped in 5 categories
Each group of tasks has an associated weight value. It defines how much of the DBA
work it takes
Category
Weight
Installation and Simple “Out-of-Box” Setup
13%
Day-to-Day Database Administration
27%
Backup & Recovery
6%
Performance Diagnostics & Tuning
34%
Miscellaneous
20%
 What type of metrics?


Time – the total time that a DBA spends in caring out the task
Steps – the number of steps required to complete the task
Oracle 10g and Oracle 9i Database
Manageability Comparison
 Test Results – Task Time Comparison (minutes)
Installation
and Simple
Setup
Day-to-Day
Database
Administration
Backup &
Recovery
Performance
Diagnostics &
Tuning
Total
Oracle 9i
45
35
46
19
147
Oracle 10g
29
13
17
5
64
Oracle 10g
Time Savings
36%
63%
63%
74%
56%
Weighting
Factor
13%
27%
6%
34%
5%
17%
4%
25%
50%
Workday = 8 hr
---------------------9i tasks = 163min
10g tasks = 41min
---------------------Saving = 122 min
DBA need
50% less
time to
perform their
function
Weighted
Time Saving
Oracle 10g and Oracle 9i Database
Manageability Comparison
 Test Results – Complexity Comparison (steps)
Installation
and Simple
“Out-of-Box”
Setup
Day-to-Day
Database
Administration
Backup &
Recovery
Performance
Diagnostics &
Tuning
Total
Oracle 9i
7
15
19
15
56
Oracle 10g
4
9
6
5
24
43%
40%
68%
67%
57%
Oracle 10g
Step Savings
Oracle 10g took
67% fewer steps
to complete the
performance
diagnostics &
tuning tasks
Self-Managing Database
 Manual regular health checks, monitoring activities and bottleneck
investigations are replaced by automatic statistics collection, storage
and analysis
 Introduction of the Advisory Framework

Automatic Database Diagnostic Monitor (ADDM)
SQL Tuning Advisor – automates the tuning of SQL statements and gives an advice

SQL Access Advisor – provides an expert advice on materialized vies, indexes and MV logs

Segment Advisor –

Undo Advisor -

Redo Logfile size Advisor - determines the optimal smallest online redo log file size, based on

responsible for space issues involving a database object. Analyzes the growth
trends and shrink possibility for the segments
suggests parameter values and how much additional space will be needed to support
flashback for a specified time. However, most of the undo tuning (like undo retention) is automatic in Oracle
10g
the current fast_start_mttr_target setting and MTTR statistics

Automatic Shared Memory Tuning -
no longer need to tune SGA individual memory pools.
Only 2 memory parameters should be set and their values is recommended by ADDM
Automatic Workload Repository (AWR)
Features
 Automatic Workload Repository (AWR) –
new persistent store, which
automatically collects and stores statistics
 Called periodically at configured time interval
 More precise and granular information is collected
(compared to StatsPack snapshots)
 The statistics survive database reboots and crashes
 An efficient capture algorithm is used
 Provide historical analysis – no need to repeat the workload
 Completely self-managed
 The statistics are accessible to external users, who can
build their own performance monitoring tools
Automatic Workload Repository (AWR)
Configuration

AWR can be managed either via:
 the Enterprise Manager, or
 PL/SQL API (dbms_workload_repository package)

Two of the most important procedures are:
 modify_snapshot_settings - defines the interval and
retention of snapshot generation

create_snapshot – takes a new snapshot
Examples #1 and #2
Automatic Workload Repository (AWR)
Collection Process
Collection Process –
the new MMON BG process
captures in-memory statistics
from SGA and transfers them
to the Workload repository


The statistic_level parameter controls the number and type of statistics
collected and stored (a dynamic init.ora parameter)
 Basic – almost all advisors are turned off
 Typical - default
 All
Automatic Workload Repository (AWR)
Database Statistics
 The better Oracle kernel is instrumented the better diagnosis –
developing a tool located inside the server allows to resolve hard to diagnose problems by adding
more instrumentation
 Increase to over of 700 wait events
 Easier high-level analysis based on grouping the wait events in the
following categories:








Application
Administration
Commit
Concurrency
Configuration
User I/O
Network Communications
Idle
 Time model –
Time is used as a common currency for comparing across modules. Most of the
advisors report their findings in time
 The new most important “db_time” statistics
–
The total time spent in db calls. The reduction of db_time can be used as a measure of
the effectiveness of the tuning efforts
Automatic Workload Repository (AWR)
Metrics
 Metrics – delta values between the snapshots
 Metrics are normalized by:



Time
Transaction
SQL statement, and so on..
 The rate of change is important rather than statistic’s absolute value
 Alerting thresholds can be set based on 183 predefined metrics
 User-defined metrics (alerts) can be created
Examples #3 and #4
Automatic Database Diagnostic Monitor (ADDM) -
Features
 ADDM is an advisory framework that provides expert
recommendations to improve DB performance
 Build into the 10g kernel
 Called periodically after each AWR run
 Findings are presented in a convenient fashion
 Reports the impact of each finding
 Recommendations and step-by-step instructions
 Using time as a common metric between components
 Focusing on the activities that the DB spends most db_time
time, and then drills down through a sophisticated problem
classification tree to determine the root cause
 Documents also the non-problem areas of the system
Automatic Database Diagnostic Monitor (ADDM) -
Problems which can be detected
 CPU bottlenecks
 Poor connection management
 Excessive parsing
 Lock contention
 IO capacity
 Under sizing of Oracle memory structures e.g. PGA, buffer
cache, log buffer
 High load SQL statements
 High PL/SQL and Java time
 High checkpoint load and cause e.g. small log files, aggressive
MTTR setting
 RAC specific issues
Automatic Database Diagnostic Monitor (ADDM) -
Using ADDM
 Accessed either via Enterprise Manager or PL/SQL API
(dbms_advisor package)
 The main procedures of the dbms_advisor package

Create_task

Execute task – generates the report

Get task report – gets the report text
 Findings
 Recommendations
Drawbacks
 The documentation is not complete and not enough
 The web based Enterprise Manager is slow and
buggy (I won’t comment the user interface design :)
 It is not clear what type of problems won’t be
detected
 The ADDM and AWR are too new to be proved by the
Oracle user community
Summary
 Oracle makes a significant step to makes complicated
things simple to manage
 Give ADDM a try – there is nothing to lose and maybe
something to earn
 ADDM allows to do more for less time – just be effective
 It is time to show some examples …
Examples
Test cases & Examples
 Example #1 - Managing AWR via the EM
 Example #2 - Creating a snapshot via the PL/SQL API
 Example #3 - Manage Metrics in EM
 Example #4 - User-defined Metrics in EM
 Test case #1 – Using Literals instead of bind variables
 Test case #2 – Concurrent updates
Example #1 - Managing AWR via EM
Movie #1 - Managing AWR via EM
Example #2 – Creating AWR Snapshot
via the PL/SQL API
Movie #2 – Creating Snapshot via PL/SQL
Example #3 - Manage Metrics in EM
Movie #3 - Manage Metrics in EM
Example #4 - User-defined Metrics in EM
Movie #4 - User-defined Metrics in EM
Test case #1 – Using Literals instead of bind variables
What will be shown
 The bad practice of
using literals will be
effectively detected by
ADDM
 The number of steps
performed by a DBA in
Oracle 10g is obviously
less than in Oracle 9i
Test case #1 – Using Literals instead of bind variables
The code
declare
l_start_snapshot_id
number;
l_end_snapshot_id
number;
n
number;
begin
l_start_snapshot_id := dbms_workload_repository.create_snapshot();
dbms_output.put_line('Start snapshot id : ' || to_char(l_start_snapshot_id));
-for i in 1..100000 loop
n := dbms_random.random;
execute immediate 'select ' || to_char(n) || ' from dual'
into n;
end loop;
-l_end_snapshot_id := dbms_workload_repository.create_snapshot();
dbms_output.put_line('End snapshot id : ' || to_char(l_end_snapshot_id));
end;
Test case #1 – Using literals instead of bind variables
Movie #5
Test Case #2 – Concurrent Updates
What will be shown
 ADDM will catch a performance problem with application locks
due to concurrent updates against one row
 Reactive Performance Diagnostics
 The Active Sessions graph – diagnosing the problem becomes
‘point and click’ !
 ADDM recommendations and findings
Test Case #2 – Concurrent Updates
The code
Two sessions are concurrently running:
begin
for i in 1..300 loop
update t set x = i;
dbms_lock.sleep(1);
commit;
end loop;
end;
/
Test Case #2 – Concurrent Updates
Movie #6 - Reactive tuning/monitoring
References

Oracle Database 10g and Oracle9 i Database Manageability Comparison February 2004
Copyright © 2004 Oracle Corporation

Oracle Database 10g New Features Oracle 10g Reference for Advanced Tuning & Administration
by Mike Ault, Daniel Liu and Madhu Tumma

Oracle Database 10g: The Self-Managing Database
An Oracle White Paper Nov. 2003

The Self-Managing Database: Automatic Performance Diagnosis
An Oracle White Paper Nov. 2003