SCRIPTS THAT REPORT SESSION STATISTICS
=================================================


SQLDBA Monitor screens get their information from the V$ tables in the
SGA. These tables can be queried directly to obtain this information
in a form that is sometimes more readable and easier to report.
Following are some SQL scripts that report session and system statistics.



1. Script to find the Session Id of the current session.

select s.sid sess_id
from v$process p, v$session s
where p.addr = s.paddr
and terminal = userenv('terminal');


2. Script to find the Oracle Session Id if the operating system
PID of the process is known.

select s.sid sess_id
from v$process p, v$session s
where p.addr = s.paddr
and p.spid = '&OS_PID';


3. Script to find statistic values for the current session.
Must be run from the same terminal as the session.

set numwidth 5
column orclname format a12 word_wrap
column sysname format a8 word_wrap
column name format a40 word_wrap

set recsep off
set pagesize 60

select st.sid sid, s.username orclname, p.username sysname,
n.name name, st.value value
from v$sesstat st, v$statname n, v$session s, v$process p
where st.statistic# = n.statistic#
and st.sid = s.sid
and s.paddr = p.addr
and p.terminal = userenv('terminal');


4. Script to find statistic values for the any session, if the operating
system PID of the process is known.

set numwidth 5
column orclname format a12 word_wrap
column sysname format a8 word_wrap
column name format a40 word_wrap

set recsep off
set pagesize 60
set verify off

select st.sid sid, s.username orclname, p.username sysname,
n.name name, st.value value
from v$sesstat st, v$statname n, v$session s, v$process p
where st.statistic# = n.statistic#
and st.sid = s.sid
and s.paddr = p.addr
and p.spid = '&OS_PID';


5. Script to find statistic values for the system.

column name format a40 word_wrap

set recsep off
set pagesize 60

select n.name name, st.value value
from v$sysstat st, v$statname n
where st.statistic# = n.statistic#;


The above scripts are written to be run from SQL*Plus. Exclude the
COLUMN and SET commands if you wish to run them from SQLDBA.