Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Oracle Tips by Burleson

Oracle 10g Automatic Workload Repository (AWR)

AWR is the main infrastructure that collects in-memory statistics at regular intervals and makes them available to the internal and external services or clients. External clients, such as Oracle Enterprise Manager and SQL*plus sessions, can view the AWR information through the data dictionary views. Internal clients, such as the ADDM and other self-tuning components or advisories, make use of the contents in the AWR.

General Benefits

Advantages of the new workload repository include:

  • AWR is a record of all database in-memory statistics historically stored. In the past, historical data could be obtained manually using the ‘statspack’ utility. AWR automatically collects more precise and granular information than past methods.
     

  • With a larger data sample, more informed decisions could be made. The self-tuning mechanism uses this information for trend analysis.
     

  • The statistics survive database reboots and crashes.
     

  • Another benefit is that AWR statistics are accessible to external users, who can build their own performance monitoring tools, routines, and scripts.

Oracle recommends that statspack users switch to the Workload Repository in Oracle Database 10g.

Oracle 10g AWR Physical Structures

AWR is stored in tables owned by ‘SYS’ but physically located on the SYSAUX tablespace. The Workload Repository contains two types of tables:

  • Metadata Tables: These are used to control, process, and describe the Workload Repository tables. For example, Oracle uses the metadata tables to determine when to perform snapshots, and what to capture to disk.
     

  • Historical Statistics Tables: These tables store historical statistical information about the database in the form of snapshots. Each snapshot is a capture of the in–memory database statistics data at a certain point in time. All names of the AWR tables are prefixed with
     

  • WRx$ with x specifying the kind of table:
     

  • WRM$ tables store metadata information for the Workload Repository.
     

  • WRH$ tables store historical data or snapshots.
     

  • WRI$ tables store data related to advisory functions.

The WRx$ tables are organized into the following categories: File Statistics, General System Statistics, Concurrency Statistics, Instance Tuning Statistics, SQL Statistics, Segment Statistics, Undo Statistics, Time– Model Statistics, Recovery Statistics, and RAC Statistics. Fig 9.1 shows a graphical view of the AWR table types.

Following is the list of WRM$ tables that control all repository operations.

WRM$_BASELINE
WRM$_DATABASE_INSTANCE
WRM$_SNAPSHOT
WRM$_SNAP_ERROR
WRM$_WR_CONTROL

Dictionary views are also provided, making the historical data available to users for query. Any view related to the historical information in the AWR has the dba_hist_ prefix. Figures 9.2 and 9.3 show the full list of the WR tables and the dba_hist* tables respectively.

 

Oracle 10g AWR Collection Process

The collection process involves the capture of in-memory statistics from the SGA and their transfer to the physical tables located in the workload repository. The new background process, MMON, does this. The frequency of the capture snapshot is 30 minutes by default, however it can be adjusted suitably.

You can control the interval and retention of snapshot generation by the dbms_workload_repository. modify_snapshot_settings procedure. For example:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (43200, 15);

In this example, the retention period is specified as 30 days (43200 min) and the interval between each snapshot is 10 min.

 

The snapshots are used for computing the rate of change of a statistic. This is mainly used for performance analysis. A snapshot sequence numer (snap_id) identifies each snapshot, which is unique within the Workload Repository.

Figure 9.4 shows the relation of AWR to other components.

 

 

Oracle 10g AWR Types of Data Collected

The collected statistics include:

  • New time model statistics that show the amount of  time spent on database activities.
     

  • Object Statistics that determine both access and usage of the segments.
     

  • Some selected statistics collected in v$sysstat and v$sesstat
     

  • Some of the optimizer statistics that include statistics for self-learning and tuning.
     

  • The ADDM Active Session History (ASH), which represents the history of the recent session’s activity.

These statistics can be broadly categorized into 5 groups based on their nature.

  • Base Statistics – This group represents raw data, which are generally values from the start of the database, e.g. total physical reads.
     

  • SQL Statistics – Important measurements regarding the SQL statement. For example: Disk Read per SQL statement.
     

  • Metrics – These are the secondary statistics, and the most interesting ones from a tuning point of view. Metrics track the rates of change of activities in the database. For example, the average physical reads in the system in the last 30 min. is a metric.
     

  • Contents of Active Session History - For example, db file sequential read wait for SID of 16, file# 12, block# 1245, obj# 67, and time: 20000us.
     

  • Advisor Results – Results of the expert analysis by the advisor framework.

Oracle 10g Using and Managing AWR

You can query base statistics and metrics from the various fixed views provided. You can optionally create your own snapshots and baselines using the dbms_workload_repository package.

dbms_workload_repository package procedures are as follows:

  • modify_snapshot_settings: Procedure to modify the snapshot settings.
     

  • drop_baseline: Procedure to drop a single baseline.
     

  • create_baseline: Procedure to create a single baseline.
     

  • ddrop_snapshot_range: Procedure to drop a range of snapshots.
     

  • ccreate_snapshot: Procedure to create a manual snapshot immediately.

You can use the Oracle-provided SQL scripts to generate reports to view the contents of AWR.

  • swrfrpt.sql: This script generates a report showing information on the overall behavior of the system over a time period. The script generates a text file.
     

  • swrfrpth.sql: This script gives the same information as swrfrpt.sql, however, the generated output file uses HTML format.

 

AWR is also used to track database usage metrics. The usage metrics represent how you use the database features. The database usage metrics are divided into two categories:

  • The database feature usage statistics.
     

  • The High Water Mark (HWM) values of certain database attributes.

Database Feature Usage Statistics include Advanced Replication, Oracle Streams, AQ, Virtual Private Database, Audit options etc.

High Water Mark Statistics include the size of the largest segment, the maximum number of sessions, the maximum number of tables, the maximum size of the database, the maximum number of data files, etc.

To view usage metric information, you can query the following:

  • dba_feature_usage_statistics - Lists the usage statistics of various database features.
     

  • dba_high_water_mark_statistics - Lists the database high water mark statistics.

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks