ash_sid.sql

-- Authored by Prashant The FatDBA 
---
undef sid
undef last_x_min

set lines 200 echo off
col sess	format a10
col stime	format a08
col sta  	format a04	head 'Sta'
col event	format a24 trunc
col seq#	format 99999
col wtime	format 9999999
col twaited	format 99999999
col p1p2p3	format a18	head '        P1:P2:P3'
col wclass	format a11 			trunc
col sqlid_cn	format a16	head 'SqlId:ChildNo'
col blk		format a01	head 'X'
col oc		format 999
col cn		format 99
col flags	format a12	head 'CPH S PRXJBZ'
col mod		format a20	head 'Module'	trunc
col sqlstart	format a05	head 'SQL|Start'
col sqlxid	format 99999999 head 'SqlExecId'
col sidser	format a15  		head 'Sid,Serial<Blk'
col dur		format 999999		head 'Dur|ms'
col cpu		format 9999		head 'CPU|ms'
col dbtime	format 999999		head 'DB Time|ms'

break on sidser skip 1 on mod on sqlid_cn on sqlxid on sqlstart
select 	 lpad(session_id,4,' ')||','||lpad(session_serial#,5,' ')||decode(substr(blocking_session_status,1,1),'V','<',' ')||lpad(blocking_session,4,' ')	sidser
	,to_char(sample_time, 'HH24:MI:SS') 	stime
	,module					mod
	,sql_id||decode(sql_child_number,-1,'   ',':'||lpad(sql_child_number,2,' '))	sqlid_cn
	,sql_exec_id				sqlxid
	,to_char(sql_exec_start,'MI:SS')	sqlstart
	,in_connection_mgmt||in_parse||in_hard_parse||' '||in_sql_execution||' '||in_plsql_execution||in_plsql_rpc||in_java_execution||in_bind||in_cursor_close 	flags
	,decode(session_state,'WAITING','Wait','ON CPU','cpu',' ? ')		sta
	,seq#					seq#
	,event					event
	,lpad(least(p1,9999999),7,' ')||':'||lpad(least(p2,999999),6,' ')||':'||lpad(least(p3,9),3,' ') p1p2p3
	,wait_class				wclass
	,tm_delta_time/1000			dur
	,tm_delta_cpu_time/1000			cpu
	,tm_delta_db_time/1000			dbtime
from	 v$active_session_history
where	 session_id = &&sid
and	 sample_time between sysdate-70/1440 and sysdate-30/1440
order by sample_time
;