[스크랩] AWR vs. STATSPACK table 이름 (dba hist view, wrh$table, statstpack table 이름 정리)

 

http://www.dba-oracle.com/oracle10g_tuning/t_awr_statspack_table_names.htm

 

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.