Re: Sysprocplan locking and Stored Procs Recompiling

Gary Zeal wrote: > > Running Informix 7 Online. > > Our application (written in SQLWindows 5.0.2) uses quite a few stored > procedures. Users are getting locked up and it seems to be due to locks > being held on the sysprocplan table. All tables have a "R"ow locklevel and > all views have a "B"ase locklevel. > > We run an update statistics on Sunday evening each week. > > When we query the "created" column in sysprocplan we find that many of the > values in this column show the current date which to me seems that the > procedures are getting either recompiled or a new execution plan during the > day. > > How do stored procedures get either recompiled or get a new execution plan > aside from a create or an update statistics? > > This problem is driving us nuts! Any help would be greatly appreciated > > Thanks in advanceThis problem is driving us nuts as well :) SQLWindows automatically performs a BEGIN WORK whether it is required or not. This means that if a procedure re-optmises at execution time the sysprocplan table is updated. The client application isn't aware that any update has occurred, and if the query doesn't perform any updating the app probably won't perform a COMMIT or ROLLBACK. This leaves locks on the sysprocplan until the next time the application does a COMMITs or ROLLBACK (possibly never for a read only application). The solution we have is not entirely successful but does help the problem and involves insuring that the procedures do not re-optmise at execution time. Ensure that all procedure statistics are updated immeadiately after the table statistics. This forces a re-optimisation. In addition we update procedures in an order that means a nested SP is updated before the calling SP. INFORMIX claim that this shouldn't make any difference but it did on our system! We now only get the problem occaisionally but when it happens its a nightmare to remedy. INFORMIX seem unable to explain why these procedures seem to suddenly re-optimise. As an aside we have contacted our SQLWindows Support to see if the automatic BEGIN WORK can be turned off. We haven't had a satisfactory answer as yet. Paul Tonkin DBA -- ------------------------------------------ The views expressed here are mine and not those of my employer, Britannic Assurance ------------------------------------------