SQL Script

Monitor User

Updated: 01-Oct-2002
Version: 1.2

Description

This script reports session statistics and what queries the current user has open. This will help you to debug specific user problems

Parameters

User - Username who you wish to monitor
SID - Filter on the SID to reports statistics for a session
stat - Filter on statistics containing this string

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set verify off

spool monuser.lst

column name form a60
column sid form 999
column serial# form 999999 Heading "Serial"
column logon_time format a15
column program format a22
column username format a12
column idle_mins format 99999 Heading "Idle"
column object format a30

PROMPT
PROMPT Displaying All non background sessions
PROMPT

select sid,serial#,username,program,status
,to_char(logon_time,'DD-MON-RR hh24:mi') logon_time
,trunc(last_call_et/60) idle_mins
from v$session
where username is not null;

ACCEPT user PROMPT 'Enter Value for username > '

PROMPT 
PROMPT User is running the following queries
select oc.sid, t.sql_text 
from v$open_cursor oc, v$sqltext t
where t.hash_value = oc.hash_value and t.address=oc.address
and oc.user_name=UPPER('&user') 
order by oc.sid, t.address, t.hash_value, t.piece
/

ACCEPT sid PROMPT 'Enter sid to refine (return for all) :'
ACCEPT stat PROMPT 'Filter on statistics (return for all) :'

select s.sid, sn.name, s.value
from v$sesstat s, v$statname sn, v$session sess
where s.sid=sess.sid
and s.statistic# = sn.statistic#
and sess.username= UPPER('&user')
and s.sid LIKE '&sid%'
and sn.name like '&stat%'
and s.value != 0
order by s.sid, sn.name
/                      

PROMPT
PROMPT Selecting V$SESSION_WAIT
PROMPT
column sid format 990
column seq# format 99990
column wait_time heading 'WTime' format 99990
column event format a30
column state forma a10 heading "State"
column seconds_in_wait form 999,999 heading secsinwait
column p1 format 9999999990
column p2 format 9999999990
column p3 format 9990

select event,p1,p2,p3,wait_time,seconds_in_wait
from v$session_wait
where sid = '&sid';

PROMPT
PROMPT Selecting V$SESSION_EVENT
PROMPT
select event,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED,AVERAGE_WAIT,  MAX_WAIT
from v$session_event where sid = '&sid';


PROMPT
PROMPT Selecting V$SESS_IO
PROMPT
select *
from v$sess_io where sid = '&sid';


spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Monitor User

Updated: 01-Oct-2002Version: 1.2

Description

Parameters

SQL Source

Site Map

Updated: 01-Oct-2002
Version: 1.2