Quiz Post #10: What are the meanings for different letter for awr base tables WR#

Oracle 10g provides the Automatic Worload Repository. The AWR is a repository of performance information collected by the database to aid in the tuning process for DBAs. The main focus for the Oracle database in version 10g is self-manageability. AWR is  the place where the data to aid in self-management is stored.

WHAT DOES AWR DO?
Historically people used bstat and estat to collect Oracle statistics over a time period and then compare them to each other. The bstat/estat approach was replaced with statspack available in Oracle 8i. Statspack was a package provided by Oracle that did roughly the same thing but better. Statspack has now been surpassed in functionality by AWR which is always collecting execution statistics for future analysis and tuning performed by all  of the expert components provided by Oracle. Oracle recommends that all statspack users switch to AWR in 10g.

The statistics saved in the AWR provide full trending an analysis. In addition, database up/down events will not lose data in the AWR.

AWR collects data in the following categories:

• Base Statistics - general database performance metrics since instance start-up.
• SQL – statistics for each executed SQL statement (# executions, # physical reads, etc)
• Deltas – the rate of change of important stats over time. Similar to our collection technologies that dobefore and after snapshots and only show the deltas over the specified period of time.
• Expert Advice – results of the expert analysis engine provided in 10g

Which background process collects AWR information?

AWR is automatically installed and running with 10g. The new MMON process is responsible for collecting data and populating the AWR and stores in tables SYSAUX,

At what intervals?

Frequency is depends on the snapshot collection methods, Further the metrics determined by this collection mechanism varies from 1 second to 10 mins depend on the metric it should collect.

For example active session history would collect 1 sec intervals, where the tablespace monitoring my collect every 10 mins.

AWE Base Tables and of their interest:-

Starts with WR#_*****_******

The third letter # of each table name signifies the type of data that it contains
I – advisory functions (SQL Advice, Space Advice, etc)
M – metadata information
H – historical data

On top of these tables there atleast a ton of views DBA_HIST* which used for different purposes.

Hope this helps

References:- http://www.oracle.com/technetwork/database/focus-areas/manageability/ps-s001-274001-106-1-fin-v1-133763.pdf

-Thanks

Geek DBA