Subject:            SQL: SCRIPTS THAT REPORT SESSION STATISTICS
Last Revision Date: 09 January   1996
Author:Saleem Haque                                                                



        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.