Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
   Oracle Tips by Burleson

Oracle10g Wait Event Model improvements

In this section we will explain the Wait Event Interface changes, updates, and improvements introduced in the 10g release. The wait event model has been steadily gaining ground as a good tuning tool. Originally introduced in Oracle 7, wait event analysis got the serious attention of many Remote DBAs in version 8 and was further improved in Oracle9i.

Overview of Wait Event Model

In a nutshell, the wait event interface provides insight into where time is consumed. Wait events are collected by the server process or thread to indicate the ‘wait’ before a process is completed. As we know, at any given moment an Oracle process is either busy servicing a request or waiting for something to happen. Oracle has defined a list of every possible event that an Oracle process could wait for.

The Wait Event Interface now provides a powerful tool to monitor the process delays. With its snapshot of the events and its detailed analysis, it becomes possible for database administrators to pinpoint areas that need tuning. Wait events show various symptoms of problems that impact performance. Examining and solving the resource crunch in those areas streamline database tuning.

Wait Event Enhancements

Oracle Database 10g introduces many new dynamic performance views and updates other views. General improvements include:

  • New columns in the v$session and v$session_wait views that track the resources sessions are waiting for.
  • A history of waits per session, enabling diagnosis of performance problems for a desired time frame and period.
  • Maintaining wait statistics of each SQL statement in the library cache
  • Histograms of wait durations, rather than a simple accumulated average

The following list shows the existing views that are modified.

Changes to v$event_name

CLASS# and CLASS columns are added. These columns help to group related events while analyzing the wait issues. For example, to list the events related to IO, use the statement,

SELECT name, class#, class FROM v$event_name
WHERE class# IN (10, 11);

In another example, to group all the events by class to get a quick idea of the performance issues, use the statement,

SELECT e.class#, sum(s.total_waits),
sum(s.time_waited)
FROM v$event_name e, v$system_event s WHERE e.name = s.event GROUP BY e.class#;

Changes to v$session

In the past, sessions experiencing waits were generally located by joining the v$session_wait view with the v$session view. To simplify the query, all the wait event columns from v$session_wait have been added to v$session.

Use the statement below to determine the wait events that involve the most sessions.

SELECT wait_class, count(username)
FROM v$session GROUP BY wait_class;

New columns have been added to v$sessions as follows:

SQL_CHILD_NUMBER, PREV_CHILD_NUMBER, BLOCKING_SESSION_STATUS, BLOCKING_SESSION, SEQ#, EVENT#, EVENT, WAIT_CLASS#, WAIT_CLASS, WAIT_TIME, SECONDS_IN_WAIT, STATE and SERVICE_NAME

Changes to v$session_wait

The new columns include wait_class# and wait_class.

The following list shows the views that are new.

  • v$system_wait_class – This view provides the instance-wide time totals for the number of waits and the time spent in each class of wait events. This view also shows the object number for which the session is waiting.
  • v$session_wait_class - This view provides the number of waits and the time spent in each class of wait event on a per session basis. This view also shows the object number for which the session is waiting.
  • v$event_histogram – This view displays a histogram of the number of waits, the maximum wait, and total wait time on a per-child cursor basis. Using this view, you can create a histogram showing the frequency of wait events for a range of durations. This information assists you in determining whether a wait event is a frequent problem that needs addressing or a unique event.
  • v$file_histogram – This view displays a histogram of all single block reads on a per-file basis. To provide more in-depth data, the v$file_histogram view shows the number of I/O wait events over a range of values. You use the histogram to determine if the bottleneck is a regular or a unique problem.
  • v$temp_histogram – This view displays a histogram of all single block reads on a per-tempfile basis.
  • v$session_wait_history – This view displays the last 10 wait events for each active session.

The new views above are quite helpful in understanding the overall health of the database. For example, use the v$system_wait_class view to display wait events occurring across the database.

SQL> SELECT wait_class#, wait_class,

  2  time_waited, total_waits

  3  FROM v$system_wait_class

  4  ORDER BY time_waited;

 

WAIT_CLASS# WAIT_CLASS       TIME_WAITED TOTAL_WAITS

----------- ---------------- ----------- -----------

          5 Commit                 10580       29404

          2 Configuration          25140        1479

          7 Network                28060    35111917

          4 Concurrency            34707       16754

          8 User I/O              308052      178647

          9 System I/O            794444     2516453

          1 Application          3781085    68100532

          0 Other               38342194       22317

          6 Idle               845197701    37411971

 

9 rows selected.
 


Get the complete Oracle10g story:

The above text is an excerpt from "Oracle Database 10g New Features: Oracle10g Reference for Advanced Tuning and Administration", by Rampant TechPress.  Written by top Oracle experts, this book has a complete online code deport with ready to use scripts. 

To get the code instantly, click here:

http://www.rampant-books.com/book_2003_2_oracle10g.htm


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.