Oracle Scratchpad

October 6, 2016

My session workload

Filed under: Oracle,Performance,Troubleshooting — Jonathan Lewis @ 1:19 pm BST Oct 6,2016

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.

8 Comments »

  1. […] code to calculate and report changes in the session stats – that’s the calls to the package snap_my_stats. Here are some of the more interesting […]

    Pingback by Interval Partition Problem | Oracle Scratchpad — February 21, 2018 @ 8:40 am GMT Feb 21,2018 | Reply

  2. […] calls to snap_my_stats are using a package I wrote a long time ago to report a delta in my session’s stats. The call […]

    Pingback by Index splits | Oracle Scratchpad — October 29, 2018 @ 1:28 pm GMT Oct 29,2018 | Reply

  3. […] dynamic performance views over short periods of time (there’s an example of the code for v$mystat here) In this case, as the names suggest, the snapshots record the changes in v$rowcache (the dictionary […]

    Pingback by Describe Upgrade | Oracle Scratchpad — April 8, 2019 @ 11:02 am BST Apr 8,2019 | Reply

  4. […] made use of a couple of little utilities I wrote years ago to take snapshots of my session statistics and the library cache (v$librarycache) stats. I’ve also used my “repetition” […]

    Pingback by Parse Calls | Oracle Scratchpad — April 23, 2019 @ 6:31 pm BST Apr 23,2019 | Reply

  5. […] packages are some code I wrote a couple of decades ago to report changes in my session’s activity stats and wait […]

    Pingback by DB links | Oracle Scratchpad — July 3, 2019 @ 1:16 pm BST Jul 3,2019 | Reply

  6. […] a symbolic dump of the current log file (“alter system dump logfile”). The calls to the snap_my_stats package allow me to see the changes in the session activity stats due to the update. A simple check […]

    Pingback by Update restarts | Oracle Scratchpad — September 10, 2019 @ 11:00 am BST Sep 10,2019 | Reply

  7. […] call to the package snap_my_stats is my standard method for reporting changes in v$mystat across the test. I’ve called the […]

    Pingback by _cursor_obsolete_threshold | Oracle Scratchpad — October 2, 2019 @ 2:39 pm BST Oct 2,2019 | Reply

  8. […] You’ll need SYS to grant you select on v_$mystat to use this one, of course, but v$mystat is a very convenient view giving you the session activity stats since logon for your own session – so there ought to be some mechanism in place that allows you to see some form of it anyway (ideally including the join to v$statname). […]

    Pingback by My SID | Oracle Scratchpad — February 1, 2021 @ 11:25 am GMT Feb 1,2021 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.