Online Contents
SQL Scripts‎ > ‎S-Z‎ > ‎

session

set echo off

---------------------------------------------
-- @name: session
-- @author: Dion Cho
-- @description: detailed snapshot info for given session
-- @usage:
--       @session 147
--       @session 147,155
---------------------------------------------

define __SID = &1;

col process format a15
col machine format a15
col program format a15
col event format a30
col spid format 99999  
col operation_type format a20

set serveroutput on
set verify off

prompt 01. basic session info
begin
  print_table('
    select s.sid, s.serial#, p.spid, s.machine, s.program,
          (select value from v$sesstat where sid = s.sid and
              statistic# = n1.statistic#) as pga,
          (select value from v$sesstat where sid = s.sid and
              statistic# = n2.statistic#) as uga,
          last_call_et,
          logon_time
    from v$session s, v$statname n1, v$statname n2, v$process p
    where sid in (&__SID) and
        n1.name = ''session pga memory'' and
        n2.name = ''session uga memory'' and
        s.paddr = p.addr
  ');

end;
/

prompt 02. session wait
begin
  print_table('
    select sid, event, p1, p1raw, p2, p2raw, p3, p3raw,
          seconds_in_wait, state
    from v$session_wait
    where sid in (&__SID)
  ');
end;
/

prompt 03. process info
begin
        print_table('
            select pid, program, pga_used_mem, pga_alloc_mem, pga_max_mem
            from v$process
            where addr = (select paddr from v$session where sid in (&__SID))
        ');
end;
/

prompt 04. sql info
begin
  print_table('
    select
        (select min(sid) from v$session where sql_address = s.address) as sid,
        sharable_mem, persistent_mem, runtime_mem, executions,
        fetches, buffer_gets,
        sql_text
    from v$sql s
    where address = (select sql_address from v$session where sid in (&__SID))
  ');
end;
/


prompt 05. sql plan info

select
  p.plan_table_output
from
  v$sql s,
  table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'typical')) p
where
 s.address = (select sql_address from v$session where sid in (&__SID))
;


 
prompt 06. workarea info

begin
  print_table('
    select sid, operation_type, active_time, work_area_size, actual_mem_used,
      max_mem_used
    from v$sql_workarea_active
    where sid in (&__SID)
  ');
end;
/


prompt 07. transaction info

begin
  print_table('
    select addr, xidusn, xidslot, xidsqn,
      ubafil, ubablk, ubasqn,
      ubarec
    from
      v$transaction
    where
      addr = (select taddr from v$session where sid in (&__SID))
    ');
end;
/

set echo on
set serveroutput off
set verify on