关于AWR

10g里从catsvrm.sql里可以了解AWR，11g里则是catawrbt.sql。

从catsvrm.sql里的脚本catawrtb.sql和catawrvw.sql可以全面了解AWR所涉及到的基表和视图，有兴趣的话可以看一下里面的注释！

当你要重建AWR的时候，你需要执行脚本catnoawr.sql。但是这个脚本在某些平台上10.2.0.3以下版本里是没有的。

以我所在的aix-64为例，10.2.0.1就没有catnoawr.sql，10.2.0.4就有。

从catnoawr.sql里你可以了解AWR所涉及到的基表的分类，如下所示：

Rem ******************************************************

Rem  Remove the local DBID from the WRM$_WR_CONTROL table

Rem ******************************************************

BEGIN

  dbms_swrf_internal.remove_wr_control;

END;

/

 

Rem ****************************************************

Rem  Drop the types used for AWR reporting

Rem ****************************************************

 

drop type AWRRPT_TEXT_TYPE_TABLE

/

drop type AWRRPT_TEXT_TYPE

/

drop type AWRRPT_HTML_TYPE_TABLE

/

drop type AWRDRPT_TEXT_TYPE_TABLE

/

drop type AWRDRPT_TEXT_TYPE

/

drop type AWRRPT_HTML_TYPE

/

drop type AWRRPT_ROW_TYPE

/

drop type AWRRPT_NUM_ARY

/

drop type AWRRPT_VCH_ARY

/

drop type AWRRPT_CLB_ARY

/

 

Rem **************************************************************

Rem ... Dropping the Workload Repository History (WRH$) Tables ...

Rem **************************************************************

 

drop table WRH$_FILESTATXS

/

drop table WRH$_FILESTATXS_BL

/

drop table WRH$_TEMPSTATXS

/

drop table WRH$_DATAFILE

/

drop table WRH$_TEMPFILE

/

drop table WRH$_COMP_IOSTAT

/

 

Rem *************************************************************************

Rem ---------------------- SQL Statistics ----------------------------------

Rem *************************************************************************

 

drop table WRH$_SQLSTAT

/

drop table WRH$_SQLSTAT_BL

/

drop table WRH$_SQLTEXT

/

drop table WRH$_SQL_SUMMARY

/

drop table WRH$_SQL_PLAN

/

drop table WRH$_SQL_BIND_METADATA

/

drop table WRH$_OPTIMIZER_ENV

/

Rem *************************************************************************

Rem ---------------------- Concurrency Statistics ---------------------------

Rem *************************************************************************

 

drop table WRH$_SYSTEM_EVENT

/

drop table WRH$_SYSTEM_EVENT_BL

/

drop table WRH$_EVENT_NAME

/

drop table WRH$_LATCH_NAME

/

drop table WRH$_BG_EVENT_SUMMARY

/

drop table WRH$_WAITSTAT

/

drop table WRH$_WAITSTAT_BL

/

drop table WRH$_ENQUEUE_STAT

/

drop table WRH$_LATCH

/

drop table WRH$_LATCH_BL

/

drop table WRH$_LATCH_CHILDREN

/

drop table WRH$_LATCH_CHILDREN_BL

/

drop table WRH$_LATCH_PARENT

/

drop table WRH$_LATCH_PARENT_BL

/

drop table WRH$_LATCH_MISSES_SUMMARY

/

drop table WRH$_LATCH_MISSES_SUMMARY_BL

/

 

Rem *************************************************************************

Rem ---------------------- Instance Statistics ------------------------------

Rem *************************************************************************

 

drop table WRH$_LIBRARYCACHE

/

drop table WRH$_DB_CACHE_ADVICE

/

drop table WRH$_DB_CACHE_ADVICE_BL

/

drop table WRH$_BUFFER_POOL_STATISTICS

/

drop table WRH$_SGA

/

drop table WRH$_SGASTAT

/

drop table WRH$_SGASTAT_BL

/

drop table WRH$_PGASTAT

/

drop table WRH$_PROCESS_MEMORY_SUMMARY

/

drop table WRH$_ROWCACHE_SUMMARY

/

drop table WRH$_ROWCACHE_SUMMARY_BL

/

drop table WRH$_RESOURCE_LIMIT

/

drop table WRH$_SHARED_POOL_ADVICE

/

drop table WRH$_STREAMS_POOL_ADVICE

/

drop table WRH$_SQL_WORKAREA_HISTOGRAM

/

drop table WRH$_PGA_TARGET_ADVICE

/

drop table WRH$_INSTANCE_RECOVERY

/

drop table WRH$_JAVA_POOL_ADVICE

/

drop table WRH$_THREAD

/

drop table WRH$_SGA_TARGET_ADVICE

/

 

Rem *************************************************************************

Rem --------------------- General System Statistics -------------------------

Rem *************************************************************************

 

drop table WRH$_SYSSTAT

/

drop table WRH$_SYSSTAT_BL

/

drop table WRH$_SYS_TIME_MODEL

/

drop table WRH$_SYS_TIME_MODEL_BL

/

drop table WRH$_OSSTAT

/

drop table WRH$_OSSTAT_BL

/

drop table WRH$_PARAMETER

/

drop table WRH$_PARAMETER_BL

/

drop table WRH$_STAT_NAME

/

drop table WRH$_OSSTAT_NAME

/

drop table WRH$_PARAMETER_NAME

/

 

Rem *************************************************************************

Rem ------------------------- Undo Statistics -------------------------------

Rem *************************************************************************

 

drop table WRH$_UNDOSTAT

/

 

Rem *************************************************************************

Rem ----------------------- Segment Statistics ------------------------------

Rem *************************************************************************

 

drop table WRH$_SEG_STAT

/

drop table WRH$_SEG_STAT_BL

/

drop table WRH$_SEG_STAT_OBJ

/

 

Rem *************************************************************************

Rem ---------------------- Metrics Tables -----------------------------------

Rem *************************************************************************

drop table WRH$_METRIC_NAME

/

drop table WRH$_SYSMETRIC_HISTORY

/

drop table WRH$_SYSMETRIC_SUMMARY

/

drop table WRH$_SESSMETRIC_HISTORY

/

drop table WRH$_FILEMETRIC_HISTORY

/

drop table WRH$_WAITCLASSMETRIC_HISTORY

/

 

Rem *************************************************************************

Rem ---------------------- Tablespace Statistics ----------------------------

Rem *************************************************************************

drop table WRH$_TABLESPACE_SPACE_USAGE

/

 

Rem *************************************************************************

Rem -------------------------- RAC Statistics -------------------------------

Rem *************************************************************************

 

drop table WRH$_DLM_MISC

/

drop table WRH$_DLM_MISC_BL

/

drop table WRH$_CR_BLOCK_SERVER

/

drop table WRH$_CURRENT_BLOCK_SERVER

/

drop table WRH$_INST_CACHE_TRANSFER

/

drop table WRH$_INST_CACHE_TRANSFER_BL

/

 

Rem *************************************************************************

Rem ---------------------Active Session History -----------------------------

Rem *************************************************************************

drop table  WRH$_ACTIVE_SESSION_HISTORY

/

drop table  WRH$_ACTIVE_SESSION_HISTORY_BL

/

 

Rem *************************************************************************

Rem -------------------------- Tablespace Statistics ------------------------

Rem *************************************************************************

drop table WRH$_TABLESPACE_STAT

/

drop table WRH$_TABLESPACE_STAT_BL

/

 

Rem *************************************************************************

Rem -------------------------- WRH$_LOG Statistics ------------------------

Rem *************************************************************************

drop table WRH$_LOG

/

 

Rem *************************************************************************

Rem -------------------------- MTTR Target Advice ---------------------------

Rem *************************************************************************

drop table WRH$_MTTR_TARGET_ADVICE

/

 

Rem *************************************************************************

Rem ----------------------- Service Statistics ------------------------------

Rem *************************************************************************

 

drop table WRH$_SERVICE_NAME

/

drop table WRH$_SERVICE_STAT

/

drop table WRH$_SERVICE_STAT_BL

/

drop table WRH$_SERVICE_WAIT_CLASS

/

drop table WRH$_SERVICE_WAIT_CLASS_BL

/

 

Rem *************************************************************************

Rem ----------------------- Session Time Stats ------------------------------

Rem *************************************************************************

 

drop table WRH$_SESS_TIME_STATS

/

 

Rem *************************************************************************

Rem ----------------------- STREAMS Stats Table -----------------------------

Rem *************************************************************************

 

drop table WRH$_STREAMS_CAPTURE

/

drop table WRH$_STREAMS_APPLY_SUM

/

drop table WRH$_BUFFERED_QUEUES

/

drop table WRH$_BUFFERED_SUBSCRIBERS

/

drop table WRH$_RULE_SET

/

 

Rem *************************************************************************

Rem -------- Tables For Maintenance Window Auto Tasks Schedules -------------

Rem *************************************************************************

 

drop table WRI$_SCH_CONTROL

/

drop table WRI$_SCH_VOTES

/

 

Rem ************************

Rem Drop the Metadata Tables

Rem ************************

 

drop table WRM$_BASELINE

/

drop table WRM$_WR_CONTROL

/

drop table WRM$_SNAPSHOT

/

drop table WRM$_SNAP_ERROR

/

drop table WRM$_DATABASE_INSTANCE

/