Oracle Tuning Waits Related

Explanation of Waits: SQL> desc v$system_event; Name -----------------------EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT TIME_WAITED_MICRO v$system_event This view displays the count (total_waits) of all wait events since startup of the instance.If timed_statistics is set to true, the sum of the wait times for all events are also displayed in the column time_waited. The unit of time_waited is one hundreth of a second. Since 10g, an additional column (time_waited_micro) measures wait times in millionth of a second. total_waits where event='buffer busy waits' is equal the sum of count in v$waitstat. v$enqueue_stat can be used to break down waits on the enqueue wait event. While this view totals all events in an instance, v$session select event, total_waits, time_waited from v$system_event where event like '%file%' Order by total_waits desc; column c1 heading 'Event|Name' format a30 column c2 heading 'Total|Waits' format 999,999,999 column c3 heading 'Seconds|Waiting' format 999,999 column c4 heading 'Total|Timeouts' format 999,999,999 column c5 heading 'Average|Wait|(in secs)' format 99.999 ttitle 'System-wide Wait Analysis|for current wait events' select event c1, total_waits c2, time_waited / 100 c3, total_timeouts c4, average_wait /100 c5 from sys.v_$system_event where event not in ( 'dispatcher timer', 'lock element cleanup', 'Null event', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get', 'PL/SQL lock timer', 'pmon timer', 'rdbms ipc message', 'slave wait', 'smon timer', 'SQL*Net break/reset to client', 'SQL*Net message from client',

'SQL*Net message to client', 'SQL*Net more data to client', 'virtual circuit status', 'WMON goes to sleep' ) AND event not like 'DFS%' and event not like '%done%'and event not like '%Idle%' AND event not like 'KXFX%' order by c2 desc; Create table beg_system_event as select * from v$system_event Run workload through system or user task Create table end_system_event as select * from v$system_event Issue SQL to determine true wait events drop table beg_system_event; drop table end_system_event; SELECT b.event, (e.total_waits - b.total_waits) total_waits, (e.total_timeouts - b.total_timeouts) total_timeouts, (e.time_waited - b.time_waited) time_waited FROM beg_system_event b, end_system_event e WHERE b.event = e.event; Cumulative info, after startup: ENQUEUE SELECT * FROM v$system_event WHERE event = 'enqueue'; SELECT * FROM v$sysstat WHERE class=4; LATCH select c.name,a.addr,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,a.wait_time, b.pid from v$latch a, v$latchholder b, v$latchname c where a.addr = b.laddr(+) and a.latch# = c.latch# order by a.latch#;