Ash waiters.sql

-- (c) Kyle Hailey 2007

clear breaks
break on sample_time

--drop view waiters;
-- for equeues
-- trim left zeros, 64 bits have all those leading 0s
-- substring out each to digit code for an ascii letter
-- put that value in decimal from heax
-- use chr() to get the ascci letter
create view waiters as
select
             to_char(sample_time,'MON-DD HH:MI:SS') sample_time,
             substr(username,0,10) username,
             substr(to_char(a.session_id),1,5) sid,
             substr(to_char(a.session_serial#),1,8) serial,
             a.sql_id hash_value,
             substr(decode(a.session_state,
                    'WAITING', n.name,
                    'ON CPU'),1,20) status
--          ,substr(decode(n.name, 'enqueue',
--   chr(to_number(substr(ltrim(to_char(a.p1,'XXXXXXXX')),1,2),'XX'))||
--   chr(to_number(substr(ltrim(to_char(a.p1,'XXXXXXXX')),3,2),'XX'))||
--   ' '||
--   substr(to_char(a.p1,'XXXXXXXX'),-1,1), a.p1),1,8) p1,
--            substr(a.p2,1,8) p2,
--            substr(a.p3,1,4) p3
from
          sash              a,
          sash_event_names   n,
          sash_users    u
where     n.event#=a.event#
    and  sample_time > sysdate - .2/(24*60)
    and  u.user_id = a.user_id
order by sample_time , sid
/
select * from waiters;

clear breaks