sysmaster:sysusers and sequential scans

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...@googlemail.com> wrote: > 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 Well http://www-01.ibm.com/support/docview.wss?uid=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.