#!/usr/bin/ksh
#set -x
column_name_long=48
A_USERNAME=a15
USR_NOT_NULL=" and s.username != 'NULL'"
HOSTNAME=`uname -n`
ACTION=DEFAULT
ROWNUM=30000
function help {
cat <<EOF

    Show sessions in system

       sl [-b]        # Info on all sessions. -b include background sessions
       sl SID         # Show info for one session
       sl -l [<x>]    # List summary stats. Optionaly order by [<x>]    p : hard parse     c : sort by CPU       r : physical read
                                                                        b : block change   g : gets             cc : consistent change
       sl -r          # session actually running an sql
       sl -i  <nn>    # program column length is set to <nn>           |      sl -m        # display machine name instead of last activity  
       sl -b  <user>  # limit selection to session owned by <username> |      sl -o        # list under a short form
       sl -siz        # show session memory size                       |      sl -lw       # active session locking and waits (10g)
       sl -c          # show cpu and db time                           |      sl -ash [nn] # show waits from active session history.
       sl -t  <SID>   # show events impact breakdown on session        |                   # default is last 60 minutes
       sl -e          # show session stats, waits and events           |      sl -f        # Failed over sessions
       sl -io <SID>   # Show session IO                                |      sl -cpu      # Report Response time breakdown
       sl -sup <SID> <r|s>  # Suspend a session (freeze) s=suspend r=resume
	                    # Example : sl sup 45 s  -->suspend session 45  #    sl sup 45 r  -->resume session 45
Dumps:
        sl -dump <SID> [ -pga |-pga_adr <adr1> <ard2> | -st ] # Dump session PGA stack, protion of PGA (adr) or Session process state
        sl -rf <trace_file>  -pga     # read dump of pga 

Additionals:
          -rac       # target GV\$ instead of V\$
          -len       # len of the column program
          -u <USER>  #  Limit Selection to user

 Example : sl -l r -rn 20       # list all session order by disk reads, limits to 20 first rows
 -----------------------     
 Snapper implementation:
 -----------------------     
        sl  -p <SID> -s <secs> -n <nn> -g [t|s|l|w|e] -ulike <USER> -plike <PROGRAM>

           -g        # option on snaper, can be cumulated :
                          s - Session Statistics from v$sesstat
                          t - Session Time model info from v$sess_time_model
                          w - Session Wait statistics from v$session_event and v$session_wait
                          l - instance Latch get statistics ( gets + immediate_gets )
                          e - instance Enqueue lock get statistics

          sl -p 155 -s 1 -g t -g w 

         

EOF
exit
}
FIELD7="s.last_call_et ssa"
FIELD4="s.serial#,"
typeset -u FTYPE
SEP=
while [ -n "$1" ]
do
  case $1 in
       -a ) ACTION=ASH; 
            if [ -z "$2" ];then
               MIN=60
            else
               MIN=$2; shift
            fi ;;
       -b ) unset USR_NOT_NULL ; A_USERNAME=a25 ; column_name_long=32;;
       -c ) ACTION="DB_CPU" ;;
       -f ) ACTION="SLF" ;;
      -io ) ACTION="SLIO"; SID=$2 ; shift ;;
     -cpu ) ACTION=CPU ;;
    -dump ) ACTION=DUMP ;  SID=$2 ; shift ;;
      -st ) DUMP_VAR=ST ;;
     -pga ) DUMP_VAR=PGA ;;
 -pga_adr ) DUMP_VAR=PGA_ADR ; START_ADR=$2 ; END_ADR=$3; shift; shift ;;
       -g ) GATHER="$GATHER$2" ;  shift ;;
       -i ) column_name_long=41 ;;
       -l )  ACTION=SLH  ; 
             if [ -n "$2" ];then
                case "$2" in
                 p ) ORDER=" order by t2.value desc" ; shift  ;;
                 c ) ORDER=" order by t.value desc"  ; shift ;;
                 r ) ORDER=" order by physical_reads desc"  ; shift ;;
                 g ) ORDER=" order by consistent_gets desc"  ; shift ;;
                 cc) ORDER=" order by consistent_changes desc"  ; shift ;;
                 b ) ORDER=" order by block_changes desc"  ; shift ;;
                esac
              fi
             ;;
     -len ) column_name_long=$1 ;;
      -lw ) ACTION="LOCK_WAIT" ;;
       -n ) SNAP_PAR3=$2 ; shift ;;
       -o ) ACTION=OVERVIEW;;
       -m ) FIELD7="machine " ; FIELD4=service_name,;;
       -p ) ACTION=SNAPER ; SID=$2; shift   ;;
    -plike) PLIKE=$2 ;shift ;;
       -r ) ACTION="RUNNING" ;;
      -rf ) ACTION=READ_DUMP_PGA ; TRC=$2 ; shift ;;
      -rn ) ROWNUM=$2 ; shift ;;
     -rac ) G=G ; unset FIELD4; EXIT=exit; INST_ID='s.inst_id,';;
       -s ) SNAP_PAR2=$2 ; shift ;;
     -siz ) ACTION=PSIZ 
            if [ -n "$2" ];then
               AND_SESSIONS=" and sid in ($2)"
               shift ;
            fi ;;
     -sup ) ACTION=SUSPEND; SID=$2 ; STYPE=$3; shift ;  shift ;;
       -t ) ACTION="EV_CPU"  ; SID=$2 ; shift;;
       -u ) AND_USER="  s.username = '$2' and " ; shift ;;
    -ulike) ULIKE=$2 ;shift ;;
       -v ) set -xv ;;
       -h ) help;;
        * ) SID=$1  ; AND_SID=" and s.sid = $1" ; ACTION=SLH ;;
  esac
  shift
done
. $SBIN/scripts/passwd.env
. ${GET_PASSWD} $S_USER $ORACLE_SID
if [  "x-$CONNECT_STRING" = "x-" ];then
   echo "could no get a the password of $S_USER"
   exit 0
fi

# ------------------------------------------------------------------------------
#     Report Response time breakdown
# ------------------------------------------------------------------------------
if [ "$ACTION" = "SUSPEND" ];then
    if [ -n "$STYPE" ];then
          if [ "$STYPE" = 's' ];then
                FACTION=suspend
          elif [ "$STYPE"  = 'r' ];then
                FACTION=resume
          else
                echo "$TYPE should be 's' or 'r' : Invalid suspend/resume action"
                exit
          fi
     else
        echo "No suspend/resume action"
        exit
     fi
                
echo $NN "MACHINE $HOST - ORACLE_SID : $ORACLE_SID $NC" 
sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set pagesize 66  linesize 80  heading off  embedded off pause off  termout on  verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||rpad(USER  ,15) || 'Suspend session - Session SID to suspend : $TO_SUSP '
from sys.dual
/
rem set pages 0 feed off echo off
set embedded on
rem prompt *----------------------------------------------------*
set lines 200
set heading on pause off
column "USERNAME" format a12
column "terminal" format a6
column "OSUSER" format a12
column "sess" format 9,999 heading "Sess|ID" justify c
column "serial#" format 99,999 heading "Serial" justify c
column "PID" format 99,999 heading "PID#" justify c
column program format a30
select nvl(s.username,p.program) "USERNAME",s.sid sess ,s.OSUSER,
       s.serial#,p.spid "PID", s.process "OS-PPID", s.program
from v\$session s, v\$process p
where s.sid = $SID and
s.paddr = p.addr
and s.username != 'NULL'
order by 2
/
EOF

ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set pagesize 0 feed off termout off head off
select p.spid from v\\$session s, v\\$process p where s.sid = $SID  and s.paddr = p.addr ;
EOF`
if $SBINS/yesno.sh "to $FACTION Session $SID" DO N
  then
sqlplus -s "$CONNECT_STRING" <<EOF
oradebug setospid $ret ;
oradebug $FACTION ;
EOF
fi


# ------------------------------------------------------------------------------
#     Report Response time breakdown
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "CPU" ];then

echo "MACHINE $HOST - ORACLE_SID : $ORACLE_SID "
sqlplus -s "$CONNECT_STRING" <<EOF
set head off lines 80 pages 66
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  ,
       'Response time breakdown' nline
from sys.dual
/
set linesize 124 
set heading on
column major      format a8
column minor      format a25
column wait_event format a50 trunc
column seconds    format 999999999
column pct        format a6 justify right
break on major skip 1 on minor

select
  substr(n_major, 3)  major,
  substr(n_minor, 3)  minor,
  wait_event,
  round(time/1000000)  seconds,
  substr(to_char(100 * ratio_to_report(time) over (), '99.00'), 2) || '%'  pct
from
  (
    select /*+ ordered use_hash(b) */
      '1 CPU time'  n_major,
      decode(t.ksusdnam,
	'redo size', '2 reloads',
	'parse time cpu', '1 parsing',
	'3 execution'
      )  n_minor,
      'n/a'  wait_event,
      decode(t.ksusdnam,
	'redo size', nvl(r.time, 0),
	'parse time cpu', t.ksusgstv - nvl(b.time, 0),
	t.ksusgstv - nvl(b.time, 0) - nvl(r.time, 0)
      )  time
    from
      sys.x\$ksusgsta  t,
      (
	select /*+ ordered use_nl(s) */		-- star query: few rows from d and b
	  s.ksusestn,				-- statistic#
	  sum(s.ksusestv)  time			-- time used by backgrounds
	from
	  sys.x\$ksusd  d,			-- statname
	  sys.x\$ksuse  b,			-- session
	  sys.x\$ksbdp  p,			-- background process
	  sys.x\$ksusesta  s			-- sesstat
	where
	  d.ksusdnam in (
	    'parse time cpu',
	    'CPU used when call started') and
	  b.ksspaown = p.ksbdppro and
	  s.ksusestn = d.indx and
	  s.indx = b.indx
	group by
	  s.ksusestn
      )  b,
      (
	select /*+ no_merge */
	  ksusgstv *				-- parse cpu time *
	  kglstrld /				-- SQL AREA reloads /
	  (1 + kglstget - kglstght)		-- SQL AREA misses
	    time
	from
	  sys.x\$kglst  k,
	  sys.x\$ksusgsta  g
	where
	  k.indx = 0 and
	  g.ksusdnam = 'parse time cpu'
      )  r
    where
      t.ksusdnam in (
	'redo size',				-- arbitrary: to get a row to replace
	'parse time cpu',			--   with the 'reload cpu time'
	'CPU used when call started') and
      b.ksusestn (+) = t.indx
    union all
    select
      decode(n_minor,
	'1 normal I/O',		'2 disk I/O',
	'2 full scans',		'2 disk I/O',
	'3 direct I/O',		'2 disk I/O',
	'4 BFILE reads',	'2 disk I/O',
	'5 other I/O',		'2 disk I/O',
	'1 DBWn writes',	'3 waits',
	'2 LGWR writes',	'3 waits',
	'3 ARCn writes',	'3 waits',
	'4 enqueue locks',	'3 waits',
	'5 PCM locks',		'3 waits',
	'6 other locks',	'3 waits',
	'1 commits',		'4 latency',
	'2 network',		'4 latency',
	'3 file ops',		'4 latency',
	'4 process ctl',	'4 latency',
	'5 global locks',	'4 latency',
	'6 misc',		'4 latency'
      )  n_major,
      n_minor,
      wait_event,
      time
    from
      (
	select /*+ ordered use_hash(b) use_nl(d) */
	  decode(
	    d.kslednam,
	    					-- disk I/O
	    'db file sequential read',			'1 normal I/O',
	    'db file scattered read',			'2 full scans',
	    'BFILE read',				'4 BFILE reads',
	    'KOLF: Register LFI read',			'4 BFILE reads',
	    'log file sequential read',			'5 other I/O',
	    'log file single write',			'5 other I/O',
						-- resource waits
	    'checkpoint completed',			'1 DBWn writes',
	    'free buffer waits',			'1 DBWn writes',
	    'write complete waits',			'1 DBWn writes',
	    'local write wait',				'1 DBWn writes',
	    'log file switch (checkpoint incomplete)',	'1 DBWn writes',
	    'rdbms ipc reply',				'1 DBWn writes',
	    'log file switch (archiving needed)',	'3 ARCn writes',
	    'enqueue',					'4 enqueue locks',
	    'buffer busy due to global cache',		'5 PCM locks',
	    'global cache cr request',			'5 PCM locks',
	    'global cache lock cleanup',		'5 PCM locks',
	    'global cache lock null to s',		'5 PCM locks',
	    'global cache lock null to x',		'5 PCM locks',
	    'global cache lock s to x',			'5 PCM locks',
	    'lock element cleanup',			'5 PCM locks',
	    'checkpoint range buffer not saved',	'6 other locks',
	    'dupl. cluster key',			'6 other locks',
	    'PX Deq Credit: free buffer',		'6 other locks',
	    'PX Deq Credit: need buffer',		'6 other locks',
	    'PX Deq Credit: send blkd',			'6 other locks',
	    'PX qref latch',				'6 other locks',
	    'Wait for credit - free buffer',		'6 other locks',
	    'Wait for credit - need buffer to send',	'6 other locks',
	    'Wait for credit - send blocked',		'6 other locks',
	    'global cache freelist wait',		'6 other locks',
	    'global cache lock busy',			'6 other locks',
	    'index block split',			'6 other locks',
	    'lock element waits',			'6 other locks',
	    'parallel query qref latch',		'6 other locks',
	    'pipe put',					'6 other locks',
	    'rdbms ipc message block',			'6 other locks',
	    'row cache lock',				'6 other locks',
	    'sort segment request',			'6 other locks',
	    'transaction',				'6 other locks',
	    'unbound tx',				'6 other locks',
						-- routine waits
	    'log file sync',				'1 commits',
	    'name-service call wait',			'2 network',
	    'Test if message present',			'4 process ctl',
	    'process startup',				'4 process ctl',
	    'read SCN lock',				'5 global locks',
	    decode(substr(d.kslednam, 1, instr(d.kslednam, ' ')),
						-- disk I/O
	      'direct ',				'3 direct I/O',
	      'control ',				'5 other I/O',
	      'db ',					'5 other I/O',
						-- resource waits
	      'log ',					'2 LGWR writes',
	      'buffer ',				'6 other locks',
	      'free ',					'6 other locks',
	      'latch ',					'6 other locks',
	      'library ',				'6 other locks',
	      'undo ',					'6 other locks',
						-- routine waits
	      'SQL*Net ',				'2 network',
	      'BFILE ',					'3 file ops',
	      'KOLF: ',					'3 file ops',
	      'file ',					'3 file ops',
	      'KXFQ: ',					'4 process ctl',
	      'KXFX: ',					'4 process ctl',
	      'PX ',					'4 process ctl',
	      'Wait ',					'4 process ctl',
	      'inactive ',				'4 process ctl',
	      'multiple ',				'4 process ctl',
	      'parallel ',				'4 process ctl',
	      'DFS ',					'5 global locks',
	      'batched ',				'5 global locks',
	      'on-going ',				'5 global locks',
	      'global ',				'5 global locks',
	      'wait ',					'5 global locks',
	      'writes ',				'5 global locks',
	      						'6 misc'
	    )
	  )  n_minor,
	  d.kslednam  wait_event,		-- event name
	  i.kslestim - nvl(b.time, 0)  time	-- non-background time
	from
	  sys.x\$kslei  i,			-- system events
	  (
	    select /*+ ordered use_hash(e) */	-- no fixed index on e
	      e.kslesenm,			-- event number
	      sum(e.kslestim)  time		-- time waited by backgrounds
	    from
	      sys.x\$ksuse  s,			-- sessions
	      sys.x\$ksbdp  b,			-- backgrounds
	      sys.x\$ksles  e			-- session events
	    where
	      s.ksspaown = b.ksbdppro and	-- background session
	      e.kslessid = s.indx
	    group by
	      e.kslesenm
	    having
	      sum(e.kslestim) > 0
	  )  b,
	  sys.x\$ksled  d
	where
	  i.kslestim > 0 and
	  b.kslesenm (+) = i.indx and
	  nvl(b.time, 0) < i.kslestim and
	  d.indx = i.indx and
	  d.kslednam not in (
	    'Null event',
	    'KXFQ: Dequeue Range Keys - Slave',
	    'KXFQ: Dequeuing samples',
	    'KXFQ: kxfqdeq - dequeue from specific qref',
	    'KXFQ: kxfqdeq - normal deqeue',
	    'KXFX: Execution Message Dequeue - Slave',
	    'KXFX: Parse Reply Dequeue - Query Coord',
	    'KXFX: Reply Message Dequeue - Query Coord',
	    'PAR RECOV : Dequeue msg - Slave',
	    'PAR RECOV : Wait for reply - Query Coord',
	    'Parallel Query Idle Wait - Slaves',
	    'PL/SQL lock timer',
	    'PX Deq: Execute Reply',
	    'PX Deq: Execution Msg',
	    'PX Deq: Index Merge Execute',
	    'PX Deq: Index Merge Reply',
	    'PX Deq: Par Recov Change Vector',
	    'PX Deq: Par Recov Execute',
	    'PX Deq: Par Recov Reply',
	    'PX Deq: Parse Reply',
	    'PX Deq: Table Q Get Keys',
	    'PX Deq: Table Q Normal',
	    'PX Deq: Table Q Sample',
	    'PX Deq: Table Q qref',
	    'PX Deq: Txn Recovery Reply',
	    'PX Deq: Txn Recovery Start',
	    'PX Deque wait',
	    'PX Idle Wait',
	    'Replication Dequeue',
	    'Replication Dequeue ',
	    'SQL*Net message from client',
	    'SQL*Net message from dblink',
	    'debugger command',
	    'parallel query dequeue wait',
	    'pipe get',
	    'queue messages',
	    'rdbms ipc message',
	    'secondary event',
	    'single-task message',
	    'slave wait'
	  ) and
	  d.kslednam not like 'resmgr:%'
      )
  )
order by n_major, n_minor, time desc
/
EOF
exit

# ------------------------------------------------------------------------------
#    Session IO
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "SLIO" ];then
     if [ -z "$SID" ];then
        echo "I need a Session ID"
        exit
     fi
sqlplus -s "$CONNECT_STRING" <<EOF

ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '   'Page:' format 999 sql.pno skip 2
set embedded on heading on feedback off pause off linesize 110 pagesize 66

col sname format A15 head "User name"
col type format A10 head "object|type"
col obj_name format A30 head "object|name"
col sid format 999 head "sid"
col gets format 9,999,999,990 head "Gets"
col reads format 9,999,999,990 head "Physical|reads"
col chg  format 9,999,999,990 head "Changes"

break on sname on sid on gets on reads on chg

select s.username sname,v.sid ,
i.BLOCK_CHANGES + I.CONSISTENT_CHANGES gets,
 I.PHYSICAL_READS reads,I.BLOCK_CHANGES + I.CONSISTENT_CHANGES chg,
v.type,v.object obj_name
from v\$session s, v\$access v, V\$SESS_IO I
where v.sid = s.sid 
  and s.sid = $SID 
  and v.sid = i.sid
order by gets desc
/
EOF

# ------------------------------------------------------------------------------
#    Read pga dump
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "SLF" ];then
echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
        sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set linesize 80
set pagesize 66
set heading off pause off
set embedded off
set verify off
spool $FOUT
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'Sessions          -  List failed over sessions info ' nline
        from sys.dual
/
set embedded on
rem prompt *----------------------------------------------------*
set lines 132
set heading on
column "USERNAME" format a15
column "terminal" format a6
column "OSUSER" format a12
column "OS-PPID" format a8
column "sess" format 9999 heading "Sess|ID" justify c
column "serial#" format 99999 heading "Serial" justify c
column "PID" format 99999 heading "PID#" justify c
column "ssa" format 999,999 heading "Last|activity" justify c
column "failed_over" format A10 heading "Failed| Over"
column "failover_method" format A14 heading "Fail. method"
column "failover_type" format A14 heading "Fail. type"

select nvl(s.username,p.program) "USERNAME",s.sid sess ,s.OSUSER,
       s.serial#,p.spid "PID", s.process "OS-PPID", s.last_call_et ssa, s.status,
       failed_over, failover_type,failover_method
from v\$session s, v\$process p
where s.paddr = p.addr
and s.username != 'NULL'
order by 2
/
EOF
exit

# ------------------------------------------------------------------------------
#    Read pga dump
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "READ_DUMP_PGA" ];then
     if [ ! -f "$TRC" ];then
        echo "I did not found the trace file"
        exit
     fi
     $SBINS/smenu_create_ext_table.ksh -f $TRC
     typeset -u fname30
     fpath=`dirname $TRC`                # full path name of the file
     fname=`basename $TRC`
     fname30=`echo $fname | cut -f1 -d'.'`
     fname30=${fname30:0:30}               # trunc file name to max 30 characters

sqlplus -s "$CONNECT_STRING" <<EOF
-- set echo off
--------------------------------------------------------
-- @name: heap_analyze
-- @author: dion cho
-- @note: analyze heap dump file. 
--    this is a example of how to analyze heap dump only works only for 
--  my local database 10gR2
--  get_trace_file script should be executed beforehand
-- @usage: heap_analyze <file_name>
-- Adapted to Smenu by bpa 10:06:2009 
--------------------------------------------------------
set verify off lines 190 pages 66
column tsize new_value total_size
column ratio format 99.99
column ds format a20
col ctype for a30
col atype for a30
with t as (
    select 
        to_number(substr(fline, 21)) as tsize
    from 
         $fname30
    where
       regexp_like(fline, 'Total heap size')
) 
select
    sum(tsize) as tsize
from t ;

with c as (
    select atype, csize, sum(csize) over() as t_size, &total_size as heap_size
    from (
        select atype, sum(csize) as csize
        from (
            select
               to_number(regexp_replace(fline,'.*sz=[[:space:]]+([[:digit:]]+)[[:space:]].*','\1')) csize,
               rtrim(regexp_replace(fline,'.*sz=[[:space:]]+[[:digit:]]+[[:space:]]+([[:alnum:]]+)[[:space:]].*','\1')) atype,
               rtrim(regexp_replace(fline,'.*["]+(.*)["].*','\1')) ctype,
               rtrim(regexp_replace(fline,'.*["]+.*["](.*)','\1')) ds
            from 
                 $fname30
            where 
                substr(fline, 1, 7) = '  Chunk'
            and rtrim(regexp_replace(fline,'.*sz=[[:space:]]+[[:digit:]]+[[:space:]]+([[:alnum:]]+)[[:space:]].*','\1')) <> 'free'
            ) 
        group by atype
    )
)
select atype, csize, heap_size, 
    (csize/heap_size)*100  as ratio
from c
union all
select 'free', heap_size-t_size, heap_size, ((heap_size-t_size)/heap_size)*100 as ratio
from c where rownum = 1 ;

with c as (
    select ctype, csize, sum(csize) over() as t_size, &total_size as heap_size
    from (
        select ctype, sum(csize) as csize
        from (
            select
               to_number(regexp_replace(fline,'.*sz=[[:space:]]+([[:digit:]]+)[[:space:]].*','\1')) csize,
               rtrim(regexp_replace(fline,'.*sz=[[:space:]]+[[:digit:]]+[[:space:]]+([[:alnum:]]+)[[:space:]].*','\1')) atype,
               rtrim(regexp_replace(fline,'.*["]+(.*)["].*','\1')) ctype,
               rtrim(regexp_replace(fline,'.*["]+.*["](.*)','\1')) ds
            from 
                $fname30
            where 
                substr(fline, 1, 7) = '  Chunk'
            and rtrim(regexp_replace(fline,'.*sz=[[:space:]]+[[:digit:]]+[[:space:]]+([[:alnum:]]+)[[:space:]].*','\1')) <> 'free'
            ) 
        group by ctype
    )
)
select ctype, csize, heap_size, (csize/heap_size)*100  as ratio
from c
union all
select 'free', heap_size-t_size, heap_size, 
    ((heap_size-t_size)/heap_size)*100 as ratio
from c where rownum = 1 ;

with c as (
    select ds, csize, sum(csize) over() as t_size, &total_size as heap_size
    from (
        select ds, sum(csize) as csize
        from (
            select
               to_number(regexp_replace(fline,'.*sz=[[:space:]]+([[:digit:]]+)[[:space:]].*','\1')) csize,
               rtrim(regexp_replace(fline,'.*sz=[[:space:]]+[[:digit:]]+[[:space:]]+([[:alnum:]]+)[[:space:]].*','\1')) atype,
               rtrim(regexp_replace(fline,'.*["]+(.*)["].*','\1')) ctype,
               rtrim(regexp_replace(fline,'.*["]+.*["][[:space:]]+ds=(.*)','\1')) ds 
            from 
                $fname30
            where 
                substr(fline, 1, 7) = '  Chunk'
                and regexp_like(fline, 'ds=' )
            ) 
        group by ds
    )
)
select
     ds, csize, heap_size, (csize/heap_size)*100  as ratio from c
union all
select 'free', heap_size-t_size, heap_size, ((heap_size-t_size)/heap_size)*100 as ratio
from c 
    where rownum = 1 ;
EOF

 # drop now the external table.
var=`sqlplus -s "$CONNECT_STRING" <<EOF
set feed off pagesize 0 head off
select count(1) from dba_external_locations where table_name = upper('$fname30') and LOCATION='$fname' ;
EOF`
if  [ $var -gt 0 ];then
    sqlplus -s "$CONNECT_STRING" <<EOF
    set feed off pagesize 0 head off pause off
    drop table $fname30;
EOF
fi

# ------------------------------------------------------------------------------
#       Dump PGA and process state
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "DUMP" ];then
   if [ "$DUMP_VAR" = 'ST' ];then
         DUMP=" processstate 10"
   elif [ "$DUMP_VAR" = 'PGA_ADR' ];then
         DUMP="heapdump_addr $START_ADR $END_ADR"
   else
         DUMP="heapdump 1"
   fi
(
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID '   right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 80 heading off embedded off pause off termout on verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Session SID whose dump : $TO_DUMP ' nline from sys.dual
/
rem set pages 0 feed off echo off
set embedded on
rem prompt *----------------------------------------------------*
set lines 200
set heading on pause off
column "USERNAME" format a20
column "terminal" format a6
column "OSUSER" format a16
column "sess" format 9,999 heading "Sess|ID" justify c
column "serial#" format 99,999 heading "Serial" justify c
column "PID" format 99,999 heading "PID#" justify c
column program format a50
select nvl(s.username,p.program) "USERNAME",s.sid sess ,s.OSUSER,
       s.serial#,p.spid "PID", s.process "OS-PPID", s.program
from v\$session s, v\$process p
where s.sid = $SID and s.paddr = p.addr and s.username != 'NULL' order by 2
/
EOF
)

ret=`sqlplus -s "$CONNECT_STRING" <<EOF
set pagesize 0 feed off termout off head off
select p.spid from v\\$session s, v\\$process p where s.sid = $SID  and s.paddr = p.addr ;
EOF
`

if $SBINS/yesno.sh "to dump PGA for Session $TO_DUMP" DO N
  then
     trc_file=`sqlplus -s "$CONNECT_STRING" <<EOF
set echo off termout off
oradebug setospid $ret ;
oradebug dump $DUMP ;
set echo on termout on
oradebug tracefile_name;
EOF`
     echo "$trc_file"
     trc=`echo "$trc_file"|tail -1`
     if [ "$DUMP_VAR" = "PGA" ];then
        echo
        echo "Use : 'sl -rf $trc'   to start parsing this trace file"
        echo
     fi
fi
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "SLH" ];then
sqlplus -s "$CONNECT_STRING" <<EOF
ttitle skip 2 'MACHINE $HOST - ORACLE_SID : $ORACLE_SID '  right 'Page:' format 999 sql.pno skip 2
column nline newline
set pagesize 66 linesize 80  termout on pause off embedded on verify off heading off

select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline ,
       'Session Hit (HELP slh -h)' nline from sys.dual
/
set heading on linesize 190 pagesize 66
col sid format 9999 heading "Sid"
col hit format A4 heading "%Hit"
col bchng format 999999999 head "Blocks |Changes" justify c
col cchng format 999999999 head "Consistent|Changes" justify c
col cgets format 999999999 head "Consistent|Gets" justify c
col cpu   format 9990.99 head "Cpu" justify c
col Pread head "Physical|Reads" justify c
col hp format 999999 head "Hard|Parse" justify c
col username format A20 head "username" justify c
col osuser format A12 head "osuser" justify c
col ospid format A10 head "ospid" justify c
col com format A3 head "Com"
col typ format A4 head "User|Type"

select sid, username, ospid , com ,hash_value, typ, CPU, HP, bchng,cchng,cgets,pread, hit
from (
select s.sid sid,
       decode(s.username,null, 
                         '-'||regexp_replace(s.program , '.*(\(.*\)).*','\1') ,
              s.Username) username, osuser ,
       osuser
       ,spid ospid,
       substr(command,1,3) com,
       nvl(s.sql_hash_value,s.prev_hash_value)  hash_value,
       substr(type,1,4) typ,
       t.value/100 CPU,
       t2.value HP,
       block_changes bchng,
       consistent_changes cchng,
       consistent_gets cgets,
       physical_reads pread,
       substr(decode((consistent_gets+block_gets),0,'None',
             (100*(consistent_gets+block_gets-physical_reads)/
             (consistent_gets+block_gets))),1,4) hit
from v\$process p, v\$SESSTAT t,v\$sess_io i ,v\$session s, v\$statname n, v\$sesstat t2
where  
       i.sid=s.sid and p.addr=paddr(+) 
  and  s.sid=t.sid and $AND_USER 
       t.statistic# (+) = 12   
  and t2.sid (+) = s.sid 
  and t2.statistic# = n.statistic# 
  and n.name (+) = 'parse count (hard)'  $AND_SID $ORDER
) where rownum < $ROWNUM
/
EOF
exit
# ------------------------------------------------------------------------------
# Snapper -- Session Snapper v1.06 by Tanel Poder ( http://www.tanelpoder.com )
# ------------------------------------------------------------------------------
elif [ "$ACTION" = "SNAPER" ];then
   if [ -n "$GATHER" ];then
      SNAP_PAR1="out,gather=$GATHER"
   else
      SNAP_PAR1=${SNAP_PAR1:-'out'}
   fi
   SNAP_PAR2=${SNAP_PAR2:-1}
   SNAP_PAR3=${SNAP_PAR3:-1}
   if [ -n "$ULIKE" ];then
       SNAP_PAR4="select sid from v\$session where username like '%${ULIKE}%'"
       SNAP_PAR4_C="select sid from v\$session where username like ''%${ULIKE}%''"
   elif [ -n "$PLIKE" ];then
       SNAP_PAR4="select sid from v\$session where program like '%${PLIKE}%'"
       SNAP_PAR4_C="select sid from v\$session where program like ''%${PLIKE}%''"
   else
       SNAP_PAR4=$SID
   fi
sqlplus -s "$CONNECT_STRING" <<EOF
set termout off tab off verify off linesize 200

-- Get parameters
define snapper_options='$SNAP_PAR1'
define   snapper_sleep='$SNAP_PAR2'
define   snapper_count='$SNAP_PAR3'
define     snapper_sid='$SNAP_PAR4'
-- The following code is required for making this script dynamic as due 
-- different Oracle versions, script parameters or granted privileges some
-- statements might not compile if not adjusted properly.

define _IF_ORA_10_OR_HIGHER='--'
define _IF_DBMS_SYSTEM_ACCESSIBLE='/* dbms_system is not accessible' /*dummy*/

col snapper_oraversion     noprint new_value _IF_ORA10_OR_HIGHER
col dbms_system_accessible noprint new_value _IF_DBMS_SYSTEM_ACCESSIBLE

select decode(substr(banner, instr(banner, 'Release ')+8,1), 1, '', '--') snapper_oraversion from v\$version where rownum=1;

-- this block determines whether dbms_system.ksdwrt is accessible to us
-- dbms_describe is required as all_procedures/all_objects may show this object
-- even if its not executable by us (thanks to o7_dictionary_accessibility=false)

var v varchar2(100)

declare
    o       sys.dbms_describe.number_table;
    p       sys.dbms_describe.number_table;
    l       sys.dbms_describe.number_table;
    a       sys.dbms_describe.varchar2_table;
    dty     sys.dbms_describe.number_table;
    def     sys.dbms_describe.number_table;
    inout   sys.dbms_describe.number_table;
    len     sys.dbms_describe.number_table;
    prec    sys.dbms_describe.number_table;
    scal    sys.dbms_describe.number_table;
    rad     sys.dbms_describe.number_table;
    spa     sys.dbms_describe.number_table;

begin
    sys.dbms_describe.describe_procedure(
        'DBMS_SYSTEM.KSDWRT', null, null, 
        o, p, l, a, dty, def, inout, len, prec, scal, rad, spa
    );
    -- we never get to following statement if dbms_system is not accessible
    -- as sys.dbms_describe will raise an exception
    :v:= '-- dbms_system is accessible';
exception    
    when others then null;
end;
/
select nvl(:v, '/* dbms_system is not accessible') dbms_system_accessible from dual; 
set termout on serverout on size 1000000
declare
    -- forward declarations
    procedure output(p_txt in varchar2);
    procedure fout;
    function tptformat( p_num in number, 
                        p_stype in varchar2 default 'STAT',
                        p_precision in number default 2,
                        p_base in number default 10,
                        p_grouplen in number default 3
                   )    
                   return varchar2;
    function getopt( p_parvalues in varchar2, 
                     p_extract in varchar2, 
                     p_delim in varchar2 default ',' 
                   ) 
                   return varchar2;
    -- type, constant, variable declarations
    -- trick for holding 32bit UNSIGNED event and stat_ids in 32bit SIGNED PLS_INTEGER
    pls_adjust constant number(10,0) := power(2,31) - 1;

    type srec is record (stype varchar2(4), sid number, statistic# number, value number );
    type stab is table of srec index by pls_integer;
    s1 stab;
    s2 stab;

    type snrec is record (stype varchar2(4), statistic# number, name varchar2(64));
    type sntab is table of snrec index by pls_integer;
    sn_tmp sntab;
    sn sntab;
    i number;
    a number;
    b number;
    c number;
    delta number;
    changed_values number;
    pagesize number:=99999999999999;
    missing_values_s1 number := 0;
    missing_values_s2 number := 0;
    d1 date;
    d2 date;
    lv_gather varchar2(100);

  /*---------------------------------------------------
    -- proc for outputting data to trace or dbms_output
    ---------------------------------------------------*/
    procedure output(p_txt in varchar2) is
    begin
        if getopt('&snapper_options', 'out') is not null then
            dbms_output.put_line(p_txt);
        end if;
        -- The block below is a sqlplus trick for conditionally commenting out PL/SQL code
        &_IF_DBMS_SYSTEM_ACCESSIBLE
        if getopt('&snapper_options', 'trace') is not null then
            sys.dbms_system.ksdwrt(1, p_txt);
            sys.dbms_system.ksdfls;
        end if;
        -- */   
    end; -- output
  /*---------------------------------------------------
    -- proc for outputting data, utilizing global vars
    ---------------------------------------------------*/
    procedure fout is
    begin
        output( 'DATA, '
                ||to_char(s2(b).sid,'999999')||', '
                ||to_char(d1, 'YYYYMMDD HH24:MI:SS')||', '
                ||to_char(case (d2-d1) when 0 then &snapper_sleep else (d2-d1) * 86400 end, '9999999')||', '
                ||s2(b).stype||', '
                ||rpad(sn(s2(b).statistic#).name, 40, ' ')||', '
                ||to_char(delta, '999999999999')||', '
                ||to_char(delta/(case (d2-d1) when 0 then &snapper_sleep else (d2-d1) * 86400 end),'999999999')||', '
                ||lpad(tptformat(delta, s2(b).stype), 10, ' ')||', '
                ||lpad(tptformat(delta/(case (d2-d1) when 0 then &snapper_sleep else (d2-d1)* 86400 end ), s2(b).stype), 10, ' ')
        );
    end;

  /*---------------------------------------------------
    -- function for converting large numbers to human-readable format
    ---------------------------------------------------*/
    function tptformat( p_num in number, 
                        p_stype in varchar2 default 'STAT',
                        p_precision in number default 2,
                        p_base in number default 10,    -- for KiB/MiB formatting use
                        p_grouplen in number default 3  -- p_base=2 and p_grouplen=10
                      )
                      return varchar2
    is
    begin
        if p_stype in ('WAIT','TIME') then
            return 
                round(
                    p_num / power( p_base , trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) ), p_precision  
                )
                || case trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen))
                       when 0            then 'us'
                       when 1            then 'us'
                       when p_grouplen*1 then 'ms'
                       when p_grouplen*2 then 's'
                       when p_grouplen*3 then 'ks'
                       when p_grouplen*4 then 'Ms'
                       else '*'||p_base||'^'||to_char( trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) )||' us'
                    end;
        else
            return 
                round(
                    p_num / power( p_base , trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) ), p_precision  
                )
                || case trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen))
                       when 0            then ''    
                       when 1            then ''
                       when p_grouplen*1 then 'k'
                       when p_grouplen*2 then 'M'
                       when p_grouplen*3 then 'G'
                       when p_grouplen*4 then 'T'
                       when p_grouplen*5 then 'P'
                       when p_grouplen*6 then 'E'
                       else '*'||p_base||'^'||to_char( trunc(log(p_base,abs(p_num)))-trunc(mod(log(p_base,abs(p_num)),p_grouplen)) )
                    end;
        end if; 
    end; -- tptformat
  /*---------------------------------------------------
    -- simple function for parsing arguments from parameter string
    ---------------------------------------------------*/
    function getopt( p_parvalues in varchar2, 
                     p_extract in varchar2, 
                     p_delim in varchar2 default ',' 
                   ) return varchar2
    is
        ret varchar(1000) := NULL;
    begin
        if lower(p_parvalues) like lower(p_extract)||'%'
        or lower(p_parvalues) like '%'||p_delim||lower(p_extract)||'%' then
        
            ret :=
                nvl ( 
                    substr(p_parvalues,
                            instr(p_parvalues, p_extract)+length(p_extract), 
                            case 
                                instr(
                                    substr(p_parvalues,
                                            instr(p_parvalues, p_extract)+length(p_extract)
                                    )
                                    , p_delim
                                )
                            when 0 then length(p_parvalues)
                            else
                                instr(
                                    substr(p_parvalues,
                                            instr(p_parvalues, p_extract)+length(p_extract)
                                    )
                                    , p_delim
                                ) - 1
                            end
                    )
                    , chr(0) -- in case parameter was specified but with no value
                );
        else
            ret := null; -- no parameter found
        end if;
        return ret;
    end; -- getopt
  /*---------------------------------------------------
    -- proc for querying performance data into collections
    ---------------------------------------------------*/
   procedure snap( p_snapdate in out date, p_stats in out stab ) is
   begin
        p_snapdate := sysdate;
        select *
        bulk collect into p_stats
        from (  
                                         select 'STAT' stype, sid, statistic# - pls_adjust statistic#, value 
                                         from v\$sesstat
                                         where sid in ($SNAP_PAR4)
                                         and   lv_gather like '%s%'
                                         --
                                         union all
                                         select 
                                                'WAIT', sw.sid, 
                                                en.event# + (select count(*) from v\$statname) + 1 - pls_adjust, 
                                                nvl(se.time_waited_micro,0) + ( decode(se.event||sw.state, sw.event||'WAITING', sw.seconds_in_wait, 0) * 1000000 ) value
                                         from v\$session_wait sw, v\$session_event se, v\$event_name en 
                                         where sw.sid = se.sid
                                         and   se.event = en.name
                                         and   se.sid in ($SNAP_PAR4)
                                         and   lv_gather like '%w%'
                                         --
                    &_IF_ORA10_OR_HIGHER union all 
                    &_IF_ORA10_OR_HIGHER select 'TIME' stype, sid, stat_id - pls_adjust statistic#, value
                    &_IF_ORA10_OR_HIGHER from v\$sess_time_model
                    &_IF_ORA10_OR_HIGHER where sid in ($SNAP_PAR4)
                    &_IF_ORA10_OR_HIGHER and   lv_gather like '%t%'
                                         --
                                         union all
                                         select ' LAT', s.sid, 
                                               l.latch# + 
                                                   (select count(*) from v\$statname) + 
                                                   (select count(*) from v\$event_name) +
                                                   1 - pls_adjust statistic#,
                                               l.gets + l.immediate_gets value
                                         from v\$latch l, v\$session s
                                         where 
                                              -- deliberate cartesian join
                                               s.sid in ($SNAP_PAR4)
                                         and   lv_gather like '%l%'
                                         --
                                         union all
                                         select ' ENQ', s.sid, 
                                               ascii(substr(e.eq_type,1,1))*256 + ascii(substr(e.eq_type,2,1)) +
                                                   (select count(*) from v\$statname) + 
                                                   (select count(*) from v\$event_name) +
                                                   (select count(*) from v\$latch) +
                                                   1 - pls_adjust statistic#,
                                               e.total_req# value
                                         from v\$enqueue_stat e, v\$session s
                                         where 
                                              -- deliberate cartesian join
                                               s.sid in ($SNAP_PAR4)
                                         and   lv_gather like '%e%'
        )
        order by sid, stype, statistic#;
   end snap;
begin
    pagesize := nvl( getopt('&snapper_options', 'pagesize=' ), pagesize);
    -- determine which statistics to collect
    lv_gather := case nvl( lower(getopt ('&snapper_options', 'gather=')), 'stw')
                    when 'all'  then 'stw'
                    else nvl( lower(getopt ('&snapper_options', 'gather=')), 'stw')
                 end;
    if pagesize > 0 then 
        output(' ');
        output(chr(8));
      output('-- Parameters used: snapper.sql '||'&snapper_options'||' '||to_char(&snapper_sleep)||' '
              ||to_char(&snapper_count)||' '||'$SNAP_PAR4_C');
        output(chr(8));
        output(' ');
    end if;
    -- initialize statistic and event name array
    -- fetch statistic names with their adjusted IDs
    select *
    bulk collect into sn_tmp
    from (  
                                 select 'STAT' stype, statistic# - pls_adjust statistic#, name
                                 from v\$statname
                                 where lv_gather like '%s%'
                                 --
                                 union all
                                 select 'WAIT', 
                                        event# + (select count(*) from v\$statname) + 1 - pls_adjust, name
                                 from v\$event_name
                                 where lv_gather like '%w%'
                                 --
            &_IF_ORA10_OR_HIGHER union all 
            &_IF_ORA10_OR_HIGHER select 'TIME' stype, stat_id - pls_adjust statistic#, stat_name name
            &_IF_ORA10_OR_HIGHER from v\$sys_time_model
            &_IF_ORA10_OR_HIGHER where lv_gather like '%t%'
                                 --
                                 union all
                                 select ' LAT', 
                                       l.latch# + 
                                           (select count(*) from v\$statname) + 
                                           (select count(*) from v\$event_name) +
                                           1 - pls_adjust statistic#,
                                       name
                                 from v\$latch l
                                 where lv_gather like '%l%'
                                 --
                                 union all
                                 select ' ENQ', 
                                       ascii(substr(e.eq_type,1,1))*256 + ascii(substr(e.eq_type,2,1)) +
                                           (select count(*) from v\$statname) + 
                                           (select count(*) from v\$event_name) +
                                           (select count(*) from v\$latch) +
                                           1 - pls_adjust statistic#,
                                           eq_type
                                 from (
                                       select es.eq_type
            &_IF_ORA10_OR_HIGHER              ||' - '||lt.name
                                              eq_type,
                                              total_req#
                                       from
                                              v\$enqueue_stat es
            &_IF_ORA10_OR_HIGHER            , V\$lock_type lt
            &_IF_ORA10_OR_HIGHER       where es.eq_type = lt.type
                                 ) e
                                 where lv_gather like '%e%'
    )
    order by stype, statistic#;
    -- store these into an index_by array organized by statistic# for fast lookup
    --output('sn_tmp.count='||sn_tmp.count);
    --output('lv_gather='||lv_gather);
    for i in 1..sn_tmp.count loop
    --  output('i='||i||' statistic#='||sn_tmp(i).statistic#);
        sn(sn_tmp(i).statistic#) := sn_tmp(i);
    end loop;

    -- main sampling loop
    for c in 1..&snapper_count loop
        -- print header if required
        if pagesize > 0 and mod(c-1, pagesize) = 0 then 
            output(rpad('--',141,'-'));
            output('HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, '
                    ||rpad('STATISTIC',40,' ')
                    ||',         DELTA,      D/SEC,     HDELTA,     HD/SEC'
            );
            output(rpad('-',141,'-'));
        else 
            if pagesize = -1 and c = 1 then

                output('HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, '
                        ||rpad('STATISTIC',40,' ')
                        ||',         DELTA,      D/SEC,     HDELTA,     HD/SEC'
                );
        
            end if;
        end if;
        if c = 1 then 
            snap(d1,s1);        
        else
            d1 := d2;            
            s1 := s2;
        end if; -- c = 1
        dbms_lock.sleep( (&snapper_sleep - (sysdate - d1)) ); -- dbms_lock.sleep( (&snapper_sleep - (sysdate - d1))*1000/1024 );
        snap(d2,s2);
        changed_values := 0;
        missing_values_s1 := 0;
        missing_values_s2 := 0;
        i :=1; -- iteration counter (for debugging)
        a :=1; -- s1 array index
        b :=1; -- s2 array index
        while ( a <= s1.count and b <= s2.count ) loop
            delta := 0; -- don't print
            case
                when s1(a).sid = s2(b).sid then
                    case
                        when s1(a).statistic# = s2(b).statistic# then
                            delta := s2(b).value - s1(a).value;
                            if delta != 0 then fout(); end if;
                            a := a + 1;
                            b := b + 1;
                        when s1(a).statistic# > s2(b).statistic# then
                            delta := s2(b).value;
                            if delta != 0 then fout(); end if;
                            b := b + 1;
                        when s1(a).statistic# < s2(b).statistic# then
                            output('ERROR, s1(a).statistic# < s2(b).statistic#, a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count);
                            a := a + 1;
                            b := b + 1;
                    else
                            output('ERROR, s1(a).statistic# ? s2(b).statistic#, a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count);
                            a := a + 1;
                            b := b + 1;
                    end case; -- s1(a).statistic# ... s2(b).statistic#
                when s1(a).sid > s2(b).sid then
                    delta := s2(b).value;
                    if delta != 0 then fout(); end if;
                    b := b + 1;
                when s1(a).sid < s2(b).sid then
                    output('WARN, Session has disappeared during snapshot, ignoring SID='||to_char(s2(b).sid)||' a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count);
                    a := a + 1;
                else
                    output('ERROR, Should not be here, SID='||to_char(s2(b).sid)||' a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count);
            end case; -- s1(a).sid ... s2(b).sid
            i:=i+1;
            if  delta != 0 then
                changed_values := changed_values + 1;
            end if; -- delta != 0
        end loop; -- while ( a <= s1.count and b <= s2.count )
    if pagesize > 0 and changed_values > 0 then output('--  End of snap '||to_char(c)); end if;
    end loop; -- for c in 1..snapper_count 
end;
/

undefine snapper_oraversion
undefine snapper_sleep
undefine snapper_count
undefine snapper_sid
undefine _IF_ORA10_OR_HIGHER
undefine _IF_DBMS_SYSTEM_ACCESSIBLE
col snapper_oraversion clear
col dbms_system_accessible clear

EOF
# ----------------
# Events and CPU time for a session
# ----------------
elif [ "$ACTION" = "EV_CPU" ];then
 if [ -z "$SID" ];then
      echo
      echo
      echo "  Error : I need an Session SID\n"  
      echo "          sl -t 1 "
      echo
      echo
      exit
 fi
echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
sqlplus -s "$CONNECT_STRING" <<EOF
set lines 132 pagesize 66 feed off pagesize 30 verify off
col sid format 9999
col response_time new_value response_time
column event format a30 head 'Event type'
column total_waits head 'Total  |Waits  '
column total_timeouts head 'Total  | Timeouts '
column time_waited format 9999999999.9 head ' Time (cs)|Waited  '
column average_wait format 99999999.9 head 'Average (ms)|Wait   '
col wait_class format a20
col impact format 990.9
col service_time head 'Service Time (cs)'
col tot_wait head 'Total waits|For session (cs)'
col response_time head 'Total | Response time(cs)' justify c
col cpu_other head 'cpu others (cs)'
col recursive_cpu head 'recursive |cpu(cs)'  justify c
col parse_time head 'Parse | time (cs)'  justify c
col impact head 'Impact (%)'

select  service_time, tot_wait , (service_time + tot_wait) response_time, 
       (service_time-parse_time-recursive_cpu) cpu_other , recursive_cpu, parse_time
from 
   ( select value service_time FROM v\$sesstat st, v\$statname sn 
            where st.sid=$SID and st.statistic# = sn.statistic# and sn.name = 'CPU used by this session' ),
   ( select value parse_time FROM v\$sesstat st, v\$statname sn 
            where st.sid=$SID and st.statistic# = sn.statistic# and sn.name = 'parse time cpu'  ),
   ( select value recursive_cpu FROM v\$sesstat st, v\$statname sn 
            where st.sid=$SID and st.statistic# = sn.statistic# and sn.name = 'recursive cpu usage' ),
   ( select  sum(se.time_waited) tot_wait from v\$session_event se 
             where se.sid=$SID and se.wait_class != 'Idle' )
/

prompt
prompt =================================================================

prompt =  Break down of session events and impact on the response time =
prompt =================================================================


select
  a.sid ,
  a.event, total_waits $S total_waits, total_timeouts , 
   a.time_waited,  a.average_wait, a.wait_class , decode(&response_time,0,0,time_waited/&response_time*100) impact
from
  sys.v_\$session_event a , sys.v_\$session b
  where a.sid = b.sid 
        and a.wait_class !='Idle'   and a.sid = '$SID'
        order by impact desc;
prompt
col  tim head 'Time (ms)'
select /*+ leading(mys) no_merge(mys) */
        stat_name, round(value/1024/1024,1) tim
from
        v\$sess_time_model stm
where
        stm.sid = $SID
and     stm.value != 0 ;
EOF
#--select value dbtime FROM v\$sesstat st, v\$statname sn where st.sid=$SID and st.statistic# = sn.statistic# and sn.name = 'DB time' ;
# ----------------
# CPU and DB time
# ----------------
elif [ "$ACTION" = "DB_CPU" ];then
sqlplus -s "$CONNECT_STRING" <<EOF
set lines 132 pagesize 66 
col username format a16
col pp format  a6 head 'OS PID'
col module format a50
col sid format 9999
SELECT s.sid, p.spid pp, s.username, s.module, st.value/100 as "DB Time (sec)"
    , stcpu.value/100 as "CPU Time (sec)", decode(st.value,0, round(stcpu.value / st.value * 100,2)) as "% CPU"
    FROM v\$sesstat st, v\$statname sn, v\$session s, v\$sesstat stcpu, v\$statname sncpu, v\$process p
    WHERE sn.name = 'DB time' -- CPU
    AND st.statistic# = sn.statistic# and $AND_USER
        st.sid = s.sid
    AND  sncpu.name = 'CPU used by this session' -- CPU
    AND stcpu.statistic# = sncpu.statistic#
    AND stcpu.sid = st.sid
   AND s.paddr = p.addr
   AND st.value > 0
  order by 6 desc
/

EOF
# ----------------
# ASH
# ----------------
elif [ "$ACTION" = "ASH" ];then
sqlplus -s "$CONNECT_STRING" <<EOF
set lines 190 pagesize 66 
 SELECT   DECODE (session_state, 'WAITING', event, NULL) event,
                     session_state, COUNT(*), SUM (time_waited) time_waited
            FROM     v\$active_session_history
            WHERE          sample_time >= SYSDATE -  ($MIN / 1440)
            GROUP BY DECODE (session_state, 'WAITING', event, NULL),
                   session_state
            ORDER by 1;
EOF
# ----------------
# Size
# ----------------
elif [ "$ACTION" = "PSIZ" ];then
echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
        sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set linesize 80 pagesize 66 heading off pause off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Sessions          -  Session memory size (help: sl -h)' nline
        from sys.dual
/
set embedded on linesize 124 pagesize 66 head on 
col  program format a50
break on report
comp sum label Total of alloc_mb used_mb max_mb on report
col alloc_mb head 'PGA curr.|Allocated' for 999990.9 justify c
col used_mb head 'PGA curr.|Used' for 999990.9 justify c
col max_mb head 'PGA Max.|Used' for 999990.9 justify c
col pga_freeable_mem head 'PGA Freeable' for 999990.9 justify c
select
   $INST_ID s.sid, p.program,  
   pga_alloc_mem/1048576 as alloc_mb,
   pga_used_mem/1048576 as used_mb,
   pga_max_mem/1048576 as max_mb,
   background, pga_freeable_mem/1048576 pga_freeable_mem
from ${G}v\$process p ,${G}v\$session  s
  where s.paddr = p.addr $AND_SESSIONS 
 order by alloc_mb desc
/
EOF
# ----------------
# Default
# ----------------
elif [ "$ACTION" = "DEFAULT" ];then
        #cat <<!EOF
        echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
        sqlplus -s "$CONNECT_STRING" <<!EOF
column nline newline
set linesize 80 pagesize 66 heading off pause off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Sessions          -  Generic info Overview  (help: sl -h)' nline
        from sys.dual
/
set embedded on
rem prompt *----------------------------------------------------*
set lines 190
set heading on
column "USERNAME" format $A_USERNAME
column "terminal" format a6
column "OSUSER" format a13
column "sess" format 9999 heading "Sess|ID" justify c
column "serial#" format 99999 heading "Serial" justify c
column PID format A8 heading "PID#" justify c
column PPID format A11 heading "OS-PID" justify c
column "ssa" format 999,999 heading "Last|activity" justify c
column "machine" format A12
column program format a${column_name_long}
column event format A55
column status format A12
col service_name for a30
column logon_time format a12
column inst_id format 9999 head 'Inst'

select $INST_ID nvl(s.username,p.program) "USERNAME",s.sid sess ,s.OSUSER,
       $FIELD4 p.spid PID, s.process PPID,  $FIELD7
        , s.status,
       to_char(s.logon_time,'dd-mon hh24:mi') logon_time,s.program
from 
     ${G}v\$session s, 
     ${G}v\$process p
where s.paddr = p.addr $USR_NOT_NULL $AND_SID order by $INST_ID s.sid 
/
$EXIT
prompt waiting sessions :
prompt
select sid,event from ${G}v\$session_wait s where wait_time=0 $AND_SID and
      (event not like '%message%' and event not like '%timer%' and event not like '%pipe%' and event not like '%wakeup%'  
       and event not like '%idle wait%' and event not like '%class slave wait%' and event not like '%waiting for time management%')
/
prompt
prompt who is doing full_table_scans ?
prompt ===============================
prompt
select sid from ${G}v\$session_wait where event like '%scattered%'
/
exit
/

exit
!EOF
# -----------
# lock wait
# -----------
elif [ "$ACTION" = "LOCK_WAIT" ];then
        echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
        sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set linesize 80 pagesize 66 heading off pause off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Sessions          -  Session actually running an SQL  (help: sl -h)' nline
        from sys.dual
/
set term on verify off pagesize 333 linesize 190 feedback on  head on
col blocking_Session format 9999 head "block|sess"
col type head "Lock type"
col lmode head "Lock|held" format 999
col request head "lock|req" format 999
col object_name head "Name" format a20
col object_type head "Obj type" format a8
col event format a30
col sid format 9999
col seconds_in_Wait format 99999 head 'sec in| wait' justify c
select $INST_ID s.sid,sql_id,owner||'.'||nvl(subobject_name,object_name) object_name,
       decode(object_type, 'TABLE PARTITION', 'TBL PART','INDEX PARTITION','IDX PART',object_type)  object_type,
       lockwait,BLOCKING_SESSION, event , seconds_in_wait,v.type,Lmode, request
       from ${G}v\$session s , dba_objects , ${G}v\$lock v, ${G}v\$process p
      where p.addr=s.paddr       and
             background is null  and
             s.status = 'ACTIVE' and
             command > 0         and
             v.sid = s. sid      and
             row_wait_obj# = object_id (+);
EOF
# --------------
# Running
# --------------
elif [ "$ACTION" = "RUNNING" ];then
    if [ -n "$G" ];then
         AND_INST=" and inst_id = s.inst_id "
    fi
        echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
        sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set linesize 80 pagesize 66 heading off pause off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Sessions          -  Session actually running an SQL  (help: sl -h)' nline
        from sys.dual
/
set term on verify off pagesize 333 linesize 124 feedback off  head on
col sql_text format a80
set timing on
select $INST_ID sid,command,sql_text  , child_number
          from
               ${G}v\$session s ,
               ${G}v\$process p ,
               ${G}v\$sql v
      where p.addr=s.paddr      and
            background is null  and
            s.status = 'ACTIVE' and
            command > 0         and
            v.hash_value = s.sql_hash_value
            and child_number in (select min (child_number) from ${G}v\$sql where hash_value = s.sql_hash_value $AND_INST) ;
EOF
# ---------
# Overview
# ---------
elif [ "$ACTION" = "OVERVIEW" ];then
        echo "MACHINE $HOSTNAME - ORACLE_SID : $ORACLE_SID                   Page: 1"
        sqlplus -s "$CONNECT_STRING" <<EOF
column nline newline
set linesize 80 pagesize 66 heading off pause off embedded off verify off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline , 'Sessions          -  Generic info Overview  (help: sl -h)' nline
        from sys.dual
/
set term on verify off pagesize 333 linesize 150 feedback off  head on

col spid format 99999999 head 'Spid'
col sid format 9999 head 'Sid'
column O_USER   format A12 trunc
column U_USER   format A12 trunc
column SERIAL#  format 99999 trunc
column TERMINAL format A5 trunc
column PROGRAM  format A55 trunc
column BACKGROUND       format A1
prompt B=Background Process
select $INST_ID decode(nvl(p.background, 0), 0, ' ', 'B') background, s.sid,to_number(p.SPID) SPID, p.USERNAME u_user,
        lower(s.USERNAME) o_user, p.TERMINAL, upper(decode(nvl(s.command, 0), 0,      '---------------',
        1, 'Create Table', 2, 'Insert ...', 3, 'Select ...', 4, 'Create Cluster', 5, 'Alter Cluster', 6, 'Update ...',
        7, 'Delete ...', 8, 'Drop ...', 9, 'Create Index', 10, 'Drop Index', 11, 'Alter Index', 12, 'Drop Table',
        13, '--', 14, '--', 15, 'Alter Table', 16, '--', 17, 'Grant', 18, 'Revoke', 19, 'Create Synonym',
        20, 'Drop Synonym', 21, 'Create View', 22, 'Drop View', 23, '--', 24, '--', 25, '--', 26, 'Lock Table',
        27, 'No Operation', 28, 'Rename', 29, 'Comment', 30, 'Audit', 31, 'NoAudit', 32, 'Create Ext DB',
        33, 'Drop Ext. DB', 34, 'Create Database', 35, 'Alter Database', 36, 'Create RBS', 37, 'Alter RBS', 38, 'Drop RBS',
        39, 'Create Tablespace', 40, 'Alter Tablespace', 41, 'Drop tablespace', 42, 'Alter Session', 43, 'Alter User',
        44, 'Commit', 45, 'Rollback', 46, 'Savepoint',47,'PL/SQL')) Cmdtype, s.program program
from    ${G}v\$process p, ${G}v\$session s
where   p.addr=s.paddr (+) and $AND_USER p.spid is not null order by 1, program, p.username, s.username, spid;
EOF
else
  help
fi