JL Computer Consultancy
Capturing work done |
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.
