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

Enabling Oracle Performance Pack

Enabling Performance Tuning & Diagnostic pack:

Method 1:  Granting Management Pack Access through OEM

Home Page>setup>Management Pack Access <Grant Access> <Apply>

Method 2:

ü  Step 1

Disabling Oracle Performance Pack

   Disabling Performance Tuning & Diagnostic pack:

Method 1:  Revoking Management Pack Access through OEM

Home Page>setup>Management Pack Access <Remove Access> <Apply>

Method 2:

ü  Step 1

Oracle Performance Tuning I

Oracle Performance Tuning : Tuning Pack 

Oracle has introduced Performance tuning & Diagnostic Pack (10g onwards), let me brief you about Tuning Pack first

Oracle Tuning Pack :

The Oracle Tuning Pack provides database administrators with expert performance management for the Oracle environment, including SQL tuning and storage optimizations. The Oracle Diagnostic Pack is a prerequisite product to the Oracle Tuning Pack. Therefore, to use the Tuning Pack, you must also have a Diagnostic Pack. The Tuning Pack includes the following features:

1.SQL Access Advisor :