Sign in or 
Share this
Determine Wait Events for Sessions Attached to OLAP Multidimensional Datatypes
set lines 110 pages 500
col awuser format a25
col awnm format a20
col event format a25
col session_state format a8
select username||' ('||sid||','||serial#||')' as awuser, owner||'.'||aw_name||'
('||decode(attach_mode, 'READ WRITE', 'RW', 'READ ONLY', 'RO',
'MULTIWRITE', 'MW', 'EXCLUSIVE', 'XW', attach_mode)||')' awnm,
decode(session_state, 'WAITING', a.event, null) event, session_state, count(*) cnt,
sum(time_waited) waited from v$active_session_history a, v$session b, v$aw_olap c, dba_aws d
where sid=a.session_id and sid=c.session_id and
sample_time > sysdate-(1/24) and c.aw_number=d.aw_number
group by username, sid, serial#, owner, aw_name, attach_mode,
decode(session_state, 'WAITING', a.event, null), session_state;
USER (SID/SERIAL#)..OWNER.AW (ATTACH).. WAIT EVENT..................STATE CNT..WAITED
------------------- ------------------- ----------------------- --------- --- -------
GLOBAL (272,58906)..SYS.AWXML (RO)................................ ON CPU..18...... 0
GLOBAL (272,58906)..SYS.AWXML (RO)......latch free................WAITING.. 1..123692
GLOBAL (272,58906)..SYS.AWXML (RO)......null event................WAITING.. 1...... 6
GLOBAL (272,58906)..SYS.AWXML (RO)......db file sequential read.. WAITING.. 5..275895
GLOBAL (272,58906)..SYS.EXPRESS (RO).............................. ON CPU..18...... 0
GLOBAL (272,58906)..SYS.EXPRESS (RO)....latch free................WAITING.. 1..123692
GLOBAL (272,58906)..SYS.EXPRESS (RO)....null event................WAITING.. 1...... 6
GLOBAL (272,58906)..SYS.EXPRESS (RO)....db file sequential read.. WAITING.. 5..275895
GLOBAL (272,58906)..GLOBAL.VM01 (RW).............................. ON CPU..18...... 0
GLOBAL (272,58906)..GLOBAL.VM01 (RW)....latch free................WAITING.. 1..123692
GLOBAL (272,58906)..GLOBAL.VM01 (RW)....null event................WAITING.. 1...... 6
GLOBAL (272,58906)..GLOBAL.VM01 (RW)....db file sequential read.. WAITING.. 5..275895
SYSTEM (240,53659)..SYS.AWXML (RO)................................ ON CPU.. 4...... 0
SYSTEM (240,53659)..SYS.AWXML (RO)......db file sequential read.. WAITING.. 3.. 12248
SYSTEM (240,53659)..SYS.EXPRESS (RO).............................. ON CPU.. 4...... 0
SYSTEM (240,53659)..SYS.EXPRESS (RO)....db file sequential read.. WAITING.. 3.. 12248
SYSTEM (240,53659)..GLOBAL.GLOBAL (RW)............................ ON CPU.. 4...... 0
SYSTEM (240,53659)..GLOBAL.GLOBAL (RW)..db file sequential read.. WAITING.. 3.. 12248
col awuser format a25
col awnm format a20
col event format a25
col session_state format a8
select username||' ('||sid||','||serial#||')' as awuser, owner||'.'||aw_name||'
('||decode(attach_mode, 'READ WRITE', 'RW', 'READ ONLY', 'RO',
'MULTIWRITE', 'MW', 'EXCLUSIVE', 'XW', attach_mode)||')' awnm,
decode(session_state, 'WAITING', a.event, null) event, session_state, count(*) cnt,
sum(time_waited) waited from v$active_session_history a, v$session b, v$aw_olap c, dba_aws d
where sid=a.session_id and sid=c.session_id and
sample_time > sysdate-(1/24) and c.aw_number=d.aw_number
group by username, sid, serial#, owner, aw_name, attach_mode,
decode(session_state, 'WAITING', a.event, null), session_state;
USER (SID/SERIAL#)..OWNER.AW (ATTACH).. WAIT EVENT..................STATE CNT..WAITED
------------------- ------------------- ----------------------- --------- --- -------
GLOBAL (272,58906)..SYS.AWXML (RO)................................ ON CPU..18...... 0
GLOBAL (272,58906)..SYS.AWXML (RO)......latch free................WAITING.. 1..123692
GLOBAL (272,58906)..SYS.AWXML (RO)......null event................WAITING.. 1...... 6
GLOBAL (272,58906)..SYS.AWXML (RO)......db file sequential read.. WAITING.. 5..275895
GLOBAL (272,58906)..SYS.EXPRESS (RO).............................. ON CPU..18...... 0
GLOBAL (272,58906)..SYS.EXPRESS (RO)....latch free................WAITING.. 1..123692
GLOBAL (272,58906)..SYS.EXPRESS (RO)....null event................WAITING.. 1...... 6
GLOBAL (272,58906)..SYS.EXPRESS (RO)....db file sequential read.. WAITING.. 5..275895
GLOBAL (272,58906)..GLOBAL.VM01 (RW).............................. ON CPU..18...... 0
GLOBAL (272,58906)..GLOBAL.VM01 (RW)....latch free................WAITING.. 1..123692
GLOBAL (272,58906)..GLOBAL.VM01 (RW)....null event................WAITING.. 1...... 6
GLOBAL (272,58906)..GLOBAL.VM01 (RW)....db file sequential read.. WAITING.. 5..275895
SYSTEM (240,53659)..SYS.AWXML (RO)................................ ON CPU.. 4...... 0
SYSTEM (240,53659)..SYS.AWXML (RO)......db file sequential read.. WAITING.. 3.. 12248
SYSTEM (240,53659)..SYS.EXPRESS (RO).............................. ON CPU.. 4...... 0
SYSTEM (240,53659)..SYS.EXPRESS (RO)....db file sequential read.. WAITING.. 3.. 12248
SYSTEM (240,53659)..GLOBAL.GLOBAL (RW)............................ ON CPU.. 4...... 0
SYSTEM (240,53659)..GLOBAL.GLOBAL (RW)..db file sequential read.. WAITING.. 3.. 12248
olapdba |
Latest page update: made by olapdba
, Feb 19 2008, 12:43 PM EST
(about this update
About This Update
Edited by olapdba
view changes - complete history) |
|
Keyword tags:
None
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.
