Re: Sysprocplan locking and Stored Procs Recompiling

In article <01bd2d04$ec1d3c60$8e110280@gzeal.nigas.com>, "Gary Zeal" <garyz@zealinc.com> 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 Gary, we had exactly this problem, and found a workaround fix..... The server appears to keep track of whether each procedure needs to be recompiled or not. If the procedure is updated/reloaded, abviously this flag is set. BUT changing any of the tables to which the proc refers can ALSO set this status too. Also, the database does not seem to reliably handle 'nested' proc calls When a proc needs compiling, the server appears to 'force' a transaction state (even if this has not been selected by the user). SOLUTION: (I though I'd keep you waiting till the end !!!) UPDATE STATISTICS FOR TABLE ; (do this first, the above does all your tables) then - UPDATE STATISTICS FOR PROCEDURE <name1>; UPDATE STATISTICS FOR PROCEDURE <name2>; If you do the procedures all together the system tends to run out of locks (ours ALWAYS does). This will stop the locks happening !! (It forces recompile of the procedures.) If you have 'scripts' or files for the procs (we have each proc in its own file of the same name for source control) add UPDATE STATISTICS FOR ...... at the end of the procedure declaration, then each proc is compiled after loading automatically Hope this helps, Andy. (DBA at Ministry of Agriculture, Wellington, New Zealand) -------------------==== Posted via Deja News ====----------------------- http://www.dejanews.com/ Search, Read, Post to Usenet