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 Wait Event Table tips

Oracle Tips by Burleson Consulting
August 16,  2009

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.



 

 

��  
 
 
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