Oracle_tuning_SQL/system_event.sql at master · arasmuc/Oracle_tuning_SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- %D%
-- %D%  system_event.sql  - shows current events within Oracle instance
-- %D%  example:  @system_event.sql
-- %D%  date: 12.10.2015
-- %D%  query against V$SYSTEM_EVENT
-- %D%  version 1.0
-- %D%

SET pages 3000
SET lines  1200

prompt ************************************************************************
prompt ********         Current wait events within Oracle instance
prompt ************************************************************************


column ev   heading 'Event' format 99999999D9
column tw   heading 'Total|Waits' format 999999999
column tiw  heading 'Time|Waited' format 999999999
column to   heading 'Total Timeouts' format 99999999999
column aw   heading 'Average Waits' format 9999999999
column wt   heading 'Wait Time' format 99999D99
column wc   heading 'Wait Class' format 9999999D9
column pro  heading 'Total %' format 999999D999

select * from (
select event as "ev", total_waits as "tw", time_waited as "tiw", total_timeouts as "to",
average_wait as "aw", total_waits*100/(select sum(total_waits) from v$system_event where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'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 '%Idle%'
and event not like '%done%') as "pro",
wait_class as "wc"
from v$system_event where
event not in (
'dispatcher timer',
'lock element cleanup',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'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 '%idle%'
and event not like '%done%'
order by total_waits desc)
where ROWNUM < 31;