select /*+ RULE CUSROR_SHARING_EXACT */
substrb(vs.username,1,10) db_user,
substrb(to_char(vs.sid),1,4) sid,
decode(bitand(vt.flag,4194304),0,'LOCAL','REMOTE') "LOCAL",
decode(bitand(vt.flag,268435456),0,'RDC','SRL') "ISO",
substrb(to_char(vt.log_io+vt.phy_io,'999,999,999'),2,7) totl_io,
substrb(to_char(vt.log_io/greatest((vt.log_io+vt.phy_io),1)*100,'999.99') ||'%',2,7) hit_rt,
to_char(nvl(vt.used_ublk,0),'999,999') undo,
lpad(substrb(decode(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),0,
to_char(to_date(trunc(86400*abs(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS'))),'SSSSS'),'HH24:MI:SS'),
to_char(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),'B999999')),1,10),8) "ST-D/HMS",
vs.sql_address sql_addr
from v$session vs, v$transaction vt
where vs.type != 'BACKGROUND'
and vs.taddr = vt.addr
order by 5 desc, 3 desc
/
substrb(vs.username,1,10) db_user,
substrb(to_char(vs.sid),1,4) sid,
decode(bitand(vt.flag,4194304),0,'LOCAL','REMOTE') "LOCAL",
decode(bitand(vt.flag,268435456),0,'RDC','SRL') "ISO",
substrb(to_char(vt.log_io+vt.phy_io,'999,999,999'),2,7) totl_io,
substrb(to_char(vt.log_io/greatest((vt.log_io+vt.phy_io),1)*100,'999.99') ||'%',2,7) hit_rt,
to_char(nvl(vt.used_ublk,0),'999,999') undo,
lpad(substrb(decode(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),0,
to_char(to_date(trunc(86400*abs(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS'))),'SSSSS'),'HH24:MI:SS'),
to_char(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),'B999999')),1,10),8) "ST-D/HMS",
vs.sql_address sql_addr
from v$session vs, v$transaction vt
where vs.type != 'BACKGROUND'
and vs.taddr = vt.addr
order by 5 desc, 3 desc
/
No comments:
Post a Comment