Tales From A Lazy Fat DBA

This post gives an overview and understanding of  AWR and ADDM in Oracle 10g

AWR Architecture

Oracle 10g – awrArchitecture

AWR Features :

Automatic Workload Repository (AWR) collects processes and maintains performance statistics for problem detection & self tuning purposes.

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

• Automatic Database Diagnostic Monitor
• SQL Tuning Advisor
• Undo Advisor
• Segment Advisor

AWR Space Requirements

Space requirement for AWR depends on following:

Number of active sessions in system at given time.

• Snapshot interval – Frequency at which snapshot are captured.
• Historical data retention period.

Estimate:

Snapshot interval           : 1 hr

No of sessions               : 10

Retention period : 7 days

=======================================

Space for AWR              : 200 – 300 MB

=======================================

Initialization parameters & Grants

STATISTICS_LEVEL –> TYPICAL or ALL

Note: BASIC – turns off in-memory collection of many system statistics.

• User running AWR packages needs DBA role.

Note:

• For STATISTICS_LEVEL, default setting is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.
• When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
• Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

AWR Automated Snapshots

Oracle Database 10g uses a scheduled job, GATHER_STATS_JOB, to collect AWR statistics.

This job is created, and enabled automatically, when you create a new Oracle database under Oracle Database 10g.

Note: GATHER_STATS_JOB is Oracle defined automatic optimizer statistics collection job run by SYS. For more information refer DBA_SCHEDULER_JOBS, DBA_SCHEDULER_JOB_RUN_DETAILS

Checking current Configuration in database

• Use following query to check the current settings of “GATHER_STATS_JOB” in any database:

SELECT a.job_name, a.enabled, c.window_name, c.schedule_name,

c.start_date, c.repeat_interval

FROM dba_scheduler_jobs a,

dba_scheduler_wingroup_members b,

dba_scheduler_windows c

WHERE job_name=’GATHER_STATS_JOB’

And a.schedule_name=b.window_group_name

And b.window_name=c.window_name;

• To check the AWR snap interval & Retention time – use following query:

SELECT * FROM DBA_HIST_WR_CONTROL

Sample Result

DBID                            SNAP_INTERVAL                      RETENTION                  TOPNSQL

————————————————————————————————————————–

1195050809                  +00000 01:00:00.0                     +00007 00:00:00.0         DEFAULT

Generate AWR Report

Oracle provides two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql).

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

Note:  awrrpt.sql & awrrpti.sql are exactly same except that awrrpti report allows specific instance to report on. awrrpti.sql asks to enter dbid for particular instance of interest.

Execution of script

• SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

DB Id                         DB Name          Inst Num           Instance

—————————————————————————————-

1195050809                 O01ABC0             1                   O01ABC0

• Specify the Report Type : (html / txt)

• Specify the number of days of snapshots to choose: This will show the snap id along with the time data.

Instance            DB Name           Snap Id            Snap Started                 Level

———————————————————————————————————-

O01ABC0          O01ABC0           5652                01 Apr 2008 00:00          1

5653               01 Apr 2008 01:00          1

5654                         01 Apr 2008 02:00          1

5655               01 Apr 2008 03:00          1

• Specify the Begin and End Snapshot Ids: Choose from the list displayed due to previous point.

• Specify the Report Name: If leave blank then it will take default file name.

• FTP the result in the local drive and view the results

Changing AWR Configuration

• Oracle Default Settings

è  Snap Time               : 1 hr

è  Snap Retention        : 7 days

• Snapshot

Snapshots & Baselines are sets of historical data for a specific time period that are used for performance comparison.

è  Change snapshot default settings by using following package:

BEGIN

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings (

retention => 43200,        — Minutes (= 30 Days). Current value retained if NULL.

interval  => 30);              – Minutes. Current value retained if NULL.

END;

/

è Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

low_snap_id  => 22,

high_snap_id => 32);

END;

/

• Baseline

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past.

è Create Baseline:

BEGIN

DBMS_WORKLOAD_REPOSITORY.create_baseline (

start_snap_id => 210,

end_snap_id   => 220,

baseline_name => ‘batch baseline’);

END;

/

è The pair of snapshots associated with a baseline is retained until the baseline is explicitly deleted:

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_baseline (

baseline_name => ‘batch baseline’,

cascade       => FALSE); — Deletes associated snapshots if TRUE.

END;

/

Workload Repository Views

View Name	Description
V$ACTIVE_SESSION_HISTORY	Displays the active session history (ASH) sampled every second.
V$METRIC	Displays metric information.
V$METRICNAME	Displays the metrics associated with each metric group.
V$METRIC_HISTORY	Displays historical metrics.
V$METRICGROUP	Displays all metrics groups
DBA_HIST_ACTIVE_SESS_HISTORY	Displays the history contents of the active session history.
DBA_HIST_BASELINE	Displays baseline information.
DBA_HIST_DATABASE_INSTANCE	Displays database environment information.
DBA_HIST_SNAPSHOT	Displays snapshot information.
DBA_HIST_SQL_PLAN	Displays SQL execution plans.
DBA_HIST_WR_CONTROL	Displays AWR settings.

Automatic Performance Diagnostics

The automatic database diagnostic monitor (ADDM) analyzes the AWR data on regular basis, then locates the root cause of performance problems, provides recommendation for correcting any problems, identifies non problem areas of the system.

• ADDM analysis is performed every time AWR snapshot is taken and the results are saved in database.
• The goal of ADDM analysis is to reduce a single throughput metric called DB time. DB time is the cumulative time spent by database server in processing user requests. (DB time includes CPU time and time of non-idle user sessions).
• ADDM does not target individual user response times.

Types of Problems

Following are the types of problems ADDM report addresses:

è     CPU bottlenecks

è     Undersized memory structures

è     I/O capacity issues

è     High load SQL statements

è     RAC specific issues

è     Database configuration issues

è     Also provides recommendations on hardware changes, database configuration & schema changes.

ADDM Results

ADDM report has following sections:

è     Problem: Finding that describe the root cause of database performance issue.

è     Symptom: Finding that contain information that often lead to one or more problem finding.

è     Information: Findings that are used to report non-problem area of the system.

è     Recommendations: Composed of action & rationales.

Initialization parameters & Grants

• User running the ADDM scripts needs “ADVISOR” privileges

• STATISTICS_LEVEL should be set to TYPICAL or ALL

• DBIO_EXPECTED – describes the expected performance of the I/O subsystem. The value of DBIO_EXPECTED is the average time that takes to read a single database block in microsecond. Default is 10millisecond.

Note:

To modify DBIO_EXPECTED value to 8000 microseconds, execute following as SYS user.

EXECUTE DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER (

‘ADDM’, ‘DBIO_EXPECTED’, 8000);

Running ADDM

We can generate ADDM report in two ways:

• By using oracle script $ORACLE_HOME/rdbms/admin/addmrpt.sql
• DBMS_ADVISOR package.

Running ADDM using addmrpt.sql

• SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id    DB Name      Inst Num Instance

———– ———— ——– ————

1195050809 O01ABC0             1 O01ABC0

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id     Inst Num DB Name      Instance     Host

———— ——– ———— ———— ————

* 1195050809        1 O01ABC0      O01ABC0      test01

Using 1195050809 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Automatic Workload Repository (AWR) in Oracle Database 10g

Automatic Workload Repository (AWR) in Oracle Database 10g

Snap

Instance DB Name Snap Id Snap Started Level

———————————————————————————————————————

O01ABC0 O01ABC0 6020 16 Apr 2008 08:00 1

6021 16 Apr 2008 09:00 1

6022 16 Apr 2008 10:00 1

• Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 6020

Begin Snapshot Id specified: 6020

Enter value for end_snap: 6022

Running ADDM using DBMS_ADVISOR APIs

Steps:

• Create advisor task. (DBMS_ADVISOR.CREATE_TASK)
• Setting required parameters to run specific type of task, such as START_SNAPSHOT, END_SNAPSHOT parameters. (DBMS_ADVISOR.TASK_PARAMETER)
• Execute task. (DBMS_ADVISOR.EXECUTE_TASK)
• View results ((DBMS_ADVISOR.GET_TASK_REPORT)

Automatic Workload Repository (AWR) in Oracle Database 10g

ADDM Views

View Name	Description
DBA_ADVISOR_TASKS	Provides information about existing task, like – task id, name, etc.
DBA_ADVISOR_LOG	Contain the current task information such as status, progress, error messages, and execution times.
DBA_ADVISOR_RECOMMENDATIONS	Complete result of diagnostic task.
DBA_ADVISOR_FINDINGS	Display all symptoms and specific recommendations.