-- #############################################################################################
--
-- %Purpose: Show Number of Transactions and other Cursor Statistics (Commits, Rollbacks, etc)
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
column  STATISTIC#  format 999          heading 'Id'
column  NA          format a32          heading 'Statistic'
column  PPE         format 99990.90
column  NR_TX       format 99999990
column  NR_RECC     format 99990.90

ttitle  left 'Monitor Cursor Activites' skip 2

spool   cursor_activites.log
set     termout on

select  rpad (NAME, 32, '.') as NA,
        VALUE
  from  V$SYSSTAT
 where  NAME like '%cursor%'
    or  NAME in ('parse count',
                 'execute count',
                 'user calls',
                 'user commits',
                 'user rollbacks',
                 'parse time cpu',
                 'parse time elapsed',
                 'recursive calls')
/

ttitle  left 'Number of Transactions' skip 2

select  A.VALUE + B.VALUE  as NR_TX
  from  V$SYSSTAT A,
        V$SYSSTAT B
 where  A.NAME = 'user commits'
   and  B.NAME = 'user rollbacks'
/

ttitle  left 'Recursive call Per user call' skip 2

select  C.VALUE / D.VALUE  as NR_RECC
  from  V$SYSSTAT C,
        V$SYSSTAT D
 where  C.NAME = 'recursive calls'
   and  D.NAME = 'user calls'
/

ttitle  left 'Parse per Execute [%]' skip 2

select  E.VALUE / F.VALUE * 100.0 as PPE
  from  V$SYSSTAT E,
        V$SYSSTAT F
 where  E.NAME = 'parse count'
   and  F.NAME = 'execute count'
/

spool off
ttitle off