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! >