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
è Snap Time : 1 hr
è Snap Retention : 7 days
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;
/
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. |
Tales From A Lazy Fat DBA 