Oracle Performance Tuning II

Oracle Diagnostic Pack:

The Oracle Diagnostic Pack provides automatic performance diagnostic and advanced system monitoring functionality. The Diagnostic Pack includes the following features:

1.Automatic Workload Repository

The AWR collects and stores database statistics relating to problem detection and tuning. AWR is a replacement for the stats pack utility which helps gather database performance statistics. AWR generates snapshots of key performance data, such as system and session statistics, segment-usage statistics, time-model statistics, high-load statistics and stores it in the sysaux tablespace. The MMON process is responsible for collecting the statistics in the SGA and saving them to the sysaux tablespace.

AWR will collect data on the following

• Base statistics that are also part of the V$SYSSTAT and V$SESSTAT views
• SQL statistics
• Database object-usage statistics
• Time-model statistics
• Wait statistics
• ASH (active session history) statistics
• Operating system statistics

Tables that AWR uses to collect statistics
v$sys_time_model	time model stats (db time, java execution time, pl/sql execution time, etc)
v$osstat	operating system stats (avg_busy_ticks, avg_idle_ticks, etc)
v$service_stats	wait statistics ( db cpu, app wait time, user commits, etc)
v$sysstat	system stats
v$sesstat	session stats

To active the AWR change the system parameter statistics_level to one of three values

• basic - this option disables the AWR
• typical (default) - activates standard level of collection
• all - same as typical but includes execution plans and timing info from the O/S

To Modify Snapshot settings:

Exec dbms_workload_repository.modify_snapshot_settings ( interval => 60, retention => 43200);

Interval = minutes (default 60 min)
retention = seconds (default 7 Days)


To Display AWR settings:

Select * from dba_hist_wr_control;

To Take Manual Snapshot:

Exec dbms_workload_repository.create_snapshot;

2. Automatic Database Diagnostic Monitor (ADDM)

The Automatic Database Diagnostic Monitor (ADDM) analyzes data in the Automatic Workload Repository (AWR) to identify potential performance bottlenecks. For each of the identified issues it locates the root cause and provides recommendations for correcting the problem. An ADDM analysis task is performed and its findings and recommendations stored in the database every time an AWR snapshot is taken provided the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. The ADDM analysis includes:

• CPU load
• Memory usage
• I/O usage
• Resource intensive SQL
• Resource intensive PL/SQL and Java
• RAC issues
• Application issues
• Database configuration issues
• Concurrency issues
• Object contention
• Hardware changes
• Database configuration changes
• Schema changes
• Application changes
• Using other advisors

Report can be produced by using addmrpt.sql or by using package DBMS_ADVISOR

3. Performance monitoring (database and host)

It provides real time or historic performance graphical statistics such as Load Average, Active Sessions, Top CPU sessions, Instance Disk IO, Instance Throughput through OEM

4. Event notifications: notification methods, rules, and schedules

Notification Methods allow you to globally define different mechanisms for sending notifications. These include e-mail, SNMP traps and running custom scripts. Once defined, these methods can then be used with Notification Rules for sending notifications to administrators as a result of alert occurrences. Each administrator has Notification Rules defined as a preference.

Preferences are list of users along with email id to receive alerts

Rules includes configuring of Host/Listener/Agent reachablity alerts with various metrics Memory/DISK/CPU/Swap Utilization, Process/tablespace usage threshold etc.

5. Event history and metric history (database and host)

Shows historic alerts, notifications & events

6. Blackouts

Any host can be placed in blackout mode for specific time or in unlimited period to avoid sending alerts from particular HOST/Instances during activity window

7. Dynamic metric baselines

Metric baselines are created from representative samples of metrics taken over a time frame.

Dynamic metric baselines are based upon a rolling window of time that extends a minimum of seven days into the past. Oracle also allows the creation of Dynamic metric baselines that extend 7, 21, 35, and even 90 days into the past

8. Monitoring templates

Monitoring templates can be created to customize monitoring metrics per Host or Instance by setting threshold values

e.g.

For particular instance tablespace Threshold can be set to 80 %, CPU utilization thresholds at 60 % etc.

9. Memory-access based performance monitoring

Memory access based performance monitoring is done through OEM

Performance monitoring in Memory Access Mode shows all performance graphs w.r.t Memory instead of SQL Access Mode, Switching between Memory & SQL Access Mode can be done as below.