To occupy a few minds tonight :-)

I've been investigating some highish sequential scan figures (1.4
million in 8 hours). I know they are not bad in themselves but just
wanted to see if there were any large tables being scanned. I
successfully identified a couple of larger tables (250 rows x 300
chrs) that were usefully indexed and am only left with 15 or so tables
that are being scanned but that have < 10 rows each, so far so good.
One of these tables is giving pause for thought however. The sql I am
using is :-
DATABASE sysmaster;
SELECT a.dbsname[1,10] AS database,
a.tabname[1,18] AS table,
b.npused,
a.bufreads,
a.bufwrites,
a.seqscans,
a.pagreads,
a.pagwrites
FROM sysptprof a, sysptnhdr b
WHERE a.partnum = b.partnum
AND a.seqscans > 4000
ORDER BY a.seqscans DESC

which produces (amonst others):-
database table npused bufreads bufwrites
seqscans pagreads pagwrites
---------- ------------------ ------------ ------------
------------ ------------ ------------ ------------
sysmaster sysusers 3 29400007 0
7133 0 0

The number of scans is not concerning (on a par with the rest and
1/20th the highest) however the bufreads is a standout figure being a
factor of 20 higher than the next nearest and a factor of 200 higher
than most other scanned tables.
I know the sysmaster tables are not real and this mayall be a red
herring, but is there any potential issue here??

AIX 5.3 running IBM Informix Dynamic Server Version 9.40.FC6 (in the
process of upgrading to 11).

Many thanks

Keith

On 8 June, 18:39, Keith Simmons <smile... (AT) googlemail (DOT) com> wrote:
Well http://www-01.ibm.com/support/docvie...id=swg1IC59896
mentions

IC59896: PERFORMANCE SLOWDOWN WHEN MANY NON-DBA USERS EXECUTE A STORED
PROCEDURE

The problem is fixed in IDS 11.50.xC4. Users should no longer
see the contention on the mutex as the necessary
information is read from cache instead of accessing the table
every time.

After 11.50 is installed I would cehk the size of the dictionary cache
with onstat -g dic.