Ever wonder what tables are in your buffer cache and how many pages each
are contributing? Onstat -P too terse and onstat -B too voluminous? Try
this script:

Art S. Kagel

########### CUT HERE ############
#!/usr/bin/ksh
which nawk|fgrep "no nawk in" >/dev/null
if [[ $? = 0 ]]; then
nawk=0;
else
nawk=1;
AWK=nawk;
fi

if [[ $nawk -eq 0 ]]; then
which gawk|fgrep "no gawk in" >/dev/null
if [[ $? = 0 ]]; then
gawk=0;
else
gawk=1;
AWK=gawk;
fi
fi

if [[ $nawk -eq 0 && $gawk -eq 0 ]]; then
echo "nawk or gawk needed to run this script. Try converting it to perl"
echo "with a2p if you do not have either."
exit 1
fi

(
sqlcmd -H -d sysmaster <<EOF
select dbsname, tabname, count(*)||' ' table_count, " " database_count
from sysextents se, sysbufhdr bh
where bh.pagenum between se.start and (se.start + se.size - 1)
group by 1, 2, 4
UNION ALL
select dbsname, " TOTAL:", " " table_count, count(*)||' '
from sysextents se, sysbufhdr bh
where bh.pagenum between se.start and (se.start + se.size - 1)
group by 1, 2, 3
order by 1, 2;
EOF
) |$AWK -F\| '$1=="dbsname"{
gsub("_"," ",$0);
printf "\n\n%-18s %-18s %-10s %-10s\n", toupper($1), toupper($2), toupper($3), toupper($4);
printf "%-18s %-18s %-10s %-10s\n", "------------------", "------------------", "-----------", "--------------";
next;
}
$3==" "{
printf "%-18s %-18s %10s %10d\n", $1, " ", " ", $4;
next;
}
$4==" "{
printf "%-18s %-18s %10d\n", " ", $2, $3;
next;
}'

Thanks, that is very useful. Gave me a list of tables that need better indexes.

But... sysbufhdr.pagenum doesn't exist on 9.4. Has it been renamed to offset?

Would this be correct on 9.4?

select dbsname, tabname, count(*) as buffer_count
from sysextents se, sysbufhdr bh
where bh.offset between se.offset and (se.offset + se.size - 1)
group by 1, 2
order by 3 desc, 1, 2

On Fri, 22 Aug 2003 11:23:58 -0400, Kristofer Andersson wrote:
Almost one clause missing:



Art S. Kagel

"Art S. Kagel" <kagel (AT) bloomberg (DOT) net> wrote

Thanks.

What is TBLSpace and what are those entries with numeric names for
tabname (678_1234 etc)?

Kristofer

On Sun, 24 Aug 2003 17:06:42 -0400, Kristofer Andersson wrote:

The entries with 'tablename' "TBLSpace" are the Tablespace entries or
partition headers for the tables, detached indexes, and fragments contained
in each dbspace. In the latest version of the script that I posted the
'-t' option filters out these entries, only listing real partitions (see
the '-h' option for usage). As to the numeric 'tablenames' I'd guess
these are the partitions for detached indexes created by constraints and
actually have a leading space character that is hard to see in the
report.

Art S. Kagel

How would you know?
I ran this script on a customer's system and got values from 1 to 150.
The largest values came from tables with largest numbers of inserts, not the
largest number of reads.

How else would one use the output if this script for tuning?

----- Original Message -----
From: "Kristofer Andersson" <anderssonk75 (AT) hotmail (DOT) com>
To: <informix-list (AT) iiug (DOT) org>
Sent: Friday, August 22, 2003 10:18 AM
Subject: Re: What's in my buffers script


sending to informix-list