Our IDS 9.21 system has developed some performance problems lately. It used to
handle the load just fine, but it's really been chewing up the CPU and disks
lately. It's a transactional system mostly reading/updating single rows at a
time. My stats show a non-trivial number of sequential scans, but I know that
many of those are on small tables which should always be in memory. But maybe
there are some not-so-trivial scans happening.

I've been using Art Kagel's performance ratios: Buffer Waits, Buffer Turnover,
Read-Ahead Utilization. My RAU is over 99%, my BWR is less than 1%, but the BTR
just seems to keep climbing as time goes by, even though the nature of the DB
activity is pretty much constant throughout the day. It can get near 100 after a
week.

We run the same apps at multiple locations, each with their own Informix server.
I am only having trouble at one location. The BTR gets rather high at all
locations, but only one of them is taxing the CPU.

Art said about BTR in a prior post:

For my onstat -P the 'other' value is in the hundreds. I've already increased my
buffers, but since my bufwait ratio is low, I've probably got enough. He says to
"look at the partnums for particular tables", but the partnums listed by onstat
-P are 'partition' numbers, not table partnums. I don't know where to look up
these 'partition' numbers.

How can I identify which tables are filling up the buffers?

Thanks,

--
Jeff
jlar310 at yahoo

oncheck -pT <thefoundnumber>

or
dbaccess sysmaster <<!
select * from systabnames
where partnum = <your number>
!

Superboer.

BTW looking for seqscans
if in your onconfig:

TBLSPACE_STATS 1

dbaccess sysmaster <<!
select * from sysptprof where
seqscans > <somenumber you make up>
!