 |
 |
 |
 |
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
 |
|||
|
|||
 |
|||
|
|||
 |
|||
|
|||
 |
Show current events within Oracle9i
Donald K. Burleson |
Oracle event wait analysis is an extremely sophisticated part of Oracle system tuning. In order to be effective, an Oracle wait analysis must interrogate v$system_event to look at system events that are currently happening within your system. Excessive waits on system events can indicate disk I/O bottlenecks, network bottlenecks, or additional external environmental problems that are causing slowdowns within your Oracle database.
Below is the script that is commonly used by senior Oracle consultants in order to take a look at current events within any Oracle instance.
This query selects and aggregates by event name and counts the total number of waits as well as the total time waited for each system event. Running the query against an Oracle database that is experiencing performance problems will give you very good insight into the causes of external event problems.
set pages 999
set lines 90
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
;
This is enhanced in Oracle 10g. This view stores snapshots of the v$system_event system dynamic view. The following sys_event_int_10g.sql query can be used to retrieve wait events data for a particular snapshot interval:
< sys_event_int_10g.sql
waits "Waits",
timeouts "Timeouts",
time "Wait Time (s)",
avgwait "Avg Wait (ms)",
waitclass "Wait Class"
from
(select e.event_name event
, e.total_waits - nvl(b.total_waits,0) waits
, e.total_timeouts - nvl(b.total_timeouts,0) timeouts
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time
, decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL),
((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait
, e.wait_class waitclass
from
dba_hist_system_event b ,
dba_hist_system_event e
where
b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.event_id(+) = e.event_id and e.total_waits > nvl(b.total_waits,0) and e.wait_class <> 'Idle' ) order by time desc, waits desc
The sample output for this query looks like following:
SQL> @ Sys_event_int_10g.sql Event Name Waits Timeouts Wait Time (s) Avg Wait (ms) Wait Class ---------------------------- ----- -------- ------------- ------------- ---------- control file parallel write 11719 0 119.13 10.17 System I/O class slave wait 20 20 102.46 5,122.91 Other Queue Monitor Task Wait 74 0 66.74 901.86 Other log file sync 733 6 20.60 28.11 Commit db file sequential read 1403 0 14.27 10.17 User I/O log buffer space 178 0 10.17 57.16 Configuration process startup 114 0 7.65 67.07 Other db file scattered read 311 0 2.14 6.87 User I/O control file sequential read 7906 0 1.33 .17 System I/O latch free 254 0 1.13 4.45 Other log file switch completion 20 0 1.11 55.67 Configuration
For more scripts, see my latest book “Oracle Tuning: The Definitive Reference"
|
|
Copyright © 1996 - 2009 by
Burleson Enterprises, Inc. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.