Bookmark and Share
Database Wait Statistics
(Publish Date: 2008-8-6 12:46pm, Total Visits: 1152, Today: 1, This Week: 1, This Month: 3)

1. Summary

High buffer cache hit ratios don't necessarily mean that the application is well tuned. All a high buffer cache hit ratio is telling you is that your physical reads are very small when compared to the total logical reads from the buffer cache. It's entirely possible for you to have a 99.0 buffer cache hit ratio and still have a database slowdown.

 

1. Summary

High buffer cache hit ratios don't necessarily mean that the application is well tuned. All a high buffer cache hit ratio is telling you is that your physical reads are very small when compared to the total logical reads from the buffer cache. It's entirely possible for you to have a 99.0 buffer cache hit ratio and still have a database slowdown.

The total response time for a query is the time Oracle takes to execute it plus the time the process spends waiting for resources such as latches, data buffers, and so on. For a database instance to perform well, ideally your application should spend very little time waiting for access to critical resources.

Response time = service time + wait time.

The three key dynamic performance tables for finding wait information are the V$SYSTEM_EVENT, V$SESSION_EVENT, and V$SESSION_WAIT views.

V$SYSTEM_EVENT -- shows the total time waited for all the events for the entire system since the instance started up.

V$SESSION_EVENT -- shows the total time waited per session. By querying this view, you can find out the specific bottlenecks encountered by each session.

V$SESSION_WAIT -- shows the current waits or just completed waits for sessions. The real-time information in this view provides you tremendous insight into what is holding up things in the database right now. The V$SESSION_WAIT view provides detailed information on the wait event, including details such as file number, latch numbers, and block number.

2. Steps to check the event waits

First, look at the V$SYSTEM_EVENT view and rank the top wait events by the total amount of time waited, as well as the average wait time for that event.

Next, find out more details about the specific wait event that's at the top of the list. For example, if the top event is buffer busy waits, look in the V$WAITSTAT view to see which buffer block is causing the busy buffer waits

Finally, use the V$SESSION_WAIT view to find out the exact objects that may be the source of a problem.

3. SQL statements:

SQL> select event, time_waited, average_wait 2 from v$system_event 3 group by event, time_waited, average_wait 4* order by time_waited desc;

SQL> select s.username, t.sql_text, w.event from v$session s, v$sqltext t, v$session_wait w where s.sql_hash_value = t.hash_value and s.sql_address = t.address and s.type <> 'BACKGROUND' and s.sid = w.sid order by s.sid,t.hash_value,t.piece;

-- Instance-wide Waits Sorted by Total Wait Time

SQL> select event, total_waits,time_waited from V$system_event 2 where event NOT IN 3 ('pmon timer', 'smon timer', 'rdbms ipc reply', 'parallel deque wait', 4 'virtual circuit', '%SQL*Net%', 'client message', 'NULL event') 5* order by time_waited desc;