RE: Sysprocplan

> -----Original Message----- > From: David Williams [SMTP:djw@smooth1.fsnet.co.uk] > Sent: Tuesday, May 06, 2003 7:00 PM > To: informix-list@iiug.org > Subject: Re: Sysprocplan > > > > > >>>to. Every time update statistics was run on the tables accessed by > the > > >>>procedures, users accessing the table with the trigger got a lock on > > >>>sysprocplan and held it until the transaction was committed. > > So update stats on all tables then update stats on all procedures after > the table. > Not that simple. I had an update stats for the procedure run immediately after update stats for the table accessed by the procedure. Problem is the table has about 10 million rows and the update stats high would take about 10 minutes. The table is flagged as having changed at the beginning of the update stats. So, long before the update stats would run for the procedure, some user would come along and trigger the procedure and the update stats for the procedure would be done in that users transaction. Add to that the fact that the table is indexed all to hell (10 indexes) and this causes quite a problem. SET OPTIMIZATION LOW was the only reasonable solution. Bill > How many procedures do you have? > Surely each one only takes a few seconds? > > There might even be a way to work out the procedures which depend on > each > table! >

Hi, I wonder why we are still discussing a behavior that is described in detail in chapter 1 of the Informix Training Manual "Stored Procedures and Triggers". It's funny that people run an "update statistics high" on large tables during peak times. If they know that "sysprocplan" is not the only table which will get a temporary exclusive lock during an update statistics ? I would suggest to run the "update statistics high" which takes 10 minutes and more either sooner or later. The tables' size must be something around: 600sec * 20MB/sec ~ 12GB A request addressed to Art. Could you please rewrite "dostat.ec" so that this wonderful tool will run only between 2am and 4am for tables that do not fit into the buffer pool ? Thanks a lot ;-)) Another good idea is to generate: SET PDQPRIORITY 1 and an implicit invocation of: "onmode -Q 1 and onmode -M 1000000" .... speeds up the "update statistics high" for all non 1st index columns. Best regards Stefan Bill Dare wrote: > > > >> -----Original Message----- >> From: David Williams [SMTP:djw@smooth1.fsnet.co.uk] >> Sent: Tuesday, May 06, 2003 7:00 PM >> To: informix-list@iiug.org >> Subject: Re: Sysprocplan >> >> >> >> > >>>to. Every time update statistics was run on the tables accessed by >> the >> > >>>procedures, users accessing the table with the trigger got a lock on >> > >>>sysprocplan and held it until the transaction was committed. >> >> So update stats on all tables then update stats on all procedures after >> the table. >> > Not that simple. I had an update stats for the procedure run > immediately after update stats for the table accessed by the procedure. > Problem is the table has about 10 million rows and the update stats high > would take about 10 minutes. The table is flagged as having changed at > the > beginning of the update stats. So, long before the update stats would run > for the procedure, some user would come along and trigger the procedure > and the update stats for the procedure would be done in that users > transaction. Add to that the fact that the table is indexed all to hell > (10 indexes) and > this causes quite a problem. SET OPTIMIZATION LOW was the only reasonable > solution. > > Bill > > >> How many procedures do you have? >> Surely each one only takes a few seconds? >> >> There might even be a way to work out the procedures which depend on >> each >> table! >>