Travellers Trail: Favorite SQL for session waits

Session waits


col "FileId# Obj#" for a18
col event for a42

select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state  
from gv$session s, gv$session_wait sw 
where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 order by s.inst_id, s.username

Other related, or common sql


-- Current Transaction
col "username_sid" for a35
select s.username||'('||s.sid||')' username_sid,s.sid,t.start_time,t.used_ublk as "Block Count", 
round(t.used_ublk*8192/1024/1024,2) Size_MB 
from v$transaction t,v$session s where s.saddr=t.ses_Addr order by start_time;


-- Long OPS
select sid, serial#, context, sofar, totalwork, target, sql_hash_value, 
round(sofar/totalwork*100,2) "%_COMPLETE", opname, to_char(start_time, 'DD Mon YYYY - HH24:MI') start_time, 
round(time_remaining/60) tr, to_char(last_update_time, 'DD Mon YYYY - HH24:MI') last_update_time 
from v$session_longops 
where totalwork != 0 and sofar <> totalwork 
order by start_time;

SQL from hash value, SQL hash


set line 200
select * from v$sql where hash_value = 9999999;

select sq.sql_text from v$sqltext_with_newlines sq 
where sq.hash_value = 9999999
order by sq.piece

SELECT s.username, sl.sid, sq.executions, sl.last_update_time, sl.sql_id, sl.sql_hash_value,
       opname, target, elapsed_seconds, time_remaining, sq.sql_fulltext
  FROM v$session_longops sl
 INNER JOIN v$sql sq ON sq.sql_id = sl.sql_id
 INNER JOIN v$session s ON sl.SID = s.SID AND sl.serial# = s.serial#
 WHERE time_remaining > 0

SQL from pid / session from Process Id / Session Id


set line 200
col schemaname for a20
col host_name for a20
col program for a16
col pprogram for a20
col machine for a20
col com for  a10

-- PID version
select p.spid, s.schemaname, s.sid, s.serial#, s.status, s.machine, s.program, 
   p.program pprogram, s.sql_hash_value, s.sql_id, s.prev_sql_id, 
   decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 
   'PL/SQL EXE', command) com , i.host_name, i.instance_name 
from v$session s, v$process p, v$instance i
where s.paddr = p.addr 
   and p.spid = 22844;

-- SID version
col "Sid_Ser#_SqlHash"for a24
select p.spid, s.schemaname, s.sid||','||s.serial#||' ['||s.sql_hash_value||']' as "Sid_Ser#_SqlHash", s.status, 
   s.machine, s.program, p.program pprogram, s.sql_id, s.prev_sql_id, 
   decode(command, 2, 'INSERT', 3, 'SELECT', 6, 'UPDATE', 7, 'DELETE', 47, 
   'PL/SQL EXE', command) com , i.host_name, i.instance_name 
from v$session s, v$process p, v$instance i
where s.paddr = p.addr 
   and s.sid = 2790;

Travellers Trail

Thursday 4 October 2018

Favorite SQL for session waits

No comments: