New benchmark ratio proposal
Gang,
In the spirit of the BR and BTR I want to propose a new benchmark ratio and
I'd like some feedback on whether anyone else thinks this may help and also
some feel for what a 'good' value range may be if such exists. Anyone with a
system that's flying and/or one that's crawling please give the attached SPL
procedure a try and report back to me. The procedure calculates and returns,
in order, the Bufwaits Ratio (BR), ReadAhead Utilization (RAU), Buffer
Turnover Rate (BTR), the new ratio - let's call it Used Buffer Turnover Rate
or UBTR, the time elapsed since onstat -z and the time of that last reset.
Obviously on a system where all buffers are being used, BTR == UBTR, but
that's not always the case, especially on a poorly tuned server.
Anyone who's completely uninterested, please accept my apology for wasting
the bandwidth, everyone else: Have fun.
Art S. Kagel
-------------------- PROCEDURE STARTS, please cut here --------------------
-- drop procedure ratios;
create procedure ratios()
returning decimal(10,2), decimal(10,2), decimal(10,2), decimal(10,2),
interval hour(5) to second, datetime year to second;
define pagreads, bufwrites, buffwts, buffers, rapgs_used, btradata,
btraidx, dpra, stathrs, usedbuffs decimal(10,2);
define br, ubtr, btr, rau decimal(10,2);
define stattime interval hour(5) to second;
define stattemp interval day(3) to second;
define statsecs interval second(9) to second;
define whenreset datetime year to second;
define lname char(13);
define tempstr char(12);
define lvalue, resettime, tempint integer;
-- trace on;
FOREACH
select name, value
into lname, lvalue
from sysprofile
where name in ('pagreads','bufwrites', 'buffwts', 'rapgs_used', 'btradata',
'btraidx', 'dpra')
if lname == 'pagreads' then
let pagreads = lvalue;
elif lname == 'bufwrites' then
let bufwrites = lvalue;
elif lname == 'buffwts' then
let buffwts = lvalue;
elif lname == 'rapgs_used' then
let rapgs_used = lvalue;
elif lname == 'btradata' then
let btradata = lvalue;
elif lname == 'btraidx' then
let btraidx = lvalue;
elif lname == 'dpra' then
let dpra = lvalue;
end if
END FOREACH;
select count(*)
into tempint
from systables st, syscolumns sc
where st.tabid = sc.tabid
and tabname = 'sysbufhdr'
and colname = 'pagenum';
if tempint == 0 then
-- IDS 9.40+
select count(*)
into usedbuffs
from sysbufhdr
where offset >= 0 and chunk > 0;else
-- IDS pre- 9.40
select count(*)
into usedbuffs
from sysbufhdr
where pagenum > 0;end if
select current year to second
- ( sh_curtime - sh_pfclrtime) units second,
( sh_curtime - sh_pfclrtime) units second
into whenreset, statsecs
from sysshmvals;
let stattemp = current year to second - whenreset;
let stattime = stattemp;
let tempstr = (statsecs / 3600);
let stathrs = tempstr;
select cf_effective
into buffers
from sysconfig
where cf_name = 'BUFFERS';
if (pagreads + bufwrites) > 0 then
let br = ((buffwts * 100.00) / (pagreads + bufwrites));
else
let br = 0.00;
end if
if (btradata + btraidx + dpra) > 0 then
let rau = ((rapgs_used * 100) / (btradata + btraidx + dpra));
else
let rau = 100.00;
end if
if buffers > 0 and stathrs > 0 then
let btr = (((pagreads + bufwrites) / buffers) / stathrs);
else
let btr = 0;
end if
if usedbuffs > 0 and stathrs > 0 then
let ubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
else
let ubtr = 0;
end if
-- trace off;
return br, rau, btr, ubtr, stattime, whenreset;
end procedure
document
'Procedure ratios.',
' Calculate critical ratios BR, BTR, and RAU and return with time of and ',
' time since stats were last reset (onstat -z).',
'Written and copyright by Art S. Kagel, 2004. ',
'Permission for all reasonable use is granted without restriction. '
with listing in 'ratios.sql';
-------------------- END PROCEDURE --------------------