Re: SYSMASTER database and extents
"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:
>
> 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
Do you mean sysmaster, or your own database? In general this is not a
problem as the system tables are cached. But the real threat with
PeopleSoft, Baan, etc is reaching the extent limit. This is usually
about 200. The only solution is to set the next extent size after the
database is created, but BEFORE any tables are created. If you really do
mean sysmaster, then this ight present more of a challenge. :-)
> 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
I don't think the number of extents in the system tables is going to
affect this query.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock http://www.informix.com |//////// /|
| mailto:mdstock@mydas.freeserve.co.uk |///// / //|
| http://www.iiug.org +-----------------------------------+//// / ///|
| |What year 2000 bug? year 2000 bug? |/// / ////|
| |year 2000 bug? year 2000 bug? year |// / /////|
| |2000 bug? year 2000 bug? year 1900 |/ ////////|
+----------------------+-----------------------------------+-----------+