My session workload

My old website (now archived in the Wayback Machine) was due to disappear at the end of July 2020 but there were a couple of timeless articles on it that were worth saving and although the popularity of this one has probably been surpassed by Tanel Poder’s Snapper script, or other offerings by Tom Kyte or Adrian Billington, it’s still one of those useful little things to have around – it’s a package to takes a snapshot of your session stats.

The package depends on a view created in the SYS schema, and the package itself has to be installed in the SYS schema – which is why other strategies for collecting the information have become more popular; but if you want to have it handy, here are the two scripts:

rem
rem     Script:         c_mystats.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2001
rem     Purpose:        Put names to v$mystat
rem
rem     Last tested
rem             12.1.0.2        -- naming issue
rem             11.2.0.4
rem             10.2.0.5
rem             10.1.0.4
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Notes:
rem     Should be run by SYS - which means it has to be re-run
rem     on a full database export/import
rem
rem     It looks as if it is illegal to create a view with a
rem     name starting with v$ in the sys account as from 12c.
rem     (ORA-00999: invalid view name). Hence the JV$ name.
rem
rem     But you can create a public synonym starting "v$"
rem

create or replace view jv$my_stats
as
select
        /*+
                first_rows
                ordered
        */
        ms.sid,
        sn.statistic#,
        sn.name,
        sn.class,
        ms.value
from
        v$mystat        ms,
        v$statname      sn
where
        sn.statistic# = ms.statistic#
;

drop public synonym v$my_stats;
create public synonym v$my_stats for jv$my_stats;
grant select on v$my_stats to public;

rem
rem     Script:         snap_myst.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2001
rem     Purpose:        Package to get snapshot start and delta of v$mystat
rem
rem     Last tested
rem             19.3.0.0
rem             12.2.0.1
rem             12.1.0.2
rem             11.2.0.4
rem             10.2.0.5
rem             10.1.0.4
rem              9.2.0.8
rem              8.1.7.4
rem
rem     Notes
rem     Has to be run by SYS to create the package
rem     Depends on view (j)v$my_stats (see c_mystats.sql)
rem
rem     Usage:
rem             set serveroutput on size 1000000 format wrapped
rem             set linesize 120
rem             set trimspool on
rem             execute snap_my_stats.start_snap
rem             -- do something
rem             execute snap_my_stats.end_snap
rem

create or replace package snap_my_stats as
        procedure start_snap;
        procedure end_snap (i_limit in number default 0);
end;
/

create or replace package body snap_my_stats as

cursor c1 is
        select 
                statistic#, 
                name,
                value
        from 
                v$my_stats
        where
                value != 0
        ;

        type w_type is table of c1%rowtype index by binary_integer;
        w_list          w_type;
        empty_list      w_type;

        m_start_time    date;
        m_start_flag    char(1);
        m_end_time      date;

procedure start_snap is
begin

        m_start_time := sysdate;
        m_start_flag := 'U';
        w_list := empty_list;

        for r in c1 loop
                w_list(r.statistic#).value := r.value;
        end loop;

end start_snap;


procedure end_snap (i_limit in number default 0) 
is
begin

        m_end_time := sysdate;

        dbms_output.put_line('---------------------------------');

        dbms_output.put_line('Session stats - ' ||
                                to_char(m_end_time,'dd-Mon hh24:mi:ss')
        );

        if m_start_flag = 'U' then
                dbms_output.put_line('Interval:-  '  || 
                                trunc(86400 * (m_end_time - m_start_time)) ||
                                ' seconds'
                );
        else
                dbms_output.put_line('Since Startup:- ' || 
                                to_char(m_start_time,'dd-Mon hh24:mi:ss')
                );
        end if;

        if (i_limit != 0) then
                dbms_output.put_line('Lower limit:-  '  || i_limit);
        end if;

        dbms_output.put_line('---------------------------------');

        dbms_output.put_line(
                rpad('Name',60) ||
                lpad('Value',18)
        );

        dbms_output.put_line(
                rpad('----',60) ||
                lpad('-----',18)
        );

        for r in c1 loop
                if (not w_list.exists(r.statistic#)) then
                    w_list(r.statistic#).value := 0;
                end if;

                if (
                       (r.value > w_list(r.statistic#).value + i_limit)
                ) then
                        dbms_output.put(rpad(r.name,60));
                        dbms_output.put(to_char(
                                r.value - w_list(r.statistic#).value,
                                        '9,999,999,999,990')
                        );
                        dbms_output.new_line;
                end if;
        end loop;

end end_snap;

begin
        select
                logon_time, 'S'
        into
                m_start_time, m_start_flag
        from
                v$session
        where
                sid =   (
                                select /*+ no_unnest */ sid 
                                from v$mystat 
                                where rownum = 1
                        );

end snap_my_stats;
/

drop public synonym snap_my_stats;
create public synonym snap_my_stats for snap_my_stats;
grant execute on snap_my_stats to public;

One point to be cautious about with this package: do not embed it inside anonymous pl/sql blocks, e.g.

begin

        snap_my_stats.start_snap;

        -- some other code

        snap_my_stats.end_snap;

end;
/

There are some statistics in v$my_stats (v$mystat / v$sesstat) which are not updated until the end of a database call – and calling an anonymous pl/sql block counts as a single database call, so some of your statistics (for example “CPU used by this session”) will report misleading values until after the call to the pl/sql block ends.