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 advance Hi Gary, You proberly have to rewrite your stored procs !!! Stored Procedures are re-optimised when the tables they depends on changes e.g. new indexes or columns. IF your SP creates an index or uses temp tables with primary key constraint (enforced with a unique index) THEN your SP are optimised every time they are executed. When a SP is optimised it gets an exclusive lock on the sysprocplan, so the result is only one invokation of this paticular SP is able to run at any given time. With many users this is a real killer. Good luck Niels Berner nberner@dk.dhl.com