JL Computer Consultancy

Capturing work done

Aug 2007


Update 11th Aug 2007


A few years ago I published an example of the code that I use to take ‘micro-Statspack’ snapshots of the dynamic performance views. The concept is very simple – pick a view, such as v$session_event, and write a package with two procedure calls, a “start snapshot” which simply records the current contents of the view, and an “end snapshot” that queries the view a second time and works out the difference between the original figures and the current figures. In the example I published the “end snapshot” procedure reported the results in real time using dbms_output.

This approach has served me well over the years but recently I needed to take some snapshots from inside a reporting tool – which meant I would not be able to see the results of calling dbms_output so, having resisted the urge for many years, I finally modified one of the packages to dump its results into a couple of tables.

We start with the script to create the necessary tables. In this case I am going to capture only v$mystat and v$session_event for the current session, so we have one table for each dynamic performance view, and a control table. (The purge option on the drop command is specific to 10g)


 

drop table jpl_snap_events purge;

drop table jpl_snap_stats purge;

drop table jpl_snap_log purge;

 

create table jpl_snap_log(

        report_name     varchar2(32)    not null,

        job_id          number(10,0)    not null,

        start_time      date            not null,

        constraint jsl_pk primary key(report_name, job_id,  start_time)

)

organization index compress 1

;

 

create table jpl_snap_stats(

        report_name     varchar2(32)    not null,

        job_id          number(10,0)    not null,

        start_time      date            not null,

        statistic#      number(4,0)     not null,

        value           number(16,0),

        constraint jss_pk     primary key(report_name, job_id, start_time, statistic#),

        constraint jss_jsl_fk foreign key(report_name, job_id, start_time)
                              references jpl_snap_log

                              on delete cascade

)

organization index compress 3

;

 

 

create table jpl_snap_events(

        report_name         varchar2(32)    not null,

        job_id              number(10,0)    not null,

        start_time          date            not null,

        event_id            number(12,0)    not null,

        total_waits         number(16,0),

        total_timeouts      number(16,0),

        time_waited_micro   number(16,0),

        max_wait            number(16,2),

        constraint jse_pk     primary key(report_name, job_id, start_time, event_id),

        constraint jse_jsl_fk foreign key(report_name, job_id, start_time)
                              references jpl_snap_log

                              on delete cascade

)

organization index compress 3

;

 

In this particular case the reports were running through a report server and each report was given a (meaningless. sequential) job id as it ran. Since the job sequence was reset every time the report server was restarted, I added the start time to ensure that we had a guaranteed unique key for each job in the jpl_snap_log table.

You will notice that I’ve declared the main statistics tables to be index organized, with compression on the first three columns – the primary key of the jpl_snap_log table is both a foreign key to the stats collection tables, and the leading edge of the primary key. (We append the statistic# from v$mystat or the event_id from v$session_event to complete the primary keys – the latter tells you that this code is for a 10g system only – you will have to use the event column or use the underlying x$ object if you want to modify the code for 8i and 9i).

Using IOTs with compression minimises the space usage; the foreign key constraint – defined with on delete cascade – makes it easy to delete old data.

With the tables in place, we can move on to the package body:



create or replace package jpl_snap_work as

    procedure start_snap(i_report_name  in varchar2, i_job_id number);

    procedure end_snap(i_report_name    in varchar2, i_job_id number);

end;

/

Using the package is very easy:  at some point in your code, you simply issue a call to jpl_snap_work.start_snap() passing in a name and number, and at some later point you call jpl_snap_work.end_snap() quoting the same name and number.

The current version of the code is far from bullet-proof: if you call end_snap() without first calling start_snap() then the call will fail with Oracle error ORA-01400 (cannot insert NULL into mandatory column).

The package body is quite short. Again, there is a specific 10g feature involved.


 

create or replace package body jpl_snap_work as

 

    g_start_time    date;

    g_sid           number(6);

 

procedure start_snap(i_report_name in varchar2, i_job_id number) is

 

begin

    select  sid , sysdate

    into    g_sid, g_start_time

    from    v$mystat

    where   rownum = 1

    ;

 

    insert into jpl_snap_log values(

            i_report_name, i_job_id, g_start_time

    );

 

    insert into jpl_snap_stats

    select

            i_report_name,

            i_job_id,

            g_start_time,

            statistic#,

            value

    from    v$mystat

    where   value != 0

    ;

 

    insert into jpl_snap_events

    select

            i_report_name,

            i_job_id,

            g_start_time,

            event_id,

            total_waits,

            total_timeouts,

            time_waited_micro,

            max_wait

    from

            v$session_event where sid = g_sid

    ;

 

    commit;

 

end start_snap;

 

 

procedure end_snap(i_report_name in varchar2, i_job_id number) is

begin

 

    merge into

            jpl_snap_stats  pre

    using (

        select

                i_report_name   report_name,

                i_job_id        job_id,

                g_start_time    start_time,

                statistic#,

                value

        from    v$mystat

        where   value != 0

    )   pst

    on  (    pst.report_name = pre.report_name

         and pst.job_id      = pre.job_id

         and pst.start_time  = pre.start_time

         and pst.statistic#  = pre.statistic#

        )

    when not matched then

        insert (report_name, job_id, start_time, statistic#, value)

        values (i_report_name, i_job_id, g_start_time, pst.statistic#, pst.value)

    when matched then

        update set value = pst.value - pre.value

        delete where value = 0

    ;

 

    merge into

            jpl_snap_events  pre

    using (

        select

                i_report_name   report_name,

                i_job_id        job_id,

                g_start_time    start_time,

                event_id,

                total_waits,

                total_timeouts,

                time_waited_micro,

                max_wait

        from

                v$session_event where sid = g_sid

    )   pst

    on  (    pst.report_name = pre.report_name

         and pst.job_id      = pre.job_id

         and pst.start_time  = pre.start_time

         and pst.event_id    = pre.event_id

        )

    when not matched then

        insert(

               report_name, job_id, start_time,
               event_id, total_waits, total_timeouts, time_waited_micro, max_wait

        )

        values (

                i_report_name, i_job_id, g_start_time,

                pst.event_id, pst.total_waits, pst.total_timeouts,

                pst.time_waited_micro, pst.max_wait

        )

    when matched then

        update set

            total_waits       = pst.total_waits - pre.total_waits,

            total_timeouts    = pst.total_timeouts - pre.total_timeouts,

            time_waited_micro = pst.time_waited_micro - pre.time_waited_micro,

            max_wait          = pst.max_wait

        delete where total_waits = 0

    ;

 

    commit;

 

end end_snap;

 

end jpl_snap_work;

/

Key points to note: 

I have captured the calling session sid in the start_snap() procedure – this makes it possible to be as efficient as possible when querying v$session_event; at the same time I have captured the start time so that I can use it to identify the correct rows to update in the end_snap() procedure..

I have included a commit in both procedures – you may have reasons why this is not appropriate in your environment; you may want to make the package transactions autonomous.

I have used the merge command in the end_snap() procedure including the 10g delete option in the update branch to eliminate any rows which show no change in value. If you want to re-engineer the code for 9i, you could remove the delete from the merge and add it as a separate statement.

Note – there seems to be a bug in the 10.2.0.2 and 10.2.0.3 with this option – although the “tram-tracks” in the 10g SQL Reference manual show the “when matched update” branch appearing before the “when not matched insert” branch, if you write the code in that order the delete does not work.

The captured data has been kept to a minimum to avoid space wastage – so when you want to examine the results, you have to write a script that joins to v$statname and v$event_name and does any other visual enhancement you want to introduce; for example:


 

define m_report_name = '&what_report_name'

define m_job_id      = &what_job_id

 

column  start_time  new_value m_start_time

 

select

        to_char(start_time,'yyyy-mm-dd hh24:mi:ss') start_time

from

        jpl_snap_log

where

        job_id = &m_job_id

and     report_name = '&m_report_name'

;

 

column  name    format a64

column  value   format 999,999,999,999,999

 

select

        vsn.name, jss.value

from

        jpl_snap_stats  jss,

        v$statname      vsn

where

        jss.report_name = '&m_report_name'

and     jss.job_id      = &m_job_id

and     jss.start_time  = to_date('&m_start_time','yyyy-mm-dd hh24:mi:ss')

and     vsn.statistic#  = jss.statistic#

;

 

column  name    format a40

 

select

        ven.name,

        jse.total_waits,

        round(jse.time_waited_micro/1000000,2)              wait_sec,

        round(jse.time_waited_micro/(10000*total_waits),2)  avg_cs,

        jse.max_wait                                        max_cs

from

        jpl_snap_events     jse,

        v$event_name        ven

where

        jse.job_id      = &m_job_id

and     jse.report_name = '&m_report_name'

and     jse.start_time  = to_date('&m_start_time','yyyy-mm-dd hh24:mi:ss')

and     ven.event_id    = jse.event_id

;

The following is a brief extract from a sample run of the above script (with a few SQL*Plus formatting commands in place):


 

REPORT_NAME                      START_TIME

-------------------------------- -------------------

ABC                              2007-08-09 21:15:45

 

1 row selected.

 

 

NAME                                                                            VALUE

---------------------------------------------------------------- --------------------

db block gets                                                                     238

db block gets from cache                                                          239

db block changes                                                                  251

redo entries                                                                       32

redo size                                                                      29,072

undo change vector size                                                        11,920

IMU undo allocation size                                                       34,608

IMU Redo allocation size                                                       36,528

parse time cpu                                                                      4

parse time elapsed                                                                  4

parse count (total)                                                                18

parse count (hard)                                                                  8

parse count (failures)                                                              1

execute count                                                                      22

sorts (memory)                                                                      2

sorts (rows)                                                                      111

 

NNN rows selected.

 

 

NAME                                     TOTAL_WAITS   WAIT_SEC     AVG_CS     MAX_CS

---------------------------------------- ----------- ---------- ---------- ----------

log file sync                                      1          0        .06          0

SQL*Net message to client                          5          0          0          0

SQL*Net message from client                        5       5.73      114.5      10338

 

3 rows selected.


Update 11th Aug 2007:  In a private email, Tanel Poder has pointed out that since this example was designed for 10g it might be a nice idea to add in the v$sess_time_model information as well.  Implementation is left as an exercise for the reader, but the primary key for the new table has to include the stat_id from v$sess_time_model.


Back to Index of Topics