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
              substr(nvl(s.username,substr(s.program,-8)),1,10) username
,
              substr(to_char(s.sid),1,5) sid,
              substr(to_char(s.serial#),1,8) serial,
              s.sql_hash_value hash_value,
              substr(decode(w.wait_time,
                     0, w.event,
                    'ON CPU'),1,20) status,
              substr(decode(w.event, 'enqueue',
       chr(to_number(substr(ltrim(w.p1raw,0),1,2),'XX'))||
       chr(to_number(substr(ltrim(w.p1raw,0),3,2),'XX'))||
       ' '||
       substr(w.p1raw,-1,1),
              w.p1),1,8) p1,
              substr(w.p2,1,8) p2,
              substr(w.p3,1,4) p3
from       v$session            s,
           v$session_wait   w
where     w.sid=s.sid
        and s.status='ACTIVE'
        and s.type='USER'
        and w.event not in ('jobq slave wait',
                                         'rdbms ipc reply')
        and s.sid != ( select sid from v$mystat where rownum < 2 )
order by w.event;
select * from waiters;