------------------------------------------------------------------------------
--
-- File name:   snapper.sql
-- Purpose:     An easy to use Oracle session-level performance measurement tool
--              which does NOT require any database changes nor creation of any
--              database objects!
--
--              This is very useful for ad-hoc performance diagnosis in environments
--              with restrictive change management processes, where creating
--              even temporary tables and PL/SQL packages is not allowed or would
--              take too much time to get approved.
--
--              All processing is done by a few sqlplus commands and an anonymous
--              PL/SQL block, all that's needed is SQLPLUS access (and if you want
--              to output data to server-side tracefile then execute rights on
--              DBMS_SYSTEM). Snapper only queries some V$ views (and in advanced
--              mode some X$ fixed tables, but it does not enable any traces nor
--              use oradebug.
--
--              The output is formatted the way it could be easily post-processed
--              by either Unix string manipulation tools or loaded to spreadsheet.
--
--              As of version 3.5, Snapper works on Oracle versions starting from
--              Oracle 9.2
--
-- Note1:       The "ASH" functionality in Snapper just samples GV$SESSION view,
--              so you do NOT need Diagnostics Pack licenses to use Snapper's
--              "ASH" output
--
-- Note2:       Snapper just reports you performance metric deltas in a snapsphot
--              and does not attempt to solve any performance problems for you.
--              You still need to interpret and understand these standard Oracle
--              metrics yourself
--
-- Author:      Tanel Poder (tanel@tanelpoder.com)
-- Copyright:   (c) Tanel Poder - http://blog.tanelpoder.com - All rights reserved.
--
-- Disclaimer:  This script is provided "as is", so no warranties or guarantees are
--              made about its correctness, reliability and safety. Use it at your
--              own risk!
--
-- License:     1) You may use this script for your (or your businesses) purposes for free
--              2) You may modify this script as you like for your own (or your businesses) purpose,
--                 but you must always leave this script header (the entire comment section), including the
--                 author, copyright and license sections as the first thing in the beginning of this file
--              3) You may NOT publish or distribute this script or any variation of it PUBLICLY
--                 (including, but not limited to uploading it to your public website or ftp server),
--                 instead just link to its location in blog.tanelpoder.com
--              4) You may distribute this script INTERNALLY in your company, for internal use only,
--                 for example when building a standard DBA toolset to be deployed to all
--                 servers or DBA workstations 
--
--
-- Thanks to:   Adrian Billington, Jamey Johnston, Marcus Mnnig, Hans-Peter Sloot
--              and Ronald Rood for bugfixes, additions and improvements
--
--------------------------------------------------------------------------------
--
--   The Session Snapper v4.06 BETA ( USE AT YOUR OWN RISK !!! )
--   (c) Tanel Poder ( http://blog.tanelpoder.com )
--
--
--    +-----=====O=== Welcome to The Session Snapper! (Yes, you are looking at a cheap ASCII
--   /                                                 imitation of a fish and a fishing rod.
--   |                                                 Nevertheless the PL/SQL code below the
--   |                                                 fish itself should be helpful for quick
--   |                                                 catching of relevant Oracle performance
--   |                                                 information.
--   |                                                 So I wish you happy... um... snapping?
--   |                                                )
--   |                       ......
--   |                       iittii,,....
--                       iiffffjjjjtttt,,
--                ..;;ttffLLLLffLLLLLLffjjtt;;..
--            ..ttLLGGGGGGLLffLLLLLLLLLLLLLLffjjii,,                        ..ii,,
--            ffGGffLLLLLLjjttjjjjjjjjffLLLLLLLLLLjjii..                ..iijj;;....
--          ffGGLLiittjjttttttiittttttttttffLLLLLLGGffii..            ;;LLLLii;;;;..
--        ffEEGGffiittiittttttttttiiiiiiiittjjjjffLLGGLLii..      iiLLLLLLttiiii,,
--      ;;ffDDLLiiiitt,,ttttttttttttiiiiiiiijjjjjjffLLLLffttiiiiffLLGGLLjjtttt;;..
--    ..ttttjjiitt,,iiiiiittttttttjjjjttttttttjjjjttttjjttttjjjjffLLDDGGLLttii..
--    iittiitttt,   ;;iittttttttjjjjjjjjjjttjjjjjjffffffjjjjjjjjjjLLDDGGLLtt;;..
--    jjjjttttii:. ..iiiiffLLGGLLLLLLLLffffffLLLLLLLLLLLLLLLLffffffLLLLLLfftt,,
--    iittttii,,;;,,ttiiiiLLLLffffffjjffffLLLLLLLLffLLffjjttttttttttjjjjffjjii..
--    ,,iiiiiiiiiittttttiiiiiiiiiijjffffLLLLLLLLffLLffttttttii;;;;iiiitttttttt;;..
--    ..iittttttffffttttiiiiiiiiiittttffjjjjffffffffttiittii::    ....,,;;iittii;;
--      ..;;iittttttttttttttttiiiiiittttttttttjjjjjjtttttt;;              ..;;ii;;..
--          ..;;;;iittttttjjttiittttttttttttttjjttttttttii..                  ....
--                ....;;;;ttjjttttiiiiii;;;;;;iittttiiii..
--                      ..;;ttttii;;....      ..;;;;....
--                        ..iiii;;..
--                          ..;;,,
--                            ....
--
--
--  Usage:
--
--      snapper.sql <ash[1-3]|stats|all>[,out][,trace][,pagesize=X][,gather=[s][t][w][l][e][b][a]]> <seconds_in_snap> <snapshot_count> <sid(s)_to_snap>
--
--          ash      - sample session activity ASH style, waits and SQL_IDs from gv$session and
--                     print a TOP SQL/wait report from these samples (this is the default from
--                     Snapper 3.0). The columns chosen for TOP calculation are defined in CONFIG
--                     section below.
--
--          ash=sql_id+event+wait_class
--                   - the above example illustrates that you can also specify the gv$session
--                     columns for TOP report yourself. The above example will show a TOP
--                     activity report grouped by SQL_ID + EVENT + WAIT_CLASS
--                     Note that the columns are separated by a "+" sign (as comma is a snapper 
--                     parameter separator, not ASH column separator)
--
--          ash1
--          ash2
--          ash3     - in addition to "ash" report you can have 3 more reported during the same
--                     snapper sampling snapshot. Just include ash1=col1+col2,ash2=col3+col4,...
--                     parameters if you want multiple TOP reports per Snapper snapshot
--
--          stats    - sample gv$sesstat,gv$sess_time_model,gv$session_event performance counters
--                     and report how much these stats increased (deltas) during Snapper run
--          all      - report both ASH and stats sections
--
--          out      - use dbms_output.put_line() for output. output will be seen only when
--                     Snapper run completes due to dbms_output limitations. This is the default.
--          trace    - write output to server process tracefile
--                     (you must have execute permission on sys.dbms_system.ksdwrt() for that,
--                      you can use both out and trace parameters together if you like )
--
--          pagesize - display header lines after X snapshots. if pagesize=0 don't display
--                     any headers. pagesize=-1 will display a terse header only once
--
--          gather   - if omitted, gathers s,t,w statistics (see below)
--                   - if specified, then gather following:
--
--                        Session-level stats:
--                          s - Session Statistics from gv$sesstat
--                          t - Session Time model info from gv$sess_time_model
--                          w - Session Wait statistics from gv$session_event and gv$session_wait
--
--                        Instance-level stats:
--                          l - instance Latch get statistics ( gets + immediate_gets )
--                          e - instance Enqueue lock get statistics
--                          b - buffer get Where statistics -- useful in versions up to 10.2.x
--                          a - All above
--
--          sinclude - if specified, then show only GV$SESSTAT stats which match the
--                     LIKE pattern of sinclude (REGEXP_LIKE in 10g+)
--          linclude - if specified, then show only GV$LATCH latch stats which match the
--                     LIKE pattern of linclude (REGEXP_LIKE in 10g+)
--          tinclude - if specified, then show only GV$SESS_TIME_MODEL stats which match the
--                     LIKE pattern of tinclude (REGEXP_LIKE in 10g+)
--          winclude - if specified, then show only GV$SESSION_EVENT wait stats which match the
--                     LIKE pattern of winclude (REGEXP_LIKE in 10g+)
--
--          you can combine above parameters in any order, separate them by commas
--          !!!don't use spaces as otherwise they are treated as next parameters by sqlplus   !!!
--          !!!if you want to use spaces, enclose the whole sqlplus parameter in doublequotes !!!
--
--      <seconds_in_snap> - the number of seconds between taking snapshots
--      <snapshot_count>  - the number of snapshots to take ( maximum value is power(2,31)-1 )
--
--      <sids_to_snap> can be either one sessionid, multiple sessionids separated by
--      commas or a SQL statement which returns a list of SIDs (if you need spaces
--      in that parameter text, enclose it in double quotes).
--
--      if you want to snap ALL sids, use "all" as value for
--      <sids_to_snap> parameter
--
--      alternatively you can used "select sid from gv$session" as value for <sids_to_snap>
--      parameter to capture all SIDs. you can write any query (with multiple and/or)
--      conditions to specify complex rules for capturing only the SIDs you want
--
--      starting from version 3.0 there are further session_id selection options available in 
--      instead of sid you can write such expressions for snapper's <sids_to_snap> parameter:
--
--         sid=123     -- take sid 123 only (the same as just writing 123)
--         user=tanel  -- take all sessions where username is 'tanel' (case insensitive)
--                     -- this is the same as writing following subquery for the 
--                     -- <sids_to_snap> parameter:
--                            select sid from gv$session where lower(username) like lower('tanel')
--                     
--         user=tanel% -- take all sessions where username begins with 'tanel%' (case insensitive)
--                     -- the = means actually LIKE in SQL terms in this script
-- 
--          spid=1234  -- all these 3 parameters do the same thing:
--          ospid=1234 -- they look up the sessions(s) where the processes OS PID=1234
--          pid=1234   -- this is useful for quickly looking up what some OS process is doing
--                     -- if it consumes too much of some resource
--          qc=123     
--          qcsid=123  -- show query coordinator and all PX slave sessions
--
--         program=sqlplus%     -- the following examples filter by corresponding gv$session coulmns 
--         machine=linux01      -- machine
--         osuser=oracle        -- os username
--         module=HR            -- module
--         "action=Find Order"  -- note the quotes because there is a space inside the parameter
--                              -- value
--         client_id=tanelpoder -- show only sessions where client_identifier is set to tanelpoder
--                              -- this is very useful in cases with (properly instrumented)
--                              -- connection pools
--
--
--      Note that if you want to change some "advanced" snapper configuration parameters
--      or default values then search for CONFIG in this file to see configurable
--      variable section
--
--
--  Examples:
--      NB! Read the online examples, these are more detailed and list script output too!
--
--          http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
--
--      @snapper ash,stats 1 1 515
--      (Output one 1-second snapshot of session 515 using dbms_output and exit
--       Wait, gv$sesstat and gv$sess_time_model statistics are reported by default
--       Starting from V3 the ASH style session activity report is shown as well)
--
--      @snapper stats,gather=w 1 1 515
--      (Output one 1-second snapshot of session 515 using dbms_output and exit
--       only Wait event statistics are reported, no ASH)
--
--      @snapper ash,gather=st 1 1 515
--      (Output one 1-second snapshot of session 515 using dbms_output and exit
--       only gv$sesstat and gv$sess_Time_model statistics are gathered + ASH)
--
--      @snapper trace,ash,gather=stw,pagesize=0 10 90 117,210,313
--      (Write 90 10-second snapshots into tracefile for session IDs 117,210,313
--       all statistics are reported, do not print any headers)
--
--      @snapper trace,ash 900 999999999 "select sid from v$session"
--      (Take a snapshot of ALL sessions every 15 minutes and write the output to trace,
--       loop (almost) forever )
--
--      @snapper out,trace 300 12 "select sid from v$session where username='APPS'"
--      (Take 12 5-minute snapshots of all sessions belonging to APPS user, write
--       output to both dbms_output and tracefile)
--
--  Notes:
--
--      Snapper does not currently detect if a session with given SID has
--      ended and been recreated between snapshots, thus it may report bogus
--      statistics for such sessions. The check and warning for that will be
--      implemented in a future version.
--
--------------------------------------------------------------------------------

set termout off tab off verify off linesize 999 trimspool on trimout on null ""

--debug:
-- set termout on serveroutput on

-- Get parameters (future snapper v4.x extended syntax: @snapper <options> <"begin"|"end"|sleep#> <"snap_name"|snap_count> <sid>)
define snapper_options="&1"
define   snapper_sleep="&2"
define   snapper_count="&3"
define     snapper_sid="&4"


-- The following code is required for making this script "dynamic" as due to
-- different Oracle versions, script parameters or granted privileges some
-- statements might not compile if not adjusted properly.

define _IF_ORA11_OR_HIGHER="--"
define _IF_LOWER_THAN_ORA11="--"
define _IF_DBMS_SYSTEM_ACCESSIBLE="/* dbms_system is not accessible" 
-- /*dummy*/ -- this "dummy" is here just for avoiding VIM syntax highlighter going crazy due to previous line
define _IF_X_ACCESSIBLE="--"

-- plsql_object_id columns available in v$session (from 10.2.0.3)
define _YES_PLSQL_OBJ_ID="--"  
define _NO_PLSQL_OBJ_ID=""
-- blocking_instance available in v$session (from 10.2)
define _YES_BLK_INST="--"      
define _NO_BLK_INST=""

-- snapper v4 manual before/after snapshotting
define _MANUAL_SNAPSHOT="--"   
define _USE_DBMS_LOCK=""

-- set the noprint's value to "noprint" if you don't want these temporary variables to show up in a sqlplus spool file
DEF noprint=""
col snapper_ora11higher    &noprint new_value _IF_ORA11_OR_HIGHER
col snapper_ora11lower     &noprint new_value _IF_LOWER_THAN_ORA11
col dbms_system_accessible &noprint new_value _IF_DBMS_SYSTEM_ACCESSIBLE
col x_accessible           &noprint new_value _IF_X_ACCESSIBLE
col no_plsql_obj_id        &noprint new_value _NO_PLSQL_OBJ_ID
col yes_plsql_obj_id       &noprint new_value _YES_PLSQL_OBJ_ID
col no_blk_inst            &noprint new_value _NO_BLK_INST
col yes_blk_inst           &noprint new_value _YES_BLK_INST
col manual_snapshot        &noprint new_value _MANUAL_SNAPSHOT
col use_dbms_lock          &noprint new_value _USE_DBMS_LOCK

col snapper_sid            &noprint new_value snapper_sid

-- sid_filter and inst_filter are the new RAC gv$ friendly way to filter sessions in Snapper v4 
def sid_filter="/**/"
def inst_filter="/**/"
col sid_filter             &noprint new_value sid_filter
col inst_filter            &noprint new_value inst_filter


-- initialize, precompute and determine stuff
var v                      varchar2(100)
var x                      varchar2(10)
var sid_filter             varchar2(4000)
var inst_filter            varchar2(4000)

-- this is here for a reason
-- im extracting the first word of the snapper_sid (if its a complex expression, not just a single SID)
-- by relying on how DEF and & assignment treat spaces in strings
def ssid_begin=&snapper_sid

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;

    tmp                    number;
    lv_sid_filter          varchar2(4000);
    lv_inst_filter         varchar2(4000);

    function get_filter(str in varchar2) return varchar2
    is
      ret varchar2(1000);
    begin
      if str like '%@%' then 
        --dbms_output.put_line('get_filter:1 str= '||str);
        ret := lower(trim(regexp_replace(substr(str,instr(str,'=')+1), '^(.+)@([[:digit:]\*]+)(.*)', '\1')));
      else
        --dbms_output.put_line('get_filter:2 str= '||str);
        ret := lower(trim(substr(str,instr(str,'=')+1)));
      end if;
      --dbms_output.put_line('get_filter = ' || ret);
      return ret;
    end get_filter;

begin
    -- compute inst_filter
    case 
        when regexp_instr('&ssid_begin','@')           = 0 then 
            lv_inst_filter := '/* inst_filter */ s.inst_id=USERENV(''Instance'')';
        when regexp_instr('&ssid_begin','@\*') > 0 or '&ssid_begin' like '(%' then 
            lv_inst_filter := '/* inst_filter */ 1=1';
        when regexp_instr('&ssid_begin','@\d+')        > 0 then 
            lv_inst_filter := 's.inst_id = ' || regexp_replace('&ssid_begin', '^(.+)@(\d+)(.*)', '\2');
        else
            lv_inst_filter := 's.inst_id=USERENV(''Instance'')';
        --when regexp_instr('&ssid_begin','@\d+')   > 0 then regexp_replace(snapper_sid, '^(.+)@\d+', '\1') || ' AND inst_id = ' || regexp_replace(snapper_sid, '^(.+)@(\d+)(.*)', '\2') 
    end case;

    -- compute sid_filter
    case
        when trim(lower('&ssid_begin')) like 'sid=%'       then lv_sid_filter   := 's.sid in ('||get_filter('&ssid_begin')||')'; --||trim(replace('&ssid_begin','sid=',''))||')';
        when trim(lower('&ssid_begin')) like 'user=%'      then lv_sid_filter   := 'lower(username) like '''         ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'username=%'  then lv_sid_filter   := 'lower(username) like '''         ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'machine=%'   then lv_sid_filter   := 'lower(machine) like '''          ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'program=%'   then lv_sid_filter   := 'lower(program) like '''          ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'service=%'   then lv_sid_filter   := 'lower(service_name) like '''     ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'module=%'    then lv_sid_filter   := 'lower(module) like '''           ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'action=%'    then lv_sid_filter   := 'lower(action) like '''           ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'osuser=%'    then lv_sid_filter   := 'lower(osuser) like '''           ||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'client_id=%' then lv_sid_filter   := 'lower(client_identifier) like '''||get_filter('&ssid_begin')||'''';
        when trim(lower('&ssid_begin')) like 'spid=%'      then lv_sid_filter   := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'ospid=%'     then lv_sid_filter   := '(s.inst_id,s.paddr) in (select /*+ UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'pid=%'       then lv_sid_filter   := '(s.inst_id,s.paddr) in (select /*+ NO_UNNEST */ inst_id,addr from gv$process where spid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'qcsid=%'     then lv_sid_filter   := '(s.inst_id,s.sid)   in (select /*+ NO_UNNEST */ inst_id,sid from gv$px_session where qcsid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'qc=%'        then lv_sid_filter   := '(s.inst_id,s.sid)   in (select /*+ NO_UNNEST */ inst_id,sid from gv$px_session where qcsid in ('||get_filter('&ssid_begin')||'))';
        when trim(lower('&ssid_begin')) like 'all%'        then lv_sid_filter   := '1=1';
        when trim(lower('&ssid_begin')) like 'bg%'         then lv_sid_filter   := 'type=''BACKGROUND''';
        when trim(lower('&ssid_begin')) like 'fg%'         then lv_sid_filter   := 'type=''USER''';
        when trim(lower('&ssid_begin')) like 'smon%'       then lv_sid_filter   := 'program like ''%(SMON)%''';
        when trim(lower('&ssid_begin')) like 'pmon%'       then lv_sid_filter   := 'program like ''%(PMON)%''';
        when trim(lower('&ssid_begin')) like 'ckpt%'       then lv_sid_filter   := 'program like ''%(CKPT)%''';
        when trim(lower('&ssid_begin')) like 'lgwr%'       then lv_sid_filter   := 'program like ''%(LGWR)%''';
        when trim(lower('&ssid_begin')) like 'dbwr%'       then lv_sid_filter   := 'program like ''%(DBW%)%''';
        when trim(lower('&ssid_begin')) like 'select%'     then /*lv_inst_filter  := '/* inst_filter2  1=1'; */ lv_sid_filter := q'{(s.inst_id,s.sid) in (&snapper_sid)}';
        --when trim(lower('&ssid_begin')) like 'select%'     then lv_sid_filter := '(s.inst_id,s.sid) in ('||regexp_replace(replace(q'{&snapper_sid}','',''''''), '^select ', 'select /*+ unnest */ ', 1, 1, 'i')||')'; -- '1=1'; lv_inst_filter := '1=1';
        --when trim(lower('&ssid_begin')) like 'with%'       then lv_sid_filter := '(s.inst_id,s.sid) in (&snapper_sid)'; -- '1=1'; lv_inst_filter := '1=1';
        when trim(lower('&ssid_begin')) like '(%'          then lv_inst_filter := '/* inst_filter2 */ 1=1'; lv_sid_filter := q'{(s.inst_id,s.sid) in (&snapper_sid)}'; -- '1=1'; lv_inst_filter := '1=1';
        else                                                    lv_sid_filter   := '/* sid_filter_else_cond */ s.sid in ('||get_filter('&ssid_begin')||')'; --lv_sid_filter := '/* sid_filter_else_cond */ s.sid in (&ssid_begin)';
    end case;

    :inst_filter := lv_inst_filter;
    :sid_filter  := lv_inst_filter||' and '||lv_sid_filter;

    -- 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)
    begin
        execute immediate 'select count(*) from x$kcbwh where rownum = 1' into tmp;
        :x:= ' '; -- x$ tables are accessible, so dont comment any lines out
    exception
        when others then null;
    end;

    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;
/

-- this is here for a reason
-- im extracting the first word of the snapper_sid (if its a complex expression, not just a single SID)
-- by relying on how DEF and & assignment treat spaces in strings
-- def ssid_begin=&snapper_sid

-- select * from (
--     select 
--         snapper_sid
--         --case 
--         --    when regexp_instr('&ssid_begin','@')      = 0 then snapper_sid || ' AND inst_id = sys_context(''userenv'', ''instance'')'
--         --    when regexp_instr('&ssid_begin','@\*')    > 0 then regexp_replace(snapper_sid, '^(.+)@\*', '\1')  -- all instances
--         --    when regexp_instr('&ssid_begin','@\d+')   > 0 then regexp_replace(snapper_sid, '^(.+)@\d+', '\1') || ' AND inst_id = ' || regexp_replace(snapper_sid, '^(.+)@(\d+)(.*)', '\2') 
--         --    else snapper_sid
--         --end snapper_sid 
--     from (
--         select
--             case 
-- --                when trim(lower('&ssid_begin')) like 'sid=%'       then trim(replace('&ssid_begin','sid=','')) 
--                 when trim(lower('&ssid_begin')) like 'sid=%'       then 'select inst_id,sid from gv$session where sid in ('||trim(replace('&ssid_begin','sid=',''))||')' 
--                 when trim(lower('&ssid_begin')) like 'user=%'      then 'select inst_id,sid from gv$session where lower(username) like '''||lower(trim(replace('&ssid_begin','user=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'username=%'  then 'select inst_id,sid from gv$session where lower(username) like '''||lower(trim(replace('&ssid_begin','username=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'machine=%'   then 'select inst_id,sid from gv$session where lower(machine) like '''||lower(trim(replace('&ssid_begin','machine=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'program=%'   then 'select inst_id,sid from gv$session where lower(program) like '''||lower(trim(replace('&ssid_begin','program=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'service=%'   then 'select inst_id,sid from gv$session where lower(service_name) like '''||lower(trim(replace('&ssid_begin','service=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'module=%'    then 'select inst_id,sid from gv$session where lower(module) like '''||lower(trim(replace('&ssid_begin','module=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'action=%'    then 'select inst_id,sid from gv$session where lower(action) like '''||lower(trim(replace('&ssid_begin','action=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'osuser=%'    then 'select inst_id,sid from gv$session where lower(osuser) like '''||lower(trim(replace('&ssid_begin','osuser=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'client_id=%' then 'select inst_id,sid from gv$session where lower(client_identifier) like '''||lower(trim(replace('&ssid_begin','client_id=','')))||'''' 
--                 when trim(lower('&ssid_begin')) like 'spid=%'      then 'select inst_id,sid from gv$session where paddr in (select addr from gv$process where spid in ('||lower(trim(replace('&ssid_begin','spid=','')))||'))' 
--                 when trim(lower('&ssid_begin')) like 'ospid=%'     then 'select inst_id,sid from gv$session where paddr in (select addr from gv$process where spid in ('||lower(trim(replace('&ssid_begin','ospid=','')))||'))' 
--                 when trim(lower('&ssid_begin')) like 'pid=%'       then 'select inst_id,sid from gv$session where paddr in (select addr from gv$process where spid in ('||lower(trim(replace('&ssid_begin','pid=','')))||'))' 
--                 when trim(lower('&ssid_begin')) like 'qcsid=%'     then 'select inst_id,sid from gv$px_session where qcsid in ('||lower(trim(replace('&ssid_begin','qcsid=','')))||')'  -- TODO use pxs
--                 when trim(lower('&ssid_begin')) like 'qc=%'        then 'select inst_id,sid from gv$px_session where qcsid in ('||lower(trim(replace('&ssid_begin','qc=','')))||')'     -- TODO use pxs
--                 when trim(lower('&ssid_begin')) = 'all'            then 'select inst_id,sid from gv$session'
--                 when trim(lower('&ssid_begin')) = 'bg'             then 'select inst_id,sid from gv$session where type=''BACKGROUND'''
--                 when trim(lower('&ssid_begin')) = 'fg'             then 'select inst_id,sid from gv$session where type=''USER'''
--                 when trim(lower('&ssid_begin')) = 'smon'           then 'select inst_id,sid from gv$session where program like ''%(SMON)%'''
--                 when trim(lower('&ssid_begin')) = 'pmon'           then 'select inst_id,sid from gv$session where program like ''%(PMON)%'''
--                 when trim(lower('&ssid_begin')) = 'ckpt'           then 'select inst_id,sid from gv$session where program like ''%(CKPT)%'''
--                 when trim(lower('&ssid_begin')) = 'lgwr'           then 'select inst_id,sid from gv$session where program like ''%(LGWR)%'''
--                 when trim(lower('&ssid_begin')) = 'dbwr'           then 'select inst_id,sid from gv$session where program like ''%(DBW%)%'''
--                 when trim(lower('&ssid_begin')) like 'select%'     then null
--                 when trim(lower('&ssid_begin')) like 'with%'       then null 
--                 --else 'select inst_id,sid from gv$session where sid in (&ssid_begin)'  
--                 else null
--             end snapper_sid -- put the result back to snapper_sid sqlplus value (if its not null)
--         from 
--             dual
--     )
-- )
-- where
--     snapper_sid is not null -- snapper_sid sqlplus variable value will not be replaced if this query doesnt return any rows
-- /

prompt snapper_sid = &snapper_sid

-- this query populates some sqlplus variables required for dynamic compilation used below

with mod_banner as (
    select
        replace(banner,'9.','09.') banner
    from
        v$version
    where rownum = 1
)
select
    decode(substr(banner, instr(banner, 'Release ')+8,2), '09', '--', '') snapper_ora10lower,
    decode(substr(banner, instr(banner, 'Release ')+8,2), '09', '',  '--') snapper_ora9,
    decode(substr(banner, instr(banner, 'Release ')+8,1), '1',  '',  '--') snapper_ora10higher,
    case when substr(banner, instr(banner, 'Release ')+8,2) >= '11' then '' else '--' end snapper_ora11higher,
    case when substr(banner, instr(banner, 'Release ')+8,2)  < '11' then '' else '--' end snapper_ora11lower,
    nvl(:v, '/* dbms_system is not accessible') dbms_system_accessible,
    nvl(:x, '--') x_accessible,
    case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2'     then ''   else '--' end yes_blk_inst,
    case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2'     then '--' else ''   end no_blk_inst,
    case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2.0.3' then ''   else '--' end yes_plsql_obj_id,
    case when substr( banner, instr(banner, 'Release ')+8, instr(substr(banner,instr(banner,'Release ')+8),' ') ) >= '10.2.0.3' then '--' else ''   end no_plsql_obj_id,
    case when lower('&snapper_options') like '%,begin%' or lower('&snapper_options') like 'begin%' or lower('&snapper_options') like '%,end%' or lower('&snapper_options') like 'end%' then '' else '--' end manual_snapshot,
    case when lower('&snapper_options') like '%,begin%' or lower('&snapper_options') like 'begin%' or lower('&snapper_options') like '%,end%' or lower('&snapper_options') like 'end%' then '--' else '' end use_dbms_lock,
    :sid_filter  sid_filter,
    :inst_filter inst_filter
from
    mod_banner
/

-- on different lines as sql developer might not like this command
set termout on
set serveroutput on size unlimited format wrapped

prompt Sampling SID &4 with interval &snapper_sleep seconds, taking &snapper_count snapshots...

-- main()
-- let the Snapping start!!!
declare
    -- Snapper start
    -- 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 (ts timestamp, stype varchar2(4), inst_id number, sid number, statistic# number, value number, event_count number );
    type stab is table of srec index by pls_integer;
    type ltab is table of srec index by varchar2(100); -- lookup tab for various average calculation
    s1 stab;
    s2 stab;
    l1 ltab;
    l2 ltab;

    type snrec is record (stype varchar2(4), statistic# number, name varchar2(100));
    type sntab is table of snrec index by pls_integer;
    sn_tmp sntab;
    sn sntab;

    type sntab_reverse is table of snrec index by varchar2(100); -- used for looking up stat id from stat name
    sn_reverse sntab_reverse;

    tmp_varchar2 varchar2(1000); -- misc

    function get_useful_average(c in srec /* curr_metric */, p in srec /* all_prev_metrics */) return varchar2;

    type tmp_sestab is table of gv$session%rowtype index by pls_integer;
    type sestab is table of gv$session%rowtype index by varchar2(20);
        
    g_sessions           sestab;
    g_empty_sessions     sestab;

    type hc_tab is table of number index by pls_integer; -- index is sql hash value
    type ses_hash_tab is table of hc_tab index by pls_integer; -- index is SID
    
    g_ses_hash_tab       ses_hash_tab;
    g_empty_ses_hash_tab ses_hash_tab;

    -- dbms_debug_vc2coll is a built-in collection present in every oracle db
    g_ash                sys.dbms_debug_vc2coll := new sys.dbms_debug_vc2coll();
    g_empty_ash          sys.dbms_debug_vc2coll := new sys.dbms_debug_vc2coll();
    g_snap1              sys.dbms_debug_vc2coll; 
    g_snap2              sys.dbms_debug_vc2coll; 

    g_ash_samples_taken  number := 0;

    g_count_statname  number;
    g_count_eventname number;

    g_mysid           number;

    i number;
    a number;
    b number;

    c number;
    delta number;
    evcnt number;
    changed_values number;
    pagesize number:=99999999999999;
    missing_values_s1 number := 0;
    missing_values_s2 number := 0;
    disappeared_sid   number := 0;
    lv_curr_sid       number := 0; -- used for determining whether to print an empty line between session stats
    d1 timestamp(6);
    d2 timestamp(6);
    ash_date1 date;
    ash_date2 date;
    lv_gather        varchar2(1000);
    gv_header_string varchar2(1000);
    lv_data_string   varchar2(1000);

    lv_ash           varchar2(1000);
    lv_stats         varchar2(1000);

    gather_stats     number := 0;
    gather_ash       number := 0;

    g_snap_begin    varchar2(1000);
    g_snap_end      varchar2(1000);

    -- CONFIGURABLE STUFF --

    -- this sets what are the default ash sample TOP reporting group by columns
    g_ash_columns     varchar2(1000) := 'inst_id + sql_id + sql_child_number + event + wait_class';
    g_ash_columns1    varchar2(1000) := 'inst_id + event + p1 + wait_class';
    g_ash_columns2    varchar2(1000) := 'inst_id + sid + user + machine + program';
    g_ash_columns3    varchar2(1000) := 'inst_id + plsql_object_id + plsql_subprogram_id + sql_id';
		
    -- output column configuration
    output_header     number := 0; -- 1=true 0=false
    output_username   number := 1; -- v$session.username
    output_inst       number := 0; -- inst
    output_sid        number := CASE WHEN dbms_utility.is_cluster_database = TRUE THEN 0 ELSE 1 END; -- just sid 
    output_inst_sid   number := CASE WHEN dbms_utility.is_cluster_database = TRUE THEN 1 ELSE 0 END; -- inst_id and sid together 
    output_time       number := 0; -- time of snapshot start
    output_seconds    number := 0; -- seconds in snapshot (shown in footer of each snapshot too)
    output_stype      number := 1; -- statistic type (WAIT,STAT,TIME,ENQG,LATG,...)
    output_sname      number := 1; -- statistic name
    output_delta      number := 1; -- raw delta
    output_delta_s    number := 0; -- raw delta normalized to per second
    output_hdelta     number := 0; -- human readable delta
    output_hdelta_s   number := 1; -- human readable delta normalized to per second
    output_percent    number := 1; -- percent of total time/samples
    output_eventcnt   number := 1; -- wait event count
    output_eventcnt_s number := 1; -- wait event count
    output_eventavg   number := 1; -- average wait duration
    output_pcthist    number := 1; -- percent of total visual bar (histogram) -- Histograms seem to work for me on 9.2.0.7 + - JBJ2)

    -- column widths in ASH report output
    w_inst_id                     number :=  4;
    w_sid                         number :=  6;
    w_username                    number := 20;
    w_machine                     number := 20;
    w_terminal                    number := 20;
    w_program                     number := 25;
    w_event                       number := 35;
    w_wait_class                  number := 15;
    w_state                       number :=  8;
    w_p1                          number := 20;
    w_p2                          number := 20;
    w_p3                          number := 20;
    w_row_wait_obj#               number := 10;
    w_row_wait_file#              number :=  6;
    w_row_wait_block#             number := 10;
    w_row_wait_row#               number :=  6;
    w_blocking_session_status     number := 15;
    w_blocking_instance           number := 12;
    w_blocking_session            number := 12;
    w_sql_hash_value              number := 12;
    w_sql_id                      number := 15;
    w_sql_child_number            number :=  9;
    w_plsql_entry_object_id       number := 10;
    w_plsql_entry_subprogram_id   number := 10;
    w_plsql_object_id             number := 10;
    w_plsql_subprogram_id         number := 10;
    w_module                      number := 25;
    w_action                      number := 25;
    w_client_identifier           number := 25;
    w_service_name                number := 25;

    w_activity_pct                number :=  7;

    -- END CONFIGURABLE STUFF --

    -- constants for ash collection extraction from the vc2 collection
    s_inst_id                      constant number :=  1 ;
    s_sid                          constant number :=  2 ;
    s_username                     constant number :=  3 ;
    s_machine                      constant number :=  4 ;
    s_terminal                     constant number :=  5 ;
    s_program                      constant number :=  6 ;
    s_event                        constant number :=  7 ;
    s_wait_class                   constant number :=  8 ;
    s_state                        constant number :=  9 ;
    s_p1                           constant number := 10 ;
    s_p2                           constant number := 11 ;
    s_p3                           constant number := 12 ;
    s_row_wait_obj#                constant number := 13 ;
    s_row_wait_file#               constant number := 14 ;
    s_row_wait_block#              constant number := 15 ;
    s_row_wait_row#                constant number := 16 ;
    s_blocking_session_status      constant number := 17 ;
    s_blocking_instance            constant number := 18 ;
    s_blocking_session             constant number := 19 ;
    s_sql_hash_value               constant number := 20 ;
    s_sql_id                       constant number := 21 ;
    s_sql_child_number             constant number := 22 ;
    s_plsql_entry_object_id        constant number := 23 ;
    s_plsql_entry_subprogram_id    constant number := 24 ;
    s_plsql_object_id              constant number := 25 ;
    s_plsql_subprogram_id          constant number := 26 ;
    s_module                       constant number := 27 ;
    s_action                       constant number := 28 ;
    s_client_identifier            constant number := 29 ;
    s_service_name                 constant number := 30 ;

    -- constants for ash collection reporting, which columns to show in report
    c_inst_id                      constant number := power(2, s_inst_id                  );
    c_sid                          constant number := power(2, s_sid                      );
    c_username                     constant number := power(2, s_username                 );
    c_machine                      constant number := power(2, s_machine                  );
    c_terminal                     constant number := power(2, s_terminal                 );
    c_program                      constant number := power(2, s_program                  );
    c_event                        constant number := power(2, s_event                    );
    c_wait_class                   constant number := power(2, s_wait_class               );
    c_state                        constant number := power(2, s_state                    );
    c_p1                           constant number := power(2, s_p1                       );
    c_p2                           constant number := power(2, s_p2                       );
    c_p3                           constant number := power(2, s_p3                       );
    c_row_wait_obj#                constant number := power(2, s_row_wait_obj#            );
    c_row_wait_file#               constant number := power(2, s_row_wait_file#           );
    c_row_wait_block#              constant number := power(2, s_row_wait_block#          );
    c_row_wait_row#                constant number := power(2, s_row_wait_row#            );
    c_blocking_session_status      constant number := power(2, s_blocking_session_status  );
    c_blocking_instance            constant number := power(2, s_blocking_instance        );
    c_blocking_session             constant number := power(2, s_blocking_session         );
    c_sql_hash_value               constant number := power(2, s_sql_hash_value           );
    c_sql_id                       constant number := power(2, s_sql_id                   );
    c_sql_child_number             constant number := power(2, s_sql_child_number         );
    c_plsql_entry_object_id        constant number := power(2, s_plsql_entry_object_id    );
    c_plsql_entry_subprogram_id    constant number := power(2, s_plsql_entry_subprogram_id);
    c_plsql_object_id              constant number := power(2, s_plsql_object_id          );
    c_plsql_subprogram_id          constant number := power(2, s_plsql_subprogram_id      );
    c_module                       constant number := power(2, s_module                   );
    c_action                       constant number := power(2, s_action                   );
    c_client_identifier            constant number := power(2, s_client_identifier        );
    c_service_name                 constant number := power(2, s_service_name             );


  /*---------------------------------------------------
    -- 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)
            or
           (getopt('&snapper_options', 'out') is null and getopt('&snapper_options', 'trace') is 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

   /*---------------------------------------------------
   -- function for converting interval datatype to microseconds
   ---------------------------------------------------*/
   function get_seconds(i interval day to second) return number
   as
   begin
       return to_number(extract(second from i)) +
              to_number(extract(minute from i)) * 60 + 
              to_number(extract(hour   from i)) * 60 * 60 + 
              to_number(extract(day    from i)) * 60 * 60 * 24;
   end get_seconds;

  /*---------------------------------------------------
    -- proc for outputting data, utilizing global vars
    ---------------------------------------------------*/
    procedure fout is
        l_output_username VARCHAR2(100);
        gsid varchar2(20);
    begin
        --if s2(b).stype='WAIT' then output( 'DEBUG WAIT ' || sn(s2(b).statistic#).name || ' ' || delta ); end if;
        --output( 'DEBUG, Entering fout(), b='||to_char(b)||' sn(s2(b).statistic#='||s2(b).statistic# );
        --output( 'DEBUG, In fout(), a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count);

        gsid := trim(to_char(s2(b).inst_id))||','||trim(to_char(s2(b).sid));

        if output_username = 1 then
            begin
                l_output_username := nvl( g_sessions(gsid).username, substr(g_sessions(gsid).program, instr(g_sessions(gsid).program,'(')) );
            exception
                when no_data_found then l_output_username := 'error';
                when others then raise;
            end;
        end if;

        -- DEBUG
        --output('before');
        --output (CASE WHEN output_eventavg    = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN lpad(tptformat(delta / CASE WHEN evcnt = 0 THEN 1 ELSE evcnt END, s2(b).stype), 10, ' ')||' average wait' ELSE get_useful_average(s2(b), s1(a)) END END);
        --output('after');

        output( CASE WHEN output_header      = 1 THEN 'SID= ' END
             || CASE WHEN output_inst        = 1 THEN to_char(s2(b).inst_id, '9999')||', ' END
             || CASE WHEN output_sid         = 1 THEN to_char(s2(b).sid,'999999')||', ' END
             || CASE WHEN output_inst_sid    = 1 THEN to_char(s2(b).sid,'99999')||' '||lpad('@'||trim(to_char(s2(b).inst_id, '99')),3)||', ' END
             || CASE WHEN output_username    = 1 THEN rpad(CASE s2(b).sid WHEN -1 THEN ' ' ELSE NVL(l_output_username, ' ') END, 10)||', ' END
             || CASE WHEN output_time        = 1 THEN to_char(d1, 'YYYYMMDD HH24:MI:SS')||', ' END
             || CASE WHEN output_seconds     = 1 THEN to_char(case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end, '9999999')||', ' END
             || CASE WHEN output_stype       = 1 THEN s2(b).stype||', ' END
             || CASE WHEN output_sname       = 1 THEN rpad(sn(s2(b).statistic#).name, 58, ' ')||', ' END
             || CASE WHEN output_delta       = 1 THEN to_char(delta, '999999999999')||', ' END
             || CASE WHEN output_delta_s     = 1 THEN to_char(delta/(case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end),'999999999')||', ' END
             || CASE WHEN output_hdelta      = 1 THEN lpad(tptformat(delta, s2(b).stype), 10, ' ')||', ' END
             || CASE WHEN output_hdelta_s    = 1 THEN lpad(tptformat(delta/(case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end ), s2(b).stype), 10, ' ')||', ' END
             || CASE WHEN output_percent     = 1 THEN CASE WHEN s2(b).stype IN ('TIME','WAIT') THEN to_char(delta/CASE get_seconds(d2-d1) WHEN 0 THEN &snapper_sleep ELSE get_seconds(d2-d1) END / 10000, '9999.9')||'%' ELSE '        ' END END||', '
             || CASE WHEN output_pcthist     = 1 THEN CASE WHEN s2(b).stype IN ('TIME','WAIT') THEN rpad(rpad('[', ceil(round(delta/CASE get_seconds(d2-d1) WHEN 0 THEN &snapper_sleep ELSE get_seconds(d2-d1) END / 100000,1))+1, CASE WHEN s2(b).stype IN ('WAIT') THEN 'W' WHEN sn(s2(b).statistic#).name = 'DB CPU' THEN '@' ELSE '#' END),11,' ')||']' ELSE '            ' END END||', '
             || CASE WHEN output_eventcnt    = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN to_char(evcnt, '99999999') ELSE '         ' END END||', '
             || CASE WHEN output_eventcnt_s  = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN lpad(tptformat((evcnt / case get_seconds(d2-d1) when 0 then &snapper_sleep else get_seconds(d2-d1) end ), 'STAT' ), 10, ' ') ELSE '          ' END END||', '
             || CASE WHEN output_eventavg    = 1 THEN CASE WHEN s2(b).stype IN ('WAIT') THEN lpad(tptformat(delta / CASE WHEN evcnt = 0 THEN 1 ELSE evcnt END, s2(b).stype), 10, ' ')||' average wait' ELSE get_useful_average(s2(b), s1(a)) END END
        );

    end;

  
  /*---------------------------------------------------
   -- lookup stat delta helper calculator (l2.value - l1.value)
   ---------------------------------------------------*/
    function get_delta(metric_id in varchar2) return number
    is
        rec1 srec;
        rec2 srec;

        val1 number;
        val2 number;

        d    number;
    begin
        begin 
            val1 := l1(metric_id).value;
        exception
            when no_data_found then val1 := 0;
        end;

        begin
            val2 := l2(metric_id).value;
        exception
            when no_data_found then val2 := 0;
        end;

        d := val2 - NVL(val1, 0);
        return d;
    end get_delta;

  /*---------------------------------------------------
   -- delta helper function for convenience - it allows to specify any metric delta, if not specified then get current one
   ---------------------------------------------------*/
    function gd(c in srec, metric_type in varchar2 DEFAULT NULL, metric_name in varchar2 DEFAULT NULL) return number
    is
        str varchar2(1000);
        tmp_delta number;
    begin
        if metric_type || metric_name  is null then
            str := c.stype||','||trim(to_char(c.inst_id))||','||trim(to_char(c.sid))||','||trim(to_char(c.statistic#,'999999999999999999999999'));
        else 
            begin
                str := trim(metric_type)||','||trim(to_char(c.inst_id))||','||trim(to_char(c.sid))||','||trim(to_char(sn_reverse(metric_type||','||metric_name).statistic#));
            exception
                when no_data_found then return 0;
            end;
        end if;
        tmp_delta := get_delta(str);
        --output('tmp_delta '||c.stype||' '||tmp_delta);
        return tmp_delta;
        -- return get_delta(str);
    end; 

    /*---------------------------------------------------
     -- function for calculating useful averages and ratios between metrics
     ---------------------------------------------------*/
    function get_useful_average(c in srec /* curr_metric */, p in srec /* all_prev_metrics */) return varchar2
    is
        ret varchar2(1000);
        mt  varchar2(100) := c.stype; -- metric_type
        mn  varchar2(100) := sn(c.statistic#).name; -- metric_name
    begin
        case 
          when mt = 'STAT' then
            case 
              when mn = 'bytes sent via SQL*Net to client'         then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'SQL*Net roundtrips to/from client'),0), mt), 10) || ' bytes per roundtrip' ;
              when mn = 'bytes receive via SQL*Net from client'    then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'SQL*Net roundtrips to/from client'),0), mt), 10) || ' bytes per roundtrip' ;
              when mn = 'redo size'                                then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'user commits'                     ),0), mt), 10) || ' bytes per user commit';
              when mn = 'execute count'                            then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'parse count (total)'              ),0), mt), 10) || ' executions per parse';
              when mn = 'parse count (total)'                      then ret := lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'parse count (hard)'               ),0), mt), 10) || ' softparses per hardparse';
              when mn = 'session cursor cache hits'                then ret := lpad( tptformat(gd(c) -       (gd(c, 'STAT', 'parse count (total)'              )  ), mt), 10) || ' softparses avoided thanks to cursor cache';
              when mn = 'buffer is pinned count'                   then ret := lpad( tptformat(gd(c) / nullif(gd(c) + gd(c, 'STAT', 'session logical reads'),0) * 100, mt), 10) || ' % buffer gets avoided thanks to buffer pin caching';
              else ret :=                                                      lpad( tptformat(gd(c) / nullif(gd(c, 'STAT', 'execute count'),0), mt), 10) || ' per execution' ;
            end case; -- mt=stat, mn
          when mt = 'TIME' then
            -- this is ugly and wrong at the moment - will refactor some day
            case
              when mn = 'DB time'                                  then ret := lpad(tptformat(get_seconds(d2 - d1)*1000000 - gd(c) - nullif(gd(c, 'DB CPU', 'TIME')
                                                                                                            - gd(c, 'WAIT', 'pmon timer')
                                                                                                            - gd(c, 'WAIT', 'VKTM Logical Idle Wait')
                                                                                                            - gd(c, 'WAIT', 'VKTM Init Wait for GSGA')
                                                                                                            - gd(c, 'WAIT', 'IORM Scheduler Slave Idle Wait')
                                                                                                            - gd(c, 'WAIT', 'rdbms ipc message')
                                                                                                            - gd(c, 'WAIT', 'i/o slave wait')
                                                                                                            - gd(c, 'WAIT', 'VKRM Idle')
                                                                                                            - gd(c, 'WAIT', 'wait for unread message on broadcast channel')
                                                                                                            - gd(c, 'WAIT', 'wait for unread message on multiple broadcast channels')
                                                                                                            - gd(c, 'WAIT', 'class slave wait')
                                                                                                            - gd(c, 'WAIT', 'KSV master wait')
                                                                                                            - gd(c, 'WAIT', 'PING')
                                                                                                            - gd(c, 'WAIT', 'watchdog main loop')
                                                                                                            - gd(c, 'WAIT', 'DIAG idle wait')
                                                                                                            - gd(c, 'WAIT', 'ges remote message')
                                                                                                            - gd(c, 'WAIT', 'gcs remote message')
                                                                                                            - gd(c, 'WAIT', 'heartbeat monitor sleep')
                                                                                                            - gd(c, 'WAIT', 'GCR sleep')
                                                                                                            - gd(c, 'WAIT', 'SGA: MMAN sleep for component shrink')
                                                                                                            - gd(c, 'WAIT', 'MRP redo arrival')
                                                                                                            - gd(c, 'WAIT', 'LNS ASYNC archive log')
                                                                                                            - gd(c, 'WAIT', 'LNS ASYNC dest activation')
                                                                                                            - gd(c, 'WAIT', 'LNS ASYNC end of log')
                                                                                                            - gd(c, 'WAIT', 'simulated log write delay')
                                                                                                            - gd(c, 'WAIT', 'LGWR real time apply sync')
                                                                                                            - gd(c, 'WAIT', 'parallel recovery slave idle wait')
                                                                                                            - gd(c, 'WAIT', 'LogMiner builder: idle')
                                                                                                            - gd(c, 'WAIT', 'LogMiner builder: branch')
                                                                                                            - gd(c, 'WAIT', 'LogMiner preparer: idle')
                                                                                                            - gd(c, 'WAIT', 'LogMiner reader: log (idle)')
                                                                                                            - gd(c, 'WAIT', 'LogMiner reader: redo (idle)')
                                                                                                            - gd(c, 'WAIT', 'LogMiner client: transaction')
                                                                                                            - gd(c, 'WAIT', 'LogMiner: other')
                                                                                                            - gd(c, 'WAIT', 'LogMiner: activate')
                                                                                                            - gd(c, 'WAIT', 'LogMiner: reset')
                                                                                                            - gd(c, 'WAIT', 'LogMiner: find session')
                                                                                                            - gd(c, 'WAIT', 'LogMiner: internal')
                                                                                                            - gd(c, 'WAIT', 'Logical Standby Apply Delay')
                                                                                                            - gd(c, 'WAIT', 'parallel recovery coordinator waits for slave cleanup')
                                                                                                            - gd(c, 'WAIT', 'parallel recovery control message reply')
                                                                                                            - gd(c, 'WAIT', 'parallel recovery slave next change')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Txn Recovery Start')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Txn Recovery Reply')
                                                                                                            - gd(c, 'WAIT', 'fbar timer')
                                                                                                            - gd(c, 'WAIT', 'smon timer')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Metadata Update')
                                                                                                            - gd(c, 'WAIT', 'Space Manager: slave idle wait')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Index Merge Reply')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Index Merge Execute')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Index Merge Close')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: kdcph_mai')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: kdcphc_ack')
                                                                                                            - gd(c, 'WAIT', 'shared server idle wait')
                                                                                                            - gd(c, 'WAIT', 'dispatcher timer')
                                                                                                            - gd(c, 'WAIT', 'cmon timer')
                                                                                                            - gd(c, 'WAIT', 'pool server timer')
                                                                                                            - gd(c, 'WAIT', 'JOX Jit Process Sleep')
                                                                                                            - gd(c, 'WAIT', 'jobq slave wait')
                                                                                                            - gd(c, 'WAIT', 'pipe get')
                                                                                                            - gd(c, 'WAIT', 'PX Deque wait')
                                                                                                            - gd(c, 'WAIT', 'PX Idle Wait')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Join ACK')
                                                                                                            - gd(c, 'WAIT', 'PX Deq Credit: need buffer')
                                                                                                            - gd(c, 'WAIT', 'PX Deq Credit: send blkd')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Msg Fragment')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Parse Reply')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Execute Reply')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Execution Msg')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Table Q Normal')
                                                                                                            - gd(c, 'WAIT', 'PX Deq: Table Q Sample')
                                                                                                            - gd(c, 'WAIT', 'Streams fetch slave: waiting for txns')
                                                                                                            - gd(c, 'WAIT', 'Streams: waiting for messages')
                                                                                                            - gd(c, 'WAIT', 'Streams capture: waiting for archive log')
                                                                                                            - gd(c, 'WAIT', 'single-task message')
                                                                                                            - gd(c, 'WAIT', 'SQL*Net message from client')
                                                                                                            - gd(c, 'WAIT', 'SQL*Net vector message from client')
                                                                                                            - gd(c, 'WAIT', 'SQL*Net vector message from dblink')
                                                                                                            - gd(c, 'WAIT', 'PL/SQL lock timer')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: emn coordinator idle wait')
                                                                                                            - gd(c, 'WAIT', 'EMON slave idle wait')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: waiting for messages in the queue')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: waiting for time management or cleanup tasks')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: delete acknowledged messages')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: deallocate messages from Streams Pool')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: qmn coordinator idle wait')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: qmn slave idle wait')
                                                                                                            - gd(c, 'WAIT', 'Streams AQ: RAC qmn coordinator idle wait')
                                                                                                            - gd(c, 'WAIT', 'HS message to agent')
                                                                                                            - gd(c, 'WAIT', 'ASM background timer')
                                                                                                            - gd(c, 'WAIT', 'auto-sqltune: wait graph update')
                                                                                                            - gd(c, 'WAIT', 'WCR: replay client notify')
                                                                                                            - gd(c, 'WAIT', 'WCR: replay clock')
                                                                                                            - gd(c, 'WAIT', 'WCR: replay paused')
                                                                                                            - gd(c, 'WAIT', 'JS external job')
                                                                                                            - gd(c, 'WAIT', 'cell worker idle')
                                                                                           ,0) , mt), 10) || ' unaccounted time' ;
              else null;
            end case; -- mt=time, mn
        end case; -- mt
        return ret;
    end get_useful_average;
 
  /*---------------------------------------------------
    -- 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_num = 0 then return '0'; end if;
        if p_num IS NULL then return '~'; end if;

        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

      -- dbms_output.put('p_parvalues = ['||p_parvalues||'] ' );
      -- dbms_output.put('p_extract = ['||p_extract||'] ' );

        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;

      -- dbms_output.put_line('ret = ['||replace(ret,chr(0),'\0')||']');

        return ret;

    end; -- getopt

  /*---------------------------------------------------
    -- proc for getting session list with username, osuser, machine etc
    ---------------------------------------------------*/
   procedure get_sessions is
       tmp_sessions tmp_sestab;
   begin

       select /*+ unnest */
           *
       bulk collect into
           tmp_sessions
       from
           gv$session s
       where
           1=1
       and (
           &sid_filter
       ) ;
           --(inst_id,sid) in (&snapper_sid);

       g_sessions := g_empty_sessions;

       for i in 1..tmp_sessions.count loop
           g_sessions(tmp_sessions(i).inst_id||','||tmp_sessions(i).sid) := tmp_sessions(i);
       end loop;

   end; -- get_sessions

  /*---------------------------------------------------
    -- function for getting session list with username, osuser, machine etc
    -- this func does not update the g_sessions global array but returns session info as return value
    ---------------------------------------------------*/
   function get_sessions return sestab is
       tmp_sessions tmp_sestab;
       l_return_sessions sestab; 
   begin

       select /*+ unnest */
           *
       bulk collect into
           tmp_sessions
       from
           gv$session s
       where
           1=1
       and (&sid_filter) ;
         --(inst_id,sid) in (&snapper_sid);

       for i in 1..tmp_sessions.count loop
           --output('get_sessions i='||i||' sid='||tmp_sessions(i).sid);
           l_return_sessions(tmp_sessions(i).inst_id||','||tmp_sessions(i).sid) := tmp_sessions(i);
       end loop;
       
       return l_return_sessions;

   end; -- get_sessions



    /*---------------------------------------------------
    -- functions for extracting and converting gv$session
    -- records to varchar2
    ---------------------------------------------------*/
   function sitem(p in varchar2) return varchar2 as
   begin
      return '<'||translate(p, '<>', '__')||'>';
   end; -- sitem varchar2

   function sitem(p in number) return varchar2 as
   begin
      return '<'||to_char(p)||'>';
   end; -- sitem number 

   function sitem(p in date) return varchar2 as
   begin
      return '<'||to_char(p, 'YYYY-MM-DD HH24:MI:SS')||'>';
   end; -- sitem date

   function sitem_raw(p in raw) return varchar2 as
   begin
      return '<'||upper(rawtohex(p))||'>';
   end; -- sitem_raw


   /*---------------------------------------------------
   -- proc for resetting the snapper ash array
   ---------------------------------------------------*/
   procedure reset_ash is
   begin
      g_ash_samples_taken := 0;
      -- clear g_ash
      g_ash := new sys.dbms_debug_vc2coll(); 
   end; -- reset_ash


   /*---------------------------------------------------
   -- proc for getting ash style samples from gv$session 
   ---------------------------------------------------*/
   procedure extract_ash is
       ash_i varchar2(30);
       s gv$session%rowtype;
       
   begin
       -- keep track how many times we sampled gv$session so we could calculate averages later on
       g_ash_samples_taken := g_ash_samples_taken + 1;
       --output('g_sessions.count='||g_sessions.count);
       ash_i := g_sessions.first;

       while ash_i is not null loop 

          s := g_sessions(ash_i);
          if -- active, on cpu
              (s.status = 'ACTIVE' and s.state != 'WAITING' and s.sid != g_mysid) 
          or -- active, waiting for non-idle wait
              (s.status = 'ACTIVE' and s.state = 'WAITING' and s.wait_class != 'Idle' and s.sid != g_mysid) 
          then 
             --output('extract_ash: i='||i||' sid='||s.sid||' hv='||s.sql_hash_value||' sqlid='||s.sql_id);
             -- if not actually waiting for anything, clear the past wait event details
             if s.state != 'WAITING' then 
                 s.state:='ON CPU';
                 s.event:='ON CPU';
                 s.wait_class:='ON CPU'; --TODO: What do we need to do for 9i here?
                 s.p1:=NULL;
                 s.p2:=NULL;
                 s.p3:=NULL;
             end if;

             g_ash.extend;

             -- max length 1000 bytes (due to dbms_debug_vc2coll)
             g_ash(g_ash.count) := substr(
                            sitem(s.inst_id)                   --  1
                          ||sitem(s.sid)                       --  2
                          ||sitem(s.username)                  --  3  -- 30 bytes
                          ||sitem(s.machine)                   --  4  -- 64 bytes
                          ||sitem(s.terminal)                  --  5  -- 30 bytes
                          ||sitem(s.program)                   --  6  -- 48 bytes
                          ||sitem(s.event)                     --  7  -- 64 bytes
                          ||sitem(s.wait_class)                --  8  -- 64 bytes, 10g+
                          ||sitem(s.state)                     --  9 
                          ||sitem(s.p1)                        -- 10
                          ||sitem(s.p2)                        -- 11
                          ||sitem(s.p3)                        -- 12
                          ||sitem(s.row_wait_obj#)             -- 13
                          ||sitem(s.row_wait_file#)            -- 14
                          ||sitem(s.row_wait_block#)           -- 15
                          ||sitem(s.row_wait_row#)             -- 16
                          ||sitem(s.blocking_session_status)   -- 17   -- 10g+
           &_NO_BLK_INST  ||sitem('N/A')                       -- 17   -- 10gR2+
          &_YES_BLK_INST  ||sitem(s.blocking_instance)         -- 18   -- 10gR2+
                          ||sitem(s.blocking_session)          -- 19   -- 10g+
                          ||sitem(s.sql_hash_value)            -- 20
                          ||sitem(s.sql_id)                    -- 21   -- 10g+
                          ||sitem(s.sql_child_number)          -- 22   -- 10g+
      &_NO_PLSQL_OBJ_ID   ||sitem('N/A')                       -- 23
      &_NO_PLSQL_OBJ_ID   ||sitem('N/A')                       -- 24
      &_NO_PLSQL_OBJ_ID   ||sitem('N/A')                       -- 25
      &_NO_PLSQL_OBJ_ID   ||sitem('N/A')                       -- 22
     &_YES_PLSQL_OBJ_ID   ||sitem(s.plsql_entry_object_id)     -- 23
     &_YES_PLSQL_OBJ_ID   ||sitem(s.plsql_entry_subprogram_id) -- 24
     &_YES_PLSQL_OBJ_ID   ||sitem(s.plsql_object_id)           -- 25
     &_YES_PLSQL_OBJ_ID   ||sitem(s.plsql_subprogram_id)       -- 26
                          ||sitem(s.module)                    -- 27  -- 48 bytes
                          ||sitem(s.action)                    -- 28  -- 32 bytes
                          ||sitem(s.client_identifier)         -- 29  -- 64 bytes
                          ||sitem(s.service_name)              -- 30  -- 64 bytes, 10g+
                    , 1, 1000);
             
          end if; -- sample is of an active session
          
          ash_i := g_sessions.next(ash_i);
       
       end loop;

   exception
       when no_data_found then output('error in extract_ash(): no_data_found for item '||i);
   end; -- extract_ash


   /*---------------------------------------------------
    -- proc for querying performance data into collections
    ---------------------------------------------------*/
   procedure snap( p_snapdate out timestamp, p_stats out stab, l_stats out ltab, p_stats_string out sys.dbms_debug_vc2coll) is

        lv_include_stat  varchar2(1000) := nvl( lower(getopt('&snapper_options', 'sinclude=' )), '%');
        lv_include_latch varchar2(1000) := nvl( lower(getopt('&snapper_options', 'linclude=' )), '%');
        lv_include_time  varchar2(1000) := nvl( lower(getopt('&snapper_options', 'tinclude=' )), '%');
        lv_include_wait  varchar2(1000) := nvl( lower(getopt('&snapper_options', 'winclude=' )), '%');

        lstr             varchar2(1000);

   begin

        p_snapdate := systimestamp;

        select /* */ p_snapdate ts, snapper_stats.*
        bulk collect into p_stats
        from (
                                         select 'STAT' stype, s.inst_id, s.sid, ss.statistic# - pls_adjust statistic#, ss.value, null event_count
                                         from gv$session s, gv$sesstat ss
                                         where &sid_filter --(inst_id,sid) in (&snapper_sid)
                                         and s.inst_id = ss.inst_id
                                         and s.sid = ss.sid
                                         and  (lv_gather like '%s%' or lv_gather like '%a%')
                                         and ss.statistic# in (select /*+ no_unnest */ statistic# from v$statname
                                                            where lower(name) like '%'||lv_include_stat||'%'
                                                            or regexp_like (name, lv_include_stat, 'i')
                                                           )
                                         --
                                         union all
                                         select
                                                'WAIT', s.inst_id, s.sid,
                                                en.event# + (select count(*) from v$statname) + 1 - pls_adjust,
                                                nvl(se.time_waited_micro,0) + ( decode(se.event||s.state, s.event||'WAITING', s.seconds_in_wait, 0) * 1000000 ) value, total_waits event_count
                                         from gv$session s, gv$session_event se, v$event_name en
                                         where &sid_filter
                                         and   s.sid = se.sid
                                         and   s.inst_id = se.inst_id
                                         and   se.event = en.name
                                         --and   (se.inst_id, se.sid) in (&snapper_sid)
                                         and   (lv_gather like '%w%' or lv_gather like '%a%')
                                         and   en.event#  in (select event# from v$event_name
                                                            where lower(name) like '%'||lv_include_wait||'%'
                                                            or    regexp_like (name, lv_include_wait, 'i')
                                                           )
                                         --
                                         union all
                                         select 'TIME' stype, s.inst_id, s.sid, st.stat_id - pls_adjust statistic#, st.value, null event_count
                                         from gv$session s, gv$sess_time_model st
                                         where &sid_filter --(inst_id,sid) in (&snapper_sid)
                                         and s.inst_id = st.inst_id
                                         and s.sid = st.sid
                                         and   (lv_gather like '%t%' or lv_gather like '%a%')
                                         and st.stat_id in (select stat_id from gv$sys_time_model
                                                            where lower(stat_name) like '%'||lv_include_time||'%'
                                                            or    regexp_like (stat_name, lv_include_time, 'i')
                                                           )
                                         --
                                         union all
                                         select 'LATG', s.inst_id, -1 sid,
                                               s.latch# +
                                                   (select count(*) from v$statname) +
                                                   (select count(*) from v$event_name) +
                                                   1 - pls_adjust statistic#,
                                               s.gets + s.immediate_gets value, null event_count
                                         from gv$latch s
                                         where &inst_filter
                                         and (lv_gather like '%l%' or lv_gather like '%a%')
                                         and latch# in (select latch# from v$latchname
                                                        where lower(name) like '%'||lv_include_latch||'%'
                                                        or    regexp_like (name, lv_include_latch, 'i')
                                                       )
                                         --
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 union all
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 select 'BUFG', to_number(sys_context('userenv', 'instance')), -1 sid,
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11       s.indx +
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11           (select count(*) from v$statname) +
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11           (select count(*) from v$event_name) +
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11           (select count(*) from gv$latch) +
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11           1 - pls_adjust statistic#,
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11       s.why0+s.why1+s.why2 value, null event_count
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 from x$kcbsw s, x$kcbwh w
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 where
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11       s.indx = w.indx 
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 and   s.why0+s.why1+s.why2 > 0
 &_IF_X_ACCESSIBLE &_IF_LOWER_THAN_ORA11 and   (lv_gather like '%b%' or lv_gather like '%a%')
                                         --
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  union all
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  select 'BUFG', to_number(sys_context('userenv', 'instance')), -1 sid,
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        sw.indx +
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER            (select count(*) from v$statname) +
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER            (select count(*) from v$event_name) +
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER            (select count(*) from gv$latch) +
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER            1 - pls_adjust statistic#,
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        why.why0+why.why1+why.why2+sw.other_wait value, null event_count
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  from
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        x$kcbuwhy why,
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        x$kcbwh       dsc,
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        x$kcbsw       sw
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  where
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        why.indx = dsc.indx
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   why.inst_id = dsc.inst_id
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   dsc.inst_id = sw.inst_id
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   why.inst_id = sw.inst_id
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   why.why0 + why.why1 + why.why2 + sw.other_wait > 0
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   dsc.indx = sw.indx
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   why.indx = sw.indx
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER        -- deliberate cartesian join
 &_IF_X_ACCESSIBLE &_IF_ORA11_OR_HIGHER  and   (lv_gather like '%b%' or lv_gather like '%a%')
                                         --
                                         union all
                                         select 'ENQG', s.inst_id, -1 sid,
                                               ascii(substr(s.eq_type,1,1))*256 + ascii(substr(s.eq_type,2,1)) +
                                                   (select count(*) from v$statname) +
                                                   (select count(*) from v$event_name) +
                                                   (select count(*) from gv$latch) +
  &_IF_X_ACCESSIBLE                                (select count(*) from x$kcbwh) +
                                                   1 - pls_adjust statistic#,
                                               s.total_req# value, null event_count
                                         from gv$enqueue_stat s
                                         where &inst_filter
                                         and   (lv_gather like '%e%' or lv_gather like '%a%')
        ) snapper_stats
        order by inst_id, sid, stype, statistic#;

        if p_stats.COUNT > 0 then        
            -- l_stats is an associative array for stats lookup, used for the useful averages calculation
            -- p_stats_string is a dbms_debug_vc2coll collection datatype for "persisting" stats values across snapper DB calls (for "before" and "after" snaps)
            p_stats_string := sys.dbms_debug_vc2coll();
            for s in p_stats.first..p_stats.last loop
                -- type srec is record (stype varchar2(4), sid number, statistic# number, value number, event_count number );
                lstr := p_stats(s).stype||','||trim(to_char(p_stats(s).inst_id))||','||trim(to_char(p_stats(s).sid))||','||trim(to_char(p_stats(s).statistic#,'999999999999999999999999'));
                l_stats(lstr) := p_stats(s);

                if g_snap_begin is not null then
                    p_stats_string.extend();
                    p_stats_string(s) := TO_CHAR(p_stats(s).ts, 'YYYY-MM-DD HH24:MI:SS.FF')              ||','||
                                         p_stats(s).stype                                                ||','||
                                         TO_CHAR(p_stats(s).inst_id)                                     ||','||
                                         TO_CHAR(p_stats(s).sid)                                         ||','||
                                         TRIM(TO_CHAR(p_stats(s).statistic#, '999999999999999999999999'))||','||
                                         TRIM(TO_CHAR(p_stats(s).value,      '999999999999999999999999'))||','||
                                         TRIM(TO_CHAR(p_stats(s).event_count,'999999999999999999999999'));
                    --output('p_stats.p_stats_string='||p_stats_string(s));
                end if;
            end loop; -- s in (p_stats)
        end if;  -- p.stats.COUNT > 0
   end snap;


   /*---------------------------------------------------
    -- proc for reversing the string-normalized
    -- stats array into lookup tables/collections
    ---------------------------------------------------*/
   procedure snap_from_stats_string (p_string_stats in sys.dbms_debug_vc2coll, p_snapdate out timestamp, p_stats out stab, l_stats out ltab)
   is
     lstr varchar2(1000);
     lv_rec srec;
   begin
       p_snapdate := NULL;
       --type srec is record (stype varchar2(4), sid number, statistic# number, value number, event_count number );
       for s in p_string_stats.first .. p_string_stats.last loop
           lv_rec.ts          := TO_TIMESTAMP(replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 1),',',''), 'YYYY-MM-DD HH24:MI:SS.FF');
           lv_rec.stype       :=              replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 2),',','');
           lv_rec.inst_id     :=    TO_NUMBER(replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 3),',',''));
           lv_rec.sid         :=    TO_NUMBER(replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 4),',',''));
           lv_rec.statistic#  :=    TO_NUMBER(replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 5),',',''));
           lv_rec.value       :=    TO_NUMBER(replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 6),',',''));
           lv_rec.event_count :=    TO_NUMBER(replace(regexp_substr(p_string_stats(s)||',', '(.*?),', 1, 7),',',''));
           --output('snap_from_stats_string.event_count = '||to_char(lv_rec.event_count));

           p_stats(s) := lv_rec;
           lstr := p_stats(s).stype||','||trim(to_char(p_stats(s).inst_id))||','||trim(to_char(p_stats(s).sid))||','||trim(to_char(p_stats(s).statistic#,'999999999999999999999999'));
           l_stats(lstr) := p_stats(s);
       end loop;

       p_snapdate := lv_rec.ts;
   end snap_from_stats_string;

   /*---------------------------------------------------
    -- proc for dumping ASH data out in grouped
    -- and ordered fashion
    ---------------------------------------------------*/
    procedure out_ash( p_ash_columns in varchar2, p_topn in number := 10 ) as

        -- whether to print given column or not
        p_inst_id                     number := 0;
        p_sid                         number := 0;
        p_username                    number := 0;
        p_machine                     number := 0;
        p_terminal                    number := 0;
        p_program                     number := 0;
        p_event                       number := 0;
        p_wait_class                  number := 0;
        p_state                       number := 0;
        p_p1                          number := 0;
        p_p2                          number := 0;
        p_p3                          number := 0;
        p_row_wait_obj#               number := 0;
        p_row_wait_file#              number := 0;
        p_row_wait_block#             number := 0;
        p_row_wait_row#               number := 0;
        p_blocking_session_status     number := 0;
        p_blocking_instance           number := 0;
        p_blocking_session            number := 0;
        p_sql_hash_value              number := 0;
        p_sql_id                      number := 0;
        p_sql_child_number            number := 0;
        p_plsql_entry_object_id       number := 0;
        p_plsql_entry_subprogram_id   number := 0;
        p_plsql_object_id             number := 0;
        p_plsql_subprogram_id         number := 0;
        p_module                      number := 0;
        p_action                      number := 0;
        p_client_identifier           number := 0;
        p_service_name                number := 0;

        -- temporary variables for holding session details (for later formatting)
        o_inst_id                     varchar2(100);
        o_sid                         varchar2(100);
        o_username                    varchar2(100);
        o_machine                     varchar2(100);
        o_terminal                    varchar2(100);
        o_program                     varchar2(100);
        o_event                       varchar2(100);
        o_wait_class                  varchar2(100);
        o_state                       varchar2(100);
        o_p1                          varchar2(100);
        o_p2                          varchar2(100);
        o_p3                          varchar2(100);
        o_row_wait_obj#               varchar2(100);
        o_row_wait_file#              varchar2(100);
        o_row_wait_block#             varchar2(100);
        o_row_wait_row#               varchar2(100);
        o_blocking_session_status     varchar2(100);
        o_blocking_instance           varchar2(100);
        o_blocking_session            varchar2(100);
        o_sql_hash_value              varchar2(100);
        o_sql_id                      varchar2(100);
        o_sql_child_number            varchar2(100);
        o_plsql_entry_object_id       varchar2(100);
        o_plsql_entry_subprogram_id   varchar2(100);
        o_plsql_object_id             varchar2(100);
        o_plsql_subprogram_id         varchar2(100);
        o_module                      varchar2(100);
        o_action                      varchar2(100);
        o_client_identifier           varchar2(100);
        o_service_name                varchar2(100);

        -- helper local vars
        l_ash_grouping                number := 0;
        l_output_line                 varchar2(4000);
        l_ash_header_line             varchar2(4000);
  
    begin

      -- bail out if no ASH samples recorded
      if g_ash.count = 0 then 
          output('    <No active sessions captured during the sampling period>');
          return;
      end if;


      l_ash_header_line := 'Active%';

      -- ash,ash1,ash2,ash3 parameter column group tokenizer
      for s in (
          SELECT LEVEL
                 , SUBSTR
                   ( TOKEN
                    , DECODE(LEVEL, 1, 1, INSTR(TOKEN, DELIMITER, 1, LEVEL-1)+1)
                    , INSTR(TOKEN, DELIMITER, 1, LEVEL) -
                   DECODE(LEVEL, 1, 1, INSTR(TOKEN, DELIMITER, 1, LEVEL-1)+1)
                   ) TOKEN
          FROM ( SELECT REPLACE(  LOWER(p_ash_columns)  ,' ','')||'+' AS TOKEN
                 , '+'                        AS DELIMITER
                 FROM DUAL
          )
          CONNECT BY 
              INSTR(TOKEN, DELIMITER, 1, LEVEL)>0
          ORDER BY 
              LEVEL ASC
      ) loop

          case s.token
              -- actual column names in gv$session
              when 'inst_id'                      then l_ash_grouping := l_ash_grouping + c_inst_id                  ; l_ash_header_line := l_ash_header_line || ' | ' || lpad('INST_ID'                   , w_inst_id                  , ' ');
              when 'sid'                          then l_ash_grouping := l_ash_grouping + c_sid                      ; l_ash_header_line := l_ash_header_line || ' | ' || lpad('SID'                       , w_sid                      , ' ');
              when 'username'                     then l_ash_grouping := l_ash_grouping + c_username                 ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('USERNAME'                  , w_username                 , ' ');
              when 'machine'                      then l_ash_grouping := l_ash_grouping + c_machine                  ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('MACHINE'                   , w_machine                  , ' ');
              when 'terminal'                     then l_ash_grouping := l_ash_grouping + c_terminal                 ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('TERMINAL'                  , w_terminal                 , ' ');
              when 'program'                      then l_ash_grouping := l_ash_grouping + c_program                  ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PROGRAM'                   , w_program                  , ' ');
              when 'event'                        then l_ash_grouping := l_ash_grouping + c_event                    ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('EVENT'                     , w_event                    , ' ');
              when 'wait_class'                   then l_ash_grouping := l_ash_grouping + c_wait_class               ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('WAIT_CLASS'                , w_wait_class               , ' ');
              when 'state'                        then l_ash_grouping := l_ash_grouping + c_state                    ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('STATE'                     , w_state                    , ' ');
              when 'p1'                           then l_ash_grouping := l_ash_grouping + c_p1                       ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('P1'                        , w_p1                       , ' ');
              when 'p2'                           then l_ash_grouping := l_ash_grouping + c_p2                       ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('P2'                        , w_p2                       , ' ');
              when 'p3'                           then l_ash_grouping := l_ash_grouping + c_p3                       ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('P3'                        , w_p3                       , ' ');
              when 'row_wait_obj#'                then l_ash_grouping := l_ash_grouping + c_row_wait_obj#            ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_OBJ#'             , w_row_wait_obj#            , ' ');
              when 'row_wait_file#'               then l_ash_grouping := l_ash_grouping + c_row_wait_file#           ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_FILE#'            , w_row_wait_file#           , ' ');
              when 'row_wait_block#'              then l_ash_grouping := l_ash_grouping + c_row_wait_block#          ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_BLOCK#'           , w_row_wait_block#          , ' ');
              when 'row_wait_row#'                then l_ash_grouping := l_ash_grouping + c_row_wait_row#            ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_ROW#'             , w_row_wait_row#            , ' ');
              when 'blocking_session_status'      then l_ash_grouping := l_ash_grouping + c_blocking_session_status  ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('BLOCKING_SESSION_STATUS'   , w_blocking_session_status  , ' ');
              when 'blocking_instance'            then l_ash_grouping := l_ash_grouping + c_blocking_instance        ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('BLOCKING_INSTANCE'         , w_blocking_instance        , ' ');
              when 'blocking_session'             then l_ash_grouping := l_ash_grouping + c_blocking_session         ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('BLOCKING_SESSION'          , w_blocking_session         , ' ');
              when 'sql_hash_value'               then l_ash_grouping := l_ash_grouping + c_sql_hash_value           ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SQL_HASH_VALUE'            , w_sql_hash_value           , ' ');
              when 'sql_id'                       then l_ash_grouping := l_ash_grouping + c_sql_id                   ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SQL_ID'                    , w_sql_id                   , ' ');
              when 'sql_child_number'             then l_ash_grouping := l_ash_grouping + c_sql_child_number         ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SQL_CHILD_NUMBER'          , w_sql_child_number         , ' ');
              when 'plsql_entry_object_id'        then l_ash_grouping := l_ash_grouping + c_plsql_entry_object_id    ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_ENTRY_OBJECT_ID'     , w_plsql_entry_object_id    , ' ');
              when 'plsql_entry_subprogram_id'    then l_ash_grouping := l_ash_grouping + c_plsql_entry_subprogram_id; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_ENTRY_SUBPROGRAM_ID' , w_plsql_entry_subprogram_id, ' ');
              when 'plsql_object_id'              then l_ash_grouping := l_ash_grouping + c_plsql_object_id          ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_OBJECT_ID'           , w_plsql_object_id          , ' ');
              when 'plsql_subprogram_id'          then l_ash_grouping := l_ash_grouping + c_plsql_subprogram_id      ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_SUBPROGRAM_ID'       , w_plsql_subprogram_id      , ' ');
              when 'module'                       then l_ash_grouping := l_ash_grouping + c_module                   ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('MODULE'                    , w_module                   , ' ');
              when 'action'                       then l_ash_grouping := l_ash_grouping + c_action                   ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ACTION'                    , w_action                   , ' ');
              when 'client_identifier'            then l_ash_grouping := l_ash_grouping + c_client_identifier        ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('CLIENT_IDENTIFIER'         , w_client_identifier        , ' ');
              when 'service_name'                 then l_ash_grouping := l_ash_grouping + c_service_name             ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SERVICE_NAME'              , w_service_name             , ' ');
              -- aliases for convenience (only either real name or alias should be used together at the same time)
              when 'user'                         then l_ash_grouping := l_ash_grouping + c_username                 ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('USERNAME'                  , w_username                 , ' ');
              when 'obj'                          then l_ash_grouping := l_ash_grouping + c_row_wait_obj#            ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_OBJ#'             , w_row_wait_obj#            , ' ');
              when 'file'                         then l_ash_grouping := l_ash_grouping + c_row_wait_file#           ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_FILE#'            , w_row_wait_file#           , ' ');
              when 'block'                        then l_ash_grouping := l_ash_grouping + c_row_wait_block#          ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_BLOCK#'           , w_row_wait_block#          , ' ');
              when 'row'                          then l_ash_grouping := l_ash_grouping + c_row_wait_row#            ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ROW_WAIT_ROW#'             , w_row_wait_row#            , ' ');
              when 'bss'                          then l_ash_grouping := l_ash_grouping + c_blocking_session_status  ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('BLOCKING_SESSION_STATUS'   , w_blocking_session_status  , ' ');
              when 'bsi'                          then l_ash_grouping := l_ash_grouping + c_blocking_instance        ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('BLOCKING_INSTANCE'         , w_blocking_instance        , ' ');
              when 'bs'                           then l_ash_grouping := l_ash_grouping + c_blocking_session         ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('BLOCKING_SESSION'          , w_blocking_session         , ' ');
              when 'sql'                          then l_ash_grouping := l_ash_grouping + c_sql_hash_value           ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SQL_HASH_VALUE'            , w_sql_hash_value           , ' ');
              when 'sqlid'                        then l_ash_grouping := l_ash_grouping + c_sql_id                   ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SQL_ID'                    , w_sql_id                   , ' ');
              when 'child'                        then l_ash_grouping := l_ash_grouping + c_sql_child_number         ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SQL_CHILD_NUMBER'          , w_sql_child_number         , ' ');
              when 'plsql_eoid'                   then l_ash_grouping := l_ash_grouping + c_plsql_entry_object_id    ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_ENTRY_OBJECT_ID'     , w_plsql_entry_object_id    , ' ');
              when 'plsql_esubpid'                then l_ash_grouping := l_ash_grouping + c_plsql_entry_subprogram_id; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_ENTRY_SUBPROGRAM_ID' , w_plsql_entry_subprogram_id, ' ');
              when 'plsql_oid'                    then l_ash_grouping := l_ash_grouping + c_plsql_object_id          ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_OBJECT_ID'           , w_plsql_object_id          , ' ');
              when 'plsql_subpid'                 then l_ash_grouping := l_ash_grouping + c_plsql_subprogram_id      ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('PLSQL_SUBPROGRAM_ID'       , w_plsql_subprogram_id      , ' ');
              when 'mod'                          then l_ash_grouping := l_ash_grouping + c_module                   ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('MODULE'                    , w_module                   , ' ');
              when 'act'                          then l_ash_grouping := l_ash_grouping + c_action                   ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('ACTION'                    , w_action                   , ' ');
              when 'cid'                          then l_ash_grouping := l_ash_grouping + c_client_identifier        ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('CLIENT_IDENTIFIER'         , w_client_identifier        , ' ');
              when 'service'                      then l_ash_grouping := l_ash_grouping + c_service_name             ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('SERVICE_NAME'              , w_service_name             , ' ');
              when 'wait_event'                   then l_ash_grouping := l_ash_grouping + c_event                    ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('EVENT'                     , w_event                    , ' ');
              when 'wait_state'                   then l_ash_grouping := l_ash_grouping + c_state                    ; l_ash_header_line := l_ash_header_line || ' | ' || rpad('STATE'                     , w_state                    , ' ');
          else
              null;
              -- raise_application_error(-20000, 'Invalid ASH column name');
          end case; -- case s.token

      end loop; -- tokenizer

      output(' ');
      output(lpad('-',length(l_ash_header_line),'-'));
      output(l_ash_header_line);
      output(lpad('-',length(l_ash_header_line),'-'));

      -- this is needed for "easy" sorting and group by ops (without any custom stored object types!)
      for i in (
          with raw_records as (
             select column_value rec from table(cast(g_ash as sys.dbms_debug_vc2coll))
          ),
          ash_records as (
             select 
                 substr(r.rec, instr(r.rec, '<', 1,  1)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  1)+1), '>')-1) inst_id
               , substr(r.rec, instr(r.rec, '<', 1,  2)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  2)+1), '>')-1) sid
               , substr(r.rec, instr(r.rec, '<', 1,  3)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  3)+1), '>')-1) username
               , substr(r.rec, instr(r.rec, '<', 1,  4)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  4)+1), '>')-1) machine
               , substr(r.rec, instr(r.rec, '<', 1,  5)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  5)+1), '>')-1) terminal
               , substr(r.rec, instr(r.rec, '<', 1,  6)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  6)+1), '>')-1) program
               , substr(r.rec, instr(r.rec, '<', 1,  7)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  7)+1), '>')-1) event
               , substr(r.rec, instr(r.rec, '<', 1,  8)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  8)+1), '>')-1) wait_class
               , substr(r.rec, instr(r.rec, '<', 1,  9)+1, instr (substr(r.rec, instr(r.rec, '<', 1,  9)+1), '>')-1) state
               , substr(r.rec, instr(r.rec, '<', 1, 10)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 10)+1), '>')-1) p1
               , substr(r.rec, instr(r.rec, '<', 1, 11)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 11)+1), '>')-1) p2
               , substr(r.rec, instr(r.rec, '<', 1, 12)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 12)+1), '>')-1) p3
               , substr(r.rec, instr(r.rec, '<', 1, 13)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 13)+1), '>')-1) row_wait_obj#
               , substr(r.rec, instr(r.rec, '<', 1, 14)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 14)+1), '>')-1) row_wait_file#
               , substr(r.rec, instr(r.rec, '<', 1, 15)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 15)+1), '>')-1) row_wait_block#
               , substr(r.rec, instr(r.rec, '<', 1, 16)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 16)+1), '>')-1) row_wait_row#
               , substr(r.rec, instr(r.rec, '<', 1, 17)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 17)+1), '>')-1) blocking_session_status
               , substr(r.rec, instr(r.rec, '<', 1, 18)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 18)+1), '>')-1) blocking_instance
               , substr(r.rec, instr(r.rec, '<', 1, 19)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 19)+1), '>')-1) blocking_session
               , substr(r.rec, instr(r.rec, '<', 1, 20)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 20)+1), '>')-1) sql_hash_value
               , substr(r.rec, instr(r.rec, '<', 1, 21)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 21)+1), '>')-1) sql_id
               , substr(r.rec, instr(r.rec, '<', 1, 22)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 22)+1), '>')-1) sql_child_number
               , substr(r.rec, instr(r.rec, '<', 1, 23)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 23)+1), '>')-1) plsql_entry_object_id
               , substr(r.rec, instr(r.rec, '<', 1, 24)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 24)+1), '>')-1) plsql_entry_subprogram_id
               , substr(r.rec, instr(r.rec, '<', 1, 25)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 25)+1), '>')-1) plsql_object_id
               , substr(r.rec, instr(r.rec, '<', 1, 26)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 26)+1), '>')-1) plsql_subprogram_id
               , substr(r.rec, instr(r.rec, '<', 1, 27)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 27)+1), '>')-1) module
               , substr(r.rec, instr(r.rec, '<', 1, 28)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 28)+1), '>')-1) action
               , substr(r.rec, instr(r.rec, '<', 1, 29)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 29)+1), '>')-1) client_identifier
               , substr(r.rec, instr(r.rec, '<', 1, 30)+1, instr (substr(r.rec, instr(r.rec, '<', 1, 30)+1), '>')-1) service_name              
             from 
                raw_records r
          )
          select * from (
            select
               decode(bitand(l_ash_grouping, power(2, s_inst_id                       )), 0, chr(0), inst_id                       ) as inst_id
             , decode(bitand(l_ash_grouping, power(2, s_sid                           )), 0, chr(0), sid                           ) as sid                           
             , decode(bitand(l_ash_grouping, power(2, s_username                      )), 0, chr(0), username                      ) as username                      
             , decode(bitand(l_ash_grouping, power(2, s_machine                       )), 0, chr(0), machine                       ) as machine                       
             , decode(bitand(l_ash_grouping, power(2, s_terminal                      )), 0, chr(0), terminal                      ) as terminal                      
             , decode(bitand(l_ash_grouping, power(2, s_program                       )), 0, chr(0), program                       ) as program                       
             , decode(bitand(l_ash_grouping, power(2, s_event                         )), 0, chr(0), event                         ) as event                         
             , decode(bitand(l_ash_grouping, power(2, s_wait_class                    )), 0, chr(0), wait_class                    ) as wait_class                    
             , decode(bitand(l_ash_grouping, power(2, s_state                         )), 0, chr(0), state                         ) as state                         
             , decode(bitand(l_ash_grouping, power(2, s_p1                            )), 0, chr(0), p1                            ) as p1                            
             , decode(bitand(l_ash_grouping, power(2, s_p2                            )), 0, chr(0), p2                            ) as p2                            
             , decode(bitand(l_ash_grouping, power(2, s_p3                            )), 0, chr(0), p3                            ) as p3                            
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_obj#                 )), 0, chr(0), row_wait_obj#                 ) as row_wait_obj#                 
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_file#                )), 0, chr(0), row_wait_file#                ) as row_wait_file#                
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_block#               )), 0, chr(0), row_wait_block#               ) as row_wait_block#               
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_row#                 )), 0, chr(0), row_wait_row#                 ) as row_wait_row#                 
             , decode(bitand(l_ash_grouping, power(2, s_blocking_session_status       )), 0, chr(0), blocking_session_status       ) as blocking_session_status       
             , decode(bitand(l_ash_grouping, power(2, s_blocking_instance             )), 0, chr(0), blocking_instance             ) as blocking_instance             
             , decode(bitand(l_ash_grouping, power(2, s_blocking_session              )), 0, chr(0), blocking_session              ) as blocking_session              
             , decode(bitand(l_ash_grouping, power(2, s_sql_hash_value                )), 0, chr(0), sql_hash_value                ) as sql_hash_value                
             , decode(bitand(l_ash_grouping, power(2, s_sql_id                        )), 0, chr(0), sql_id                        ) as sql_id                        
             , decode(bitand(l_ash_grouping, power(2, s_sql_child_number              )), 0, chr(0), sql_child_number              ) as sql_child_number              
             , decode(bitand(l_ash_grouping, power(2, s_plsql_entry_object_id         )), 0, chr(0), plsql_entry_object_id         ) as plsql_entry_object_id         
             , decode(bitand(l_ash_grouping, power(2, s_plsql_entry_subprogram_id     )), 0, chr(0), plsql_entry_subprogram_id     ) as plsql_entry_subprogram_id     
             , decode(bitand(l_ash_grouping, power(2, s_plsql_object_id               )), 0, chr(0), plsql_object_id               ) as plsql_object_id               
             , decode(bitand(l_ash_grouping, power(2, s_plsql_subprogram_id           )), 0, chr(0), plsql_subprogram_id           ) as plsql_subprogram_id           
             , decode(bitand(l_ash_grouping, power(2, s_module                        )), 0, chr(0), module                        ) as module                        
             , decode(bitand(l_ash_grouping, power(2, s_action                        )), 0, chr(0), action                        ) as action                        
             , decode(bitand(l_ash_grouping, power(2, s_client_identifier             )), 0, chr(0), client_identifier             ) as client_identifier             
             , decode(bitand(l_ash_grouping, power(2, s_service_name                  )), 0, chr(0), service_name                  ) as service_name                  
             , count(*)/g_ash_samples_taken average_active_samples
            from
               ash_records a
            group by
               decode(bitand(l_ash_grouping, power(2, s_inst_id                       )), 0, chr(0), inst_id                       ) -- inst_id                       
             , decode(bitand(l_ash_grouping, power(2, s_sid                           )), 0, chr(0), sid                           ) -- sid                           
             , decode(bitand(l_ash_grouping, power(2, s_username                      )), 0, chr(0), username                      ) -- username                      
             , decode(bitand(l_ash_grouping, power(2, s_machine                       )), 0, chr(0), machine                       ) -- machine                       
             , decode(bitand(l_ash_grouping, power(2, s_terminal                      )), 0, chr(0), terminal                      ) -- terminal                      
             , decode(bitand(l_ash_grouping, power(2, s_program                       )), 0, chr(0), program                       ) -- program                       
             , decode(bitand(l_ash_grouping, power(2, s_event                         )), 0, chr(0), event                         ) -- event                         
             , decode(bitand(l_ash_grouping, power(2, s_wait_class                    )), 0, chr(0), wait_class                    ) -- wait_class                    
             , decode(bitand(l_ash_grouping, power(2, s_state                         )), 0, chr(0), state                         ) -- state                         
             , decode(bitand(l_ash_grouping, power(2, s_p1                            )), 0, chr(0), p1                            ) -- p1                            
             , decode(bitand(l_ash_grouping, power(2, s_p2                            )), 0, chr(0), p2                            ) -- p2                            
             , decode(bitand(l_ash_grouping, power(2, s_p3                            )), 0, chr(0), p3                            ) -- p3                            
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_obj#                 )), 0, chr(0), row_wait_obj#                 ) -- row_wait_obj#                 
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_file#                )), 0, chr(0), row_wait_file#                ) -- row_wait_file#                
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_block#               )), 0, chr(0), row_wait_block#               ) -- row_wait_block#               
             , decode(bitand(l_ash_grouping, power(2, s_row_wait_row#                 )), 0, chr(0), row_wait_row#                 ) -- row_wait_row#                 
             , decode(bitand(l_ash_grouping, power(2, s_blocking_session_status       )), 0, chr(0), blocking_session_status       ) -- blocking_session_status       
             , decode(bitand(l_ash_grouping, power(2, s_blocking_instance             )), 0, chr(0), blocking_instance             ) -- blocking_instance             
             , decode(bitand(l_ash_grouping, power(2, s_blocking_session              )), 0, chr(0), blocking_session              ) -- blocking_session              
             , decode(bitand(l_ash_grouping, power(2, s_sql_hash_value                )), 0, chr(0), sql_hash_value                ) -- sql_hash_value                
             , decode(bitand(l_ash_grouping, power(2, s_sql_id                        )), 0, chr(0), sql_id                        ) -- sql_id                        
             , decode(bitand(l_ash_grouping, power(2, s_sql_child_number              )), 0, chr(0), sql_child_number              ) -- sql_child_number              
             , decode(bitand(l_ash_grouping, power(2, s_plsql_entry_object_id         )), 0, chr(0), plsql_entry_object_id         ) -- plsql_entry_object_id         
             , decode(bitand(l_ash_grouping, power(2, s_plsql_entry_subprogram_id     )), 0, chr(0), plsql_entry_subprogram_id     ) -- plsql_entry_subprogram_id     
             , decode(bitand(l_ash_grouping, power(2, s_plsql_object_id               )), 0, chr(0), plsql_object_id               ) -- plsql_object_id               
             , decode(bitand(l_ash_grouping, power(2, s_plsql_subprogram_id           )), 0, chr(0), plsql_subprogram_id           ) -- plsql_subprogram_id           
             , decode(bitand(l_ash_grouping, power(2, s_module                        )), 0, chr(0), module                        ) -- module                        
             , decode(bitand(l_ash_grouping, power(2, s_action                        )), 0, chr(0), action                        ) -- action                        
             , decode(bitand(l_ash_grouping, power(2, s_client_identifier             )), 0, chr(0), client_identifier             ) -- client_identifier             
             , decode(bitand(l_ash_grouping, power(2, s_service_name                  )), 0, chr(0), service_name                  ) -- service_name                  
           order by
              count(*)/g_ash_samples_taken desc
          )
          where rownum <= p_topn
      ) loop

          l_output_line := '';

          o_inst_id                     := CASE WHEN i.inst_id                        = chr(0) THEN null ELSE nvl(i.inst_id                       , ' ') END;
          o_sid                         := CASE WHEN i.sid                            = chr(0) THEN null ELSE nvl(i.sid                           , ' ') END;
          o_username                    := CASE WHEN i.username                       = chr(0) THEN null ELSE nvl(i.username                      , ' ') END;
          o_machine                     := CASE WHEN i.machine                        = chr(0) THEN null ELSE nvl(i.machine                       , ' ') END;
          o_terminal                    := CASE WHEN i.terminal                       = chr(0) THEN null ELSE nvl(i.terminal                      , ' ') END;
          o_program                     := CASE WHEN i.program                        = chr(0) THEN null ELSE nvl(i.program                       , ' ') END;
          o_event                       := CASE WHEN i.event                          = chr(0) THEN null ELSE nvl(i.event                         , ' ') END;
          o_wait_class                  := CASE WHEN i.wait_class                     = chr(0) THEN null ELSE nvl(i.wait_class                    , ' ') END;
          o_state                       := CASE WHEN i.state                          = chr(0) THEN null ELSE nvl(i.state                         , ' ') END;
          o_p1                          := CASE WHEN i.p1                             = chr(0) THEN null ELSE nvl(i.p1                            , ' ') END;
          o_p2                          := CASE WHEN i.p2                             = chr(0) THEN null ELSE nvl(i.p2                            , ' ') END;
          o_p3                          := CASE WHEN i.p3                             = chr(0) THEN null ELSE nvl(i.p3                            , ' ') END;
          o_row_wait_obj#               := CASE WHEN i.row_wait_obj#                  = chr(0) THEN null ELSE nvl(i.row_wait_obj#                 , ' ') END;
          o_row_wait_file#              := CASE WHEN i.row_wait_file#                 = chr(0) THEN null ELSE nvl(i.row_wait_file#                , ' ') END;
          o_row_wait_block#             := CASE WHEN i.row_wait_block#                = chr(0) THEN null ELSE nvl(i.row_wait_block#               , ' ') END;
          o_row_wait_row#               := CASE WHEN i.row_wait_row#                  = chr(0) THEN null ELSE nvl(i.row_wait_row#                 , ' ') END;
          o_blocking_session_status     := CASE WHEN i.blocking_session_status        = chr(0) THEN null ELSE nvl(i.blocking_session_status       , ' ') END;
          o_blocking_instance           := CASE WHEN i.blocking_instance              = chr(0) THEN null ELSE nvl(i.blocking_instance             , ' ') END;
          o_blocking_session            := CASE WHEN i.blocking_session               = chr(0) THEN null ELSE nvl(i.blocking_session              , ' ') END;
          o_sql_hash_value              := CASE WHEN i.sql_hash_value                 = chr(0) THEN null ELSE nvl(i.sql_hash_value                , ' ') END;
          o_sql_id                      := CASE WHEN i.sql_id                         = chr(0) THEN null ELSE nvl(i.sql_id                        , ' ') END;
          o_sql_child_number            := CASE WHEN i.sql_child_number               = chr(0) THEN null ELSE nvl(i.sql_child_number              , ' ') END;
          o_plsql_entry_object_id       := CASE WHEN i.plsql_entry_object_id          = chr(0) THEN null ELSE nvl(i.plsql_entry_object_id         , ' ') END;
          o_plsql_entry_subprogram_id   := CASE WHEN i.plsql_entry_subprogram_id      = chr(0) THEN null ELSE nvl(i.plsql_entry_subprogram_id     , ' ') END;
          o_plsql_object_id             := CASE WHEN i.plsql_object_id                = chr(0) THEN null ELSE nvl(i.plsql_object_id               , ' ') END;
          o_plsql_subprogram_id         := CASE WHEN i.plsql_subprogram_id            = chr(0) THEN null ELSE nvl(i.plsql_subprogram_id           , ' ') END;
          o_module                      := CASE WHEN i.module                         = chr(0) THEN null ELSE nvl(i.module                        , ' ') END;
          o_action                      := CASE WHEN i.action                         = chr(0) THEN null ELSE nvl(i.action                        , ' ') END;
          o_client_identifier           := CASE WHEN i.client_identifier              = chr(0) THEN null ELSE nvl(i.client_identifier             , ' ') END;
          o_service_name                := CASE WHEN i.service_name                   = chr(0) THEN null ELSE nvl(i.service_name                  , ' ') END;


          -- print the activity % as the first column
          l_output_line := lpad(to_char(round(i.average_active_samples*100))||'%', w_activity_pct, ' ');

          -- loop through ash columns to find what to print and in which order
          for s in (
              SELECT LEVEL
                     , SUBSTR
                       ( TOKEN
                        , DECODE(LEVEL, 1, 1, INSTR(TOKEN, DELIMITER, 1, LEVEL-1)+1)
                        , INSTR(TOKEN, DELIMITER, 1, LEVEL) -
                       DECODE(LEVEL, 1, 1, INSTR(TOKEN, DELIMITER, 1, LEVEL-1)+1)
                       ) TOKEN
              FROM ( SELECT REPLACE(  LOWER(p_ash_columns)  ,' ','')||'+' AS TOKEN
                     , '+'                        AS DELIMITER
                     FROM DUAL
              )
              CONNECT BY 
                  INSTR(TOKEN, DELIMITER, 1, LEVEL)>0 
              ORDER BY 
                  LEVEL ASC
          ) loop
              l_output_line := l_output_line || ' | ' ||
                  case s.token
                      -- actual column names in gv$session
                      when 'inst_id'                      then lpad(o_inst_id                   , w_inst_id                  , ' ')
                      when 'sid'                          then lpad(o_sid                       , w_sid                      , ' ')
                      when 'username'                     then rpad(o_username                  , w_username                 , ' ')
                      when 'machine'                      then rpad(o_machine                   , w_machine                  , ' ')
                      when 'terminal'                     then rpad(o_terminal                  , w_terminal                 , ' ')
                      when 'program'                      then rpad(o_program                   , w_program                  , ' ')
                      when 'event'                        then rpad(o_event                     , w_event                    , ' ')
                      when 'wait_class'                   then rpad(o_wait_class                , w_wait_class               , ' ')
                      when 'state'                        then rpad(o_state                     , w_state                    , ' ')
                      when 'p1'                           then rpad(o_p1                        , w_p1                       , ' ')
                      when 'p2'                           then rpad(o_p2                        , w_p2                       , ' ')
                      when 'p3'                           then rpad(o_p3                        , w_p3                       , ' ')
                      when 'row_wait_obj#'                then rpad(o_row_wait_obj#             , w_row_wait_obj#            , ' ')
                      when 'row_wait_file#'               then rpad(o_row_wait_file#            , w_row_wait_file#           , ' ')
                      when 'row_wait_block#'              then rpad(o_row_wait_block#           , w_row_wait_block#          , ' ')
                      when 'row_wait_row#'                then rpad(o_row_wait_row#             , w_row_wait_row#            , ' ')
                      when 'blocking_session_status'      then rpad(o_blocking_session_status   , w_blocking_session_status  , ' ')
                      when 'blocking_instance'            then rpad(o_blocking_instance         , w_blocking_instance        , ' ')
                      when 'blocking_session'             then rpad(o_blocking_session          , w_blocking_session         , ' ')
                      when 'sql_hash_value'               then rpad(o_sql_hash_value            , w_sql_hash_value           , ' ')
                      when 'sql_id'                       then rpad(o_sql_id                    , w_sql_id                   , ' ')
                      when 'sql_child_number'             then rpad(o_sql_child_number          , w_sql_child_number         , ' ')
                      when 'plsql_entry_object_id'        then rpad(o_plsql_entry_object_id     , w_plsql_entry_object_id    , ' ')
                      when 'plsql_entry_subprogram_id'    then rpad(o_plsql_entry_subprogram_id , w_plsql_entry_subprogram_id, ' ')
                      when 'plsql_object_id'              then rpad(o_plsql_object_id           , w_plsql_object_id          , ' ')
                      when 'plsql_subprogram_id'          then rpad(o_plsql_subprogram_id       , w_plsql_subprogram_id      , ' ')
                      when 'module'                       then rpad(o_module                    , w_module                   , ' ')
                      when 'action'                       then rpad(o_action                    , w_action                   , ' ')
                      when 'client_identifier'            then rpad(o_client_identifier         , w_client_identifier        , ' ')
                      when 'service_name'                 then rpad(o_service_name              , w_service_name             , ' ')
                      -- aliases for convenience (only either real name or alias should be used together at the same time) 
                      when 'user'                         then rpad(o_username                  , w_username                 , ' ')
                      when 'obj'                          then rpad(o_row_wait_obj#             , w_row_wait_obj#            , ' ')
                      when 'file'                         then rpad(o_row_wait_file#            , w_row_wait_file#           , ' ')
                      when 'block'                        then rpad(o_row_wait_block#           , w_row_wait_block#          , ' ')
                      when 'row'                          then rpad(o_row_wait_row#             , w_row_wait_row#            , ' ')
                      when 'bss'                          then rpad(o_blocking_session_status   , w_blocking_session_status  , ' ')
                      when 'bsi'                          then rpad(o_blocking_instance         , w_blocking_instance        , ' ')
                      when 'bs'                           then rpad(o_blocking_session          , w_blocking_session         , ' ')
                      when 'sql'                          then rpad(o_sql_hash_value            , w_sql_hash_value           , ' ')
                      when 'sqlid'                        then rpad(o_sql_id                    , w_sql_id                   , ' ')
                      when 'child'                        then rpad(o_sql_child_number          , w_sql_child_number         , ' ')
                      when 'plsql_eoid'                   then rpad(o_plsql_entry_object_id     , w_plsql_entry_object_id    , ' ')
                      when 'plsql_esubpid'                then rpad(o_plsql_entry_subprogram_id , w_plsql_entry_subprogram_id, ' ')
                      when 'plsql_oid'                    then rpad(o_plsql_object_id           , w_plsql_object_id          , ' ')
                      when 'plsql_subpid'                 then rpad(o_plsql_subprogram_id       , w_plsql_subprogram_id      , ' ')
                      when 'mod'                          then rpad(o_module                    , w_module                   , ' ')
                      when 'act'                          then rpad(o_action                    , w_action                   , ' ')
                      when 'cid'                          then rpad(o_client_identifier         , w_client_identifier        , ' ')
                      when 'service'                      then rpad(o_service_name              , w_service_name             , ' ')
                      when 'wait_event'                   then rpad(o_event                     , w_event                    , ' ')
                      when 'wait_state'                   then rpad(o_state                     , w_state                    , ' ')
                  else
                      ''
                  end; -- case s.token

          end loop; -- ash parameter tokenizer

          output(l_output_line);

      end loop; -- grouped ash samples

  end out_ash;


-- and it begins!!!
begin

    -- get snappers own sid into g_mysid
    select sid into g_mysid from v$mystat where rownum = 1;

    pagesize := nvl( getopt('&snapper_options', 'pagesize=' ), pagesize);
    --output ( 'Pagesize='||pagesize );

    lv_ash   := getopt('&snapper_options', 'ash');
    lv_stats := getopt('&snapper_options', 'stat');

    if lv_ash is not null then gather_ash := 1; end if;
    if lv_stats is not null then gather_stats := 1; end if;

    --output('all='||case when getopt('&snapper_options', 'all') = chr(0) then 'chr(0)' when getopt('&snapper_options', 'all') is null then 'null' else (getopt('&snapper_options','all')) end);
    -- some additional default value logic
    if getopt('&snapper_options', 'all') is not null then
        --output('setting stats to all due to option = all');
        gather_stats := 1;
        gather_ash   := 1;
    else 
        if (lv_ash is null and lv_stats is null) then
            gather_stats := 0;  
            gather_ash   := 1;
        end if;
    end if;

    -- determine which performance counters and stats 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;

    --lv_gather:=getopt ('&snapper_options', 'gather=');
    --output('lv_gather='||lv_gather);

    g_snap_begin := lower(getopt('&snapper_options', 'begin' ));
    g_snap_end   := lower(getopt('&snapper_options', 'end' ));
    --output('g_snap_begin = '||g_snap_begin);    
    --output('g_snap_end = '||g_snap_end);    
 
    if pagesize > 0 then
        output(' ');
        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%' or lv_gather like '%a%')
                                 --
                                 union all
                                 select 'WAIT',
                                        event# + (select count(*) from v$statname) + 1 - pls_adjust, name
                                 from v$event_name
                                 where (lv_gather like '%w%' or lv_gather like '%a%')
                                 --
                                 union all
                                 select 'TIME' stype, stat_id - pls_adjust statistic#, stat_name name
                                 from gv$sys_time_model
                                 where (lv_gather like '%t%' or lv_gather like '%a%')
                                 --
                                 union all
                                 select 'LATG',
                                       l.latch# +
                                           (select count(*) from v$statname) +
                                           (select count(*) from v$event_name) +
                                           1 - pls_adjust statistic#,
                                       name
                                 from gv$latch l
                                 where (lv_gather like '%l%' or lv_gather like '%a%')
                                 --
            &_IF_X_ACCESSIBLE    union all
            &_IF_X_ACCESSIBLE    select 'BUFG',
            &_IF_X_ACCESSIBLE          indx +
            &_IF_X_ACCESSIBLE              (select count(*) from v$statname) +
            &_IF_X_ACCESSIBLE              (select count(*) from v$event_name) +
            &_IF_X_ACCESSIBLE              (select count(*) from gv$latch) +
            &_IF_X_ACCESSIBLE              1 - pls_adjust statistic#,
            &_IF_X_ACCESSIBLE         kcbwhdes name
            &_IF_X_ACCESSIBLE    from x$kcbwh
            &_IF_X_ACCESSIBLE    where   (lv_gather like '%b%' or lv_gather like '%a%')
                                 --
                                 union all
                                 select 'ENQG',
                                       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 gv$latch) +
            &_IF_X_ACCESSIBLE              (select count(*) from x$kcbwh) +
                                           1 - pls_adjust statistic#,
                                           eq_type
                                 from (
                                       select es.eq_type
                                              ||' - '||lt.name
                                              eq_type,
                                              total_req#
                                       from
                                              gv$enqueue_stat es
                                            , gv$lock_type lt
                                       where es.eq_type = lt.type
                                 ) e
                                 where (lv_gather like '%e%' or lv_gather like '%a%')
    ) snapper_statnames
    order by stype, statistic#;

    -- store these into an index_by array organized by statistic# for fast lookup
    for i in 1..sn_tmp.count loop
        sn(sn_tmp(i).statistic#) := sn_tmp(i);
        sn_reverse(sn_tmp(i).stype||','||sn_tmp(i).name) := sn_tmp(i);
    end loop;


    -- main sampling loop
    for c in 1..&snapper_count loop

        -- sesstat and other performance counter sampling
        if gather_stats = 1 then

            -- print header if required
            gv_header_string :=
                      CASE WHEN output_header      = 1 THEN 'HEAD,'                        END
                   || CASE WHEN output_inst        = 1 THEN ' INST,'                       END
                   || CASE WHEN output_sid         = 1 THEN '    SID,'                     END
                   || CASE WHEN output_inst_sid    = 1 THEN ' SID @INST,'                  END
                   || CASE WHEN output_username    = 1 THEN ' USERNAME  ,'                 END
                   || CASE WHEN output_time        = 1 THEN ' SNAPSHOT START   ,'          END
                   || CASE WHEN output_seconds     = 1 THEN '  SECONDS,'                   END
                   || CASE WHEN output_stype       = 1 THEN ' TYPE,'                       END
                   || CASE WHEN output_sname       = 1 THEN rpad(' STATISTIC',59,' ')||',' END
                   || CASE WHEN output_delta       = 1 THEN '         DELTA,'              END
                   || CASE WHEN output_delta_s     = 1 THEN '  DELTA/SEC,'                 END
                   || CASE WHEN output_hdelta      = 1 THEN '     HDELTA,'                 END
                   || CASE WHEN output_hdelta_s    = 1 THEN ' HDELTA/SEC,'                 END
                   || CASE WHEN output_percent     = 1 THEN '    %TIME,'                   END
                   || CASE WHEN output_pcthist     = 1 THEN ' GRAPH       ,'               END
                   || CASE WHEN output_eventcnt    = 1 THEN ' NUM_WAITS,'                  END
                   || CASE WHEN output_eventcnt_s  = 1 THEN '  WAITS/SEC,'                 END
                   || CASE WHEN output_eventavg    = 1 THEN '   AVERAGES                                    ' END
            ;


            if g_snap_begin is null then
                if pagesize > 0 and mod(c-1, pagesize) = 0 then
                    output(rpad('-',length(gv_header_string),'-'));
                    output(gv_header_string);
                    output(rpad('-',length(gv_header_string),'-'));
                else
                    if pagesize = -1 and c = 1 then
                        output(gv_header_string);
                    end if;
                end if;
            else
                output('Taking BEGIN sample ...');
            end if;

            -- TODO raise an error if both begin and end are used together
            -- TODO conditionally comment out the refcursor use unless begin and end is used

            -- manual before/after snapshots (snapper v4)
            if g_snap_begin is not null or g_snap_end is not null then
                if g_snap_begin is not null then
                    get_sessions;
                    snap(d1,s1,l1,g_snap1);
&_MANUAL_SNAPSHOT   open :snapper for select column_value rec from table(g_snap1); -- if you see this error then run: "VAR SNAPPER REFCURSOR" first!
                    exit;
                end if;

                if g_snap_end is not null then
&_MANUAL_SNAPSHOT   fetch :snapper bulk collect into g_snap1; -- You should run snapper with BEGIN option first!
                    -- procedure snap_from_stats_string (p_string_stats in sys.dbms_debug_vc2coll, p_snapdate out date, p_stats out stab, l_stats out ltab)
                    snap_from_stats_string(g_snap1, d1, s1, l1);
                end if;
            else -- normal interval sampling
                if c = 1 then
                    get_sessions;
                    snap(d1,s1,l1,g_snap1);
                else
                    get_sessions;
                    d1 := d2;
                    s1 := s2;
                    g_snap1 := g_snap2;
                end if; -- c = 1
            end if;

        end if; -- gather_stats = 1



        -- ASH style sampling 
&_USE_DBMS_LOCK ash_date1 := sysdate; 
&_USE_DBMS_LOCK if gather_ash = 1 then 
&_USE_DBMS_LOCK     while sysdate < (ash_date1 + (&snapper_sleep/86400)) loop
&_USE_DBMS_LOCK         -- get active session records from g_sessions
&_USE_DBMS_LOCK         get_sessions;
&_USE_DBMS_LOCK         extract_ash();
&_USE_DBMS_LOCK         -- sleep timeout backoff depending on the duration sampled (for up to 10 seconds total sampling time will get max 100 Hz sampling)
&_USE_DBMS_LOCK         -- for longer duration sampling the algorithm will back off and for long durations (over 100 sec) the sampling rate will stabilize
&_USE_DBMS_LOCK         -- at 1Hz
&_USE_DBMS_LOCK         dbms_lock.sleep( greatest(0.1,(least(1,&snapper_sleep*&snapper_count/100))) );
&_USE_DBMS_LOCK     end loop;
&_USE_DBMS_LOCK else
&_USE_DBMS_LOCK     dbms_lock.sleep( ((ash_date1+(&snapper_sleep/86400)) - sysdate)*86400 ); 
&_USE_DBMS_LOCK     null;
&_USE_DBMS_LOCK end if;
&_USE_DBMS_LOCK ash_date2 := sysdate; 

        -- sesstat new sample and delta calculation
        if gather_stats = 1 then

            get_sessions;
            snap(d2,s2,l2,g_snap2);

            -- manually coded nested loop outer join for calculating deltas:
            -- why not use a SQL join? this would require creation of PL/SQL 
            -- collection object types, but Snapper does not require any changes 
            -- to the database, so any custom object types are out! 
            changed_values := 0;
            missing_values_s1 := 0;
            missing_values_s2 := 0;

            -- remember last disappeared SID so we wouldn't need to output a warning 
            -- message for each statistic row of that disappeared sid 
            disappeared_sid := 0;

            i :=1; -- iteration counter (for debugging)
            a :=1; -- s1 array index
            b :=1; -- s2 array index
            
            if s2.count > 0 then lv_curr_sid := s2(b).sid; end if;

            while ( a <= s1.count and b <= s2.count ) loop

                if lv_curr_sid != 0 and lv_curr_sid != s2(b).sid then
                    if pagesize > 0 and mod(c-1, pagesize) = 0 then
                        -- if filtering specific stats, assuming that it's better to not leave spaces between every session data

                        if getopt('&snapper_options', 'sinclude=')||getopt('&snapper_options', 'tinclude=' )||getopt('&snapper_options', 'winclude=' ) is null then
                            output(' ');
                            -- output(rpad('-',length(gv_header_string),'-'));
                            -- output(gv_header_string);
                            -- output(rpad('-',length(gv_header_string),'-'));
                        end if;
                    end if;
                    lv_curr_sid := s2(b).sid;
                end if;

                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;
                                evcnt := s2(b).event_count - s1(a).event_count;
                                --output('DEBUG, 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);
                                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;
                                evcnt := s2(b).event_count;
                                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;
                        evcnt := s2(b).event_count;
                        if delta != 0 then fout(); end if;

                        b := b + 1;

                    when s1(a).sid < s2(b).sid then

                        if disappeared_sid != s1(a).sid then
                            output('WARN, Session has disappeared since previous snapshot, ignoring SID='||to_char(s1(a).sid)||' debug(a='||to_char(a)||' b='||to_char(b)||' s1.count='||s1.count||' s2.count='||s2.count||' s2.count='||s2.count||')');
                        end if;
                        disappeared_sid := s1(a).sid;                    
                        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(' ');
                --output('--  End of Stats snap '||to_char(c)||', end='||to_char(d2, 'YYYY-MM-DD HH24:MI:SS')||', seconds='||to_char(case get_seconds(d2-d1) when 0 then (&snapper_sleep) else round(get_seconds(d2-d1), 1) end)); 
                output('--  End of Stats snap '||to_char(c)||', end='||to_char(d2, 'YYYY-MM-DD HH24:MI:SS')||', seconds='||round(get_seconds(d2-d1), 1)); 
            end if;

            output(' ');

        end if; -- gather_stats = 1

        if gather_ash = 1 then

            -- get ASH sample grouping details
            g_ash_columns  := nvl( getopt('&snapper_options', 'ash='  ), g_ash_columns  );

            -- optional additional ASH groupings
            g_ash_columns1 := case when getopt('&snapper_options', 'ash1' ) is null then null when getopt('&snapper_options', 'ash1' ) = chr(0) then g_ash_columns1 else getopt('&snapper_options', 'ash1=' ) end;
            g_ash_columns2 := case when getopt('&snapper_options', 'ash2' ) is null then null when getopt('&snapper_options', 'ash2' ) = chr(0) then g_ash_columns2 else getopt('&snapper_options', 'ash2=' ) end;
            g_ash_columns3 := case when getopt('&snapper_options', 'ash3' ) is null then null when getopt('&snapper_options', 'ash3' ) = chr(0) then g_ash_columns3 else getopt('&snapper_options', 'ash3=' ) end;

            -- group ASH records and print report
            out_ash( g_ash_columns, 10 );
            -- group and print optional ASH reports
            if g_ash_columns1 is not null then out_ash( g_ash_columns1, 10 ); end if;
            if g_ash_columns2 is not null then out_ash( g_ash_columns2, 10 ); end if;
            if g_ash_columns3 is not null then out_ash( g_ash_columns3, 10 ); end if;


            if pagesize > 0 then 
                output(' '); 
                --output('--  End of ASH snap '||to_char(c)||', end='||to_char(ash_date2, 'YYYY-MM-DD HH24:MI:SS')||', seconds='||to_char(case (ash_date2-ash_date1) when 0 then (&snapper_sleep) else round((ash_date2-ash_date1) * 86400, 1) end)||', samples_taken='||g_ash_samples_taken); 
                output('--  End of ASH snap '||to_char(c)||', end='||to_char(ash_date2, 'YYYY-MM-DD HH24:MI:SS')||', seconds='||to_char(round((ash_date2-ash_date1) * 86400, 1))||', samples_taken='||g_ash_samples_taken); 
                output(' '); 
            end if;

            reset_ash();

        end if; -- gather_ash = 1

    end loop; -- for c in 1..snapper_count

end;
/

undefine snapper_oraversion
undefine snapper_sleep
undefine snapper_count
undefine snapper_sid
undefine ssid_begin
undefine _IF_ORA11_OR_HIGHER
undefine _IF_LOWER_THAN_ORA11
undefine _NO_BLK_INST
undefine _YES_BLK_INST
undefine _NO_PLSQL_OBJ_ID
undefine _YES_PLSQL_OBJ_ID
undefine _IF_DBMS_SYSTEM_ACCESSIBLE
undefine _IF_X_ACCESSIBLE
undefine _MANUAL_SNAPSHOT
undefine _USE_DBMS_LOCK
col snapper_ora11higher clear
col snapper_ora11lower  clear
col dbms_system_accessible clear

set serveroutput off