Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle AWR vs. STATSPACK table names


Oracle Tips by Burleson Consulting

AWR vs. STATSPACK

The first effective proactive time-series method for Oracle performance appeared in Oracle7 and used begin and end snapshots, using the utlbstat   and utlestat utilities, and the data was stored inside temporary DBA-defined storage tables.  Staring with Oracle8i and back-portable to Oracle8, Oracle Corporation codified the snapshot approach with the STATSPACK utility.

 

While many important time-series reports are now instantly created within Oracle10g Enterprise Manager the senior Oracle DBA may want to go beyond the recommendations of ADDM and the SQL Tuning Advisor.  Complex time series analysis, such as hypothesis testing and correlation analysis, still require that custom queries be written against the wrh$ tables.  Table 3.1 below shows the comparison of Oracle8i and Oracle9i STATSPACK tables to their AWR equivalents.  Fortunately, many of the names of the wrh$ tables are identical to their stats$ equivalents making it easy to migrate STATSPACk scripts to AWR.

 

DBA HIST VIEW

WRH$TABLE

STATSPACK TABLE

dba_hist_event_summary

wrh$_bg_event_summary

stats$bg_event_summary 

dba_hist_buffer_pool_statistics 

wrh$_buffer_pool_statistics 

stats$buffer_pool_statistics

dba_hist_filestatxs

wrh$_filestatxs

stats$filestatxs

dba_hist_latch

wrh$_latch

stats$latch 

dba_hist_latch_children

wrh$_latch_children

stats$latch_children

dba_hist_librarycache

wrh$_librarycache

stats$librarycache

dba_hist_rowcache_summary 

wrh$_rowcache_summary 

stats$rowcache_summary 

dba_hist_sgastat

wrh$_sgastat

stats$sgastat

dba_hist_sql_summary

wrh$_sql_summary

stats$sql_summary

dba_hist_sysstat

wrh$_sysstat

stats$sysstat

dba_hist_system_event

wrh$_system_event

stats$system_event

dba_hist_waitstat

wrh$_waitstat

stats$waitstat

Table 3.1: STATSPACK, DBA HIST and wrh$ equivalencies

 

It is fortunate for the seasoned DBA that the column definitions and contents of these tables are almost identical.  This allows easy porting of the STATSPACK time-series scripts to be run against the wrh$ tables with a minimum of modification. 

 

The reads_10g.sql script below gathers physical disk read counts, the phyrds column of dba_hist_filestatxs .  It then joins this data into the dba_hist_snapshot   view to get the begin_interval_time column. 

 

            reads_10g.sql

 

break on begin_interval_time skip 2

 

column phyrds  format 999,999,999

column begin_interval_time format a25

 

select

   begin_interval_time,

   filename,

   phyrds

from

   dba_hist_filestatxs

  natural join

   dba_hist_snapshot;

 

When the reads.sql script is executed, a display of the running total of physical reads, organized by datafile is shown below.  In this case, the AWR snapshots are collected every half-hour, and the DBA is free to adjust the snapshot collection interval depending on data needs.

 

SQL> @reads

 

BEGIN_INTERVAL_TIME       FILENAME                                     PHYRDS

------------------------- ---------------------------------------- --------

24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF       164,700

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF       26,082

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF       472,008

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF          1,794

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA         2,123

                                                                            

                                                                            

24-FEB-04 11.30.18.296 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF       167,809

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF       26,248

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF       476,616

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF          1,795

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA         2,244

SEE CODE DEPOT FOR FULL SCRIPTS

Starting from the reads_10g.sql script, a where clause criteria can easily be added to create a unique time-series exception report on specific data file or specific time periods.

 

Of course, with a few minor adjustments to this script, physical writes, read time, write time, single block reads, and a host of other neat metrics from the dba_hist_filestatxs   view can also be displayed.

 

Now that general information has been presented on the AWR concept, the following section will provide details about the new AWR table contents.
 


This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational