JL Computer Consultancy

Snapshots revisited

Mar 2004

A few years ago, I published an example of the type of PL/SQL code I had
been using to take snapshots of some of the dynamic
performance views as an aid to trouble-shooting. The original mechanism
used a package owned by the sys account, and allowed you to
specify a time-frame for taking a pair of snapshots and then calculating and
reporting the differences.

I have many alternative implementations of the same approach but the one
mechanism which I tend to use most frequently, because it is easier to take
snapshots of two or three dynamic views simultaneously, involves one package
per dynamic view and the use of global pl/sql table variables.

As an example of the method, the code below shows how to capture the
statistics for the current session. There are actually two parts to this
example. Part one is a script that creates an enhanced view of v$mystat
(a view that has been around for a long time, and effectively shows only the
set of statistics from v$sesstat that belong to the current
session). Part two is a script to create a package that uses the view, and
contains procedures to take the snapshots and find the difference.

In
the creation of v$my_stats, I make use of the basic v$ views. This often
gives adequate performance; but remember that some v$ views return lots of
data, and are protected by latches, and you may want to think very carefully of
ways to enhance performance by visiting the underlying x$ objects, and adding
suitable hints.

rem

remScript:snap_myst.sql

remAuthor:Jonathan
Lewis

remDated:March
2001

remPurpose:Package
to get snapshot start and delta of v$mystat

rem

remNotes

remHas to be run by SYS to create the package

remDepends on view v$my_stats (see
c_my_stats.sql)

rem

remUsage:

remset serveroutput on size 1000000
format wrapped

remset linesize 120

remset trimspool on

remexecute snap_my_stats.start_snap

rem-- do something

remexecute snap_my_stats.end_snap

rem

create
or replace package snap_my_stats as

procedure start_snap;

procedure end_snap;

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;

m_start_timedate;

m_start_flagchar(1);

m_end_timedate;

procedure
start_snap is

begin

m_start_time := sysdate;

m_start_flag := 'U';

for r in c1 loop

w_list(r.statistic#).value :=
r.value;

end loop;

end start_snap;

procedure
end_snap 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;

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 (

(w_list(r.statistic#).value != r.value)

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

--

--Instantiation code - get system startup
time just

--in case the user wants stats since session
started

--

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;

I
believe this snapshot code should work on all versions of Oracle since 8.0
– although I may be wrong; I no longer remember if the pl/sql internal
code to handle the exists operator appeared in 8.0 or 8.1.

For
newer versions of Oracle, the code could be re-written to do array
fetches into two different arrays, before looping to find the
differences. This would minimise the number of calls to the database kernel, at
a cost of a little more memory and CPU. It probably wouldn’t make much
difference to the cost of execution in this particular case, but should be
considered as an option when you look at some of the other v$ dynamic views.

One
of the standard ways I use this snapshot is in place of the autotrace
statistics option. In favour of autotrace is the fact that SQL*Plus
‘cheats’ and creates a second session on your connection so that
the work done by autotrace doesn’t affect the statistics from the trace.
On the other hand, autotrace doesn’t show all the statistics that I want to
see; and if you happen to be running some code as SYS, autotrace doesn’t
report any useful statistics at all (which is a bug/defect fixed somewhere late
in 9.2)

One
point to beware of with this package: do not embed it inside anonymous pl/sql
blocks, eg.

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 zero
values.