The AWR tables are quite simple, extensions of the ancient bstat-estat utilities and the successor to STATSPACK. Whenever an AWR snapshot is requested, Oracle interrogates the in-memory x$ and v$ structures and stores the information in the appropriate Oracle dba_hist views. Having a historical collection over long periods of time gives the DBA the opportunity to accurately simulate and implement an optimal overall performance plan for the database instance.
The best feature of STATSPACK is that it stores Oracle performance information in a set of 25 tables that can be used to develop historical trends. By interrogating these tables, Oracle professionals can gain tremendous insight into the relative performance of their databases.

The STATSPACK schema contains several control tables. The stats$parameter tables controls the thresholds for collection of detailed information, and a table called stats$level_description provides information regarding the level of detail collected with a snapshot.

Within a STATSPACK installation, the stats$sql_summary table will grow very rapidly because STATSPACK will extract SQL from the library cache every time a snapshot is executed. Hence, the Oracle administrator has to be careful to set the appropriate threshold values for stats$sql_summary data collection to ensure that the database doesn't run wild, consuming multiple megabytes of information every day.

The main anchor for STATSPACK is the table called stats$snapshot. This table contains the snapshot ID for all of the subordinate tables and the snap_time indicating when the snapshot was taken. Oracle also implements all of the subordinate tables with referential integrity, using the on cascade delete option. This means that the stats$snapshot table can be deleted in order to delete rows from all of the subordinate tables after they have passed their useful lives within the database. Underneath the stats$snapshot table, we see several categories of system tables. These categories include event tables, parallel server tables, SGA summary tables, system tables, and transaction tables:

• Event tables—These tables contain information about system, session, and idle events within the Oracle region.
  
• Parallel server tables—These tables are used in an OPS environment to store information about row caching in the Integrated Distributed Lock Manager (IDLM), as well as SGA information.
  
• SGA summary tables—These tables store information about latches, SGA statistics, SQL statements, and the background events within Oracle.
  
• System tables—The system table section of the STATSPACK utility contains information on enqueue stats, waits stats, latch stats, as well as system and session statistics, including information on the library cache and rollback statistics.
  
• Transaction tables—The STATSPACK transaction tables contain information about the buffer pool, the buffer pool statistics, and most importantly, the I/O activity against every file within the system.

Taken together, these 25 STATSPACK tables provide a huge amount of information regarding the performance of the Oracle database. It is the challenge of the Oracle administrator to understand these tables and the value of the information they contain and then to understand how to apply this information to their own performance-tuning needs.

How STATSPACK works

The Oracle STATSPACK utility was the natural outgrowth of Oracle's earlier utilities that compared beginning snapshots with ending snapshots. The original script called for utlbstat.sql and utlestat.sql. The only shortcoming to using these utilities was that the output from the elapsed time report was not stored in any type of Oracle table, and it was cumbersome to compare elapsed-time reports.

Trend reports

The STATSPACK reports are even more interesting for doing long-term trend analysis. Because STATSPACK can be configured to take hourly reports of the entire Oracle database, it is extremely useful for doing long-term planning in trend analysis and developing predictive models for future resource consumption. Predictive modeling is easy when running STATSPACK reports to summarize information by overall trends, the day of the week, or the hour of the day.

Unlike any other Oracle tool, you can use the STATSPACK utility to provide tremendous insight into both hourly and daily trends that may have gone unnoticed within an Oracle database. Almost all Oracle databases have some kind of ongoing trend patterns, and it's the job of the Oracle DBA to identify those patterns and plan for the changes in database activity. By knowing those repeating periods when their database is undergoing stress, they can take appropriate action in order to alleviate the problem.




Oracle STATSPACK/AWR books:

Oracle High-Performance Tuning with STATSPACK, Oracle Press

Oracle9i High-Performance Tuning with STATSPACK , Oracle Press
Oracle Tuning: The Definitive Reference, Rampant TechPress