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.

Back to Index of Topics


rem   Script:           c_mystats.sql

rem   Author:           Jonathan Lewis

rem   Dated:            March 2001

rem   Purpose:          Put names to v$mystat


rem   Note:

rem   Should be run by SYS - which means it has to be re-run

rem   on a full database export/import


rem   Use first_rows and ordered to avoid a sort/merge join, and

rem   to allow faster elimination of the 'value = 0' rows.



create or replace view v$my_stats












      v$mystat    ms,

      v$statname  sn


      sn.statistic# = ms.statistic#



drop public synonym v$my_stats;

create public synonym v$my_stats for v$my_stats;

grant select on v$my_stats to public;


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     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     Notes

rem     Has to be run by SYS to create the package

rem     Depends on view v$my_stats (see c_my_stats.sql)


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



create or replace package snap_my_stats as

        procedure start_snap;

        procedure end_snap;




create or replace package body snap_my_stats as


cursor c1 is








               value != 0




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

        w_list w_type;


        m_start_time   date;

        m_start_flag   char(1);

        m_end_time     date;


procedure start_snap is



        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



        m_end_time := sysdate;



        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'



               dbms_output.put_line('Since Startup:- ' ||

                               to_char(m_start_time,'dd-Mon hh24:mi:ss')


        end if;





               rpad('Name',60) ||





               rpad('----',60) ||




        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



                               r.value - w_list(r.statistic#).value,




               end if;

        end loop;


end end_snap;



--      Instantiation code - get system startup time just

--      in case the user wants stats since session started





               logon_time, 'S'


               m_start_time, m_start_flag




               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.



-- some other code



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.

Here’s a sample of the output from a database running :


Session stats - 24-Mar 10:04:16

Interval:-  14 seconds


Name                                                                     Value

----                                                                     -----

opened cursors cumulative                                                    5

user calls                                                                   8

recursive calls                                                             69

session logical reads                                                   91,525

CPU used when call started                                                  64

CPU used by this session                                                    64

session uga memory max                                                  65,464

session pga memory                                                     -65,536

session pga memory max                                                  65,536

consistent gets                                                         91,525

physical reads                                                             855

free buffer requested                                                      855

consistent gets - examination                                            2,741

shared hash latch upgrades - no wait                                    39,570

calls to get snapshot scn: kcmgss                                            5

no work - consistent read gets                                          49,381

table scans (short tables)                                                   1

table scan rows gotten                                                      66

table scan blocks gotten                                                     4

table fetch by rowid                                                    43,505

table fetch continued row                                                    1

rows fetched via callback                                                1,369

index fetch by key                                                       1,369

index scans kdiixs1                                                     39,401

cursor authentications                                                       1

buffer is pinned count                                                  63,938

buffer is not pinned count                                              63,844

parse time cpu                                                               1

parse time elapsed                                                          12

parse count (total)                                                          4

parse count (hard)                                                           2

parse count (failures)                                                       1

execute count                                                                5

bytes sent via SQL*Net to client                                           802

bytes received via SQL*Net from client                                     934

SQL*Net roundtrips to/from client                                            5


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00

Back to Index of Topics