My old website (www.jlcomp.demon.co.uk) will be disappearing in a couple of weeks – but there are a couple of timeless articles on it that are 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 126.96.36.199 -- naming issue rem 188.8.131.52 rem 10.2.0.5 rem 10.1.0.4 rem 184.108.40.206 rem 220.127.116.11 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 18.104.22.168 rem 22.214.171.124 rem 10.2.0.5 rem 10.1.0.4 rem 126.96.36.199 rem 188.8.131.52 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.