Oracle AWR wri$, wrh$ and wrm$

The wrh$ AWR tables store important historical statistical information about the database in the form of periodic snapshots. Each snapshot is a capture of the in–memory x$ fixed view and other control structures at a certain point in time. Each of the AWR table names is prefixed with wrm$ (Metadata tables), wrh$ (History tables), or wri$ (Advisory tables).

§       The wrm$ tables store metadata information for the Workload Repository.

§       The wrh$ tables store historical data or snapshots.

§       The wri$ tables: These 49 tables store data related to advisory functions.

The List of Oracle10g WRH$ wait event table

wrh$_active_session_history

wrh$_active_session_history_bl

wrh$_bg_event_summary                     

wrh$_event_name

wrh$_metric_name

wrh$_sessmetric_history

wrh$_sys_time_model

wrh$_sys_time_model_bl

wrh$_sysmetric_history

wrh$_sysmetric_summary

wrh$_sysstat                                        

wrh$_sysstat_bl

wrh$_system_event                                  

wrh$_system_event_bl

wrh$_waitclassmetric_history     

wrh$_waitstat                             

wrh$_waitstat_bl

The  Oracle10g dba_hist views that are used to create time-series performance reports We will begin with an overview of the dba_hist views.

Oracle10g performance exception reports that can be easily generated from these views with SQL*Plus

The default collection retention for AWR data is only seven days. By using the new dbms package called dbms_workload_repository.modify_snapshot_settings., many Oracle DBAs will increase the storage of detail information over longer time periods. This will change the retention period and collection frequency, providing you with longer timer periods of data:

execute dbms_workload_repository.modify_snapshot_settings(

   interval  => 60,

   retention => 43200);

As you see, the retention period is indicated as 30 days (43200 min) while the interval between each snapshot is 60 min. You will see changes to these settings if you query the dba_hist_wr_control view after this procedure is executed.

The creation of AWR and ASH provides a complete repository for diagnosing and fixing any Oracle performance issue. The AWR provides the foundation for sophisticated performance analysis including exception reporting, trend analysis, correlation analysis, hypothesis testing and data mining.