Oracle Wait Event Table tips

Prior to Oracle10g, capturing wait event information was a cumbersome process involving the setting of special events (e.g. 10046) and the reading of complex trace dumps. Fortunately, Oracle10g has simplified the way that wait event information is captured and there are a wealth of new v$ and wrh$ views relating to Oracle wait events.

Oracle10g has brand-new wait events and the database kernel now captures statistics on more than 800 specific wait events. These new wait events are the result of Oracle breaking-out their latch waits into their individual components and breaking-out enqueue waits (locks) into a finer level of granularity.

The foundation concept of the ASH architecture is called the "time model", and Oracle10g has introduced several important new wait event v$ views.

V$ View                  DBA_HIST View

v$active_sess_hist       dba_hist_active_sess_history 

v$sys_time_model         dba_hist_sys_time_model

v$active_session_history dba_hist_active_sess_history 

v$event_histogram        No equivalent DBA view

Unlike the old-fashioned v$session and v$session_wait views (where you could only see waits at the exact instant when they occurred), the new v$session_wait_history and v$sys_time_model views allow Oracle10g to capture system waits details in a time-series mode. But wait, there's more! Now let's look at the new ASH tables structures.

What a great ASH

One of the most important area of Oracle10g wait event tuning is the Oracle10g Active Session History (ASH). ASH data is visualized through the v$active_sess_hist view and the wrh$active_session_history tables.

At a basic level, ASH stores the history of a recent session's activity and facilitates the analysis of the system performance at the current time. ASH is designed as a rolling buffer in memory, and earlier information is overwritten when needed. ASH uses the memory of the SGA.

Another new innovation is the ability to use the new Oracle10g hash key for tracking session identification. This new hash key allows you to tracks common session processes and allows inter-cal session tracking in cases like OCI session 'bouncing' where each call to Oracle is a different session ID.

As we have already noted, the ASH samples for wait events every second and tracks the waits in the new v$active_sess_hist view. New data values are written to the wrh$ tables every hour, or when a new AWR snapshot is taken. In listing 203 below we see the 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

Now, let's move away from the wrh$ tables and explore the new Oracle10g dba_hist views that are used to create time-series performance reports, both manually and within Enterprise Manager. We will begin with an overview of the dba_hist views and then show you examples of custom Oracle10g performance exception reports that can be easily generated from these views with SQL*Plus. For more details, note that these views are fully documented in the Oracle 10g Database Reference Manual.

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.

Conclusion

Once we understand the AWR table data and inter-table relationships between AWR and performance metrics, we will be ready to understand how the WRH$ tables are used as input to the Automatic Memory Manager (AMM), the Automatic Database Diagnostic Monitor (ADDM), and the SQL Tuning Advisor.

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.