SYSMASTER database and extents

Hi Everyone, I'm running IDS 7.31.UC2, HP-UX 10.20 with PeopleSoft Financials. I know that too many extents on data tables is not good. Is the same true with the sysmaster tables? I have the following sysmaster tables with more than 8 extents: syscolumns 64, sysobjstate 71, sysconstraints 73, syscoldepend 54, sysviews 40, sysdistrib 17, sysdepend 15, systables 35, systabauth 20, sysindexes 27, sysfragments 40, and syscolauth 13 The reason I ask is because I have the following script that I run to tell me "real fast" what is locked: dbaccess sysmaster - << ! 2>/dev/null output to pipe 'grep -v ^$' select d.sid as owner_sid, d.username as lock_owner, hex(b.partnum) as partnum, hex(b.rowidr) as rowid, a.sid as waiter_sid, a.username as waiter from sysrstcb a, syslcktab b, systxptab c, sysrstcb d where a.lkwait=b.address and b.owner=c.address and c.owner=d.address order by 1,2,3,4 This scripts has always run in a matter of seconds. During the last few days it takes, somtimes, over 10 minutes to run. The explain looks like: Estimated Cost: 376 Estimated # of Rows Returned: 100 1) informix.a: SEQUENTIAL SCAN 2) informix.b: INDEX PATH (1) Index Keys: address Lower Index Filter: informix.b.address = informix.a.lkwait 3) informix.c: SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: informix.b.owner = informix.c.address 4) informix.d: INDEX PATH (1) Index Keys: address Lower Index Filter: informix.d.address = informix.c.owner Jerry Hamilton Fleishman-Hillard

"Hamilton, Jerry" wrote: > > Hi Everyone, > > I'm running IDS 7.31.UC2, HP-UX 10.20 with PeopleSoft Financials. > > I know that too many extents on data tables is not good. Is the same true > with the sysmaster tables? I have the following sysmaster tables with more > than 8 extents: This is two different questions. The tables listed below are all system catalog tables which are cached in the data dictionary cache (onstat -g dic) so the number of extents for these tables is irrelevant (indeed Vers 7.31 classifies buffers from tabid's <99 as LOW priority so they do not even remain in the buffer cache long). If you have a large number of databases and/or tables consider increasing the size of the dictionary cache (see onstat -g dic to know if the cache is relatively full). > syscolumns 64, sysobjstate 71, sysconstraints 73, syscoldepend 54, > sysviews 40, > sysdistrib 17, sysdepend 15, systables 35, systabauth 20, sysindexes > 27, sysfragments 40, > and syscolauth 13 > The reason I ask is because I have the following script that I run to tell > me "real fast" what is locked: > > dbaccess sysmaster - << ! 2>/dev/null > output to pipe 'grep -v ^$' > select d.sid as owner_sid, > d.username as lock_owner, > hex(b.partnum) as partnum, > hex(b.rowidr) as rowid, > a.sid as waiter_sid, > a.username as waiter > from sysrstcb a, syslcktab b, systxptab c, sysrstcb d > where a.lkwait=b.address > and b.owner=c.address > and c.owner=d.address > order by 1,2,3,4[SNIP] Now this is a different question altogether, but the answer is the same. These are the actual tables from sysmaster which are not REAL tables at all but pointers to shared memory data structures or disk structures with an SQL interface wrapped around them. (Look at the buildsmi script and sysmaster.sql someday and see how that little trick is accomplished, interesting stuff.) Since they are not real tables they cannot fragment and the whole concept of extents is non-sequitor. As to why this query is suddenly much slower... I dunno, but it's not too many extents. Art S. Kagel