sysprocplan problems

\

We saw similar problems with locks on sysprocplan held by long running transactions that didn't even lock actual db records because the retrieval was Dirty Read. We helped the situation to some degree by updating table statistics (medium) each night and then followed that up with an update stats on the Stored Procedures. This reduced the problem, but Informix still will change the plan during the day. "Corey Gorman" <corey.gorman@cmgisolutions.com> wrote in message news:39E5E4DE.F59F954C@cmgisolutions.com... We have been experiencing problems with sysprocplan for some time with informix 7.31.UC2. The last patch was going to alleviate this problem. However I think another solution might work. If I read into the documentation correctly stored procedures can and will update there query plan if connecting tables change by some degree, thus updating the statistic on this procedure and locking the sysprocplan for a short duration. This locking cause congestion for us. My plan was to update all the procedures with optimization High, which I believe is default any way, and on the connection that we use have those set to optimization low. This I would believe to be a way of pinning a query plan, to what ever is in sysprocplan, and would not lock it. Has this strategy worked for anyone else, or am incorrect in my assumption of what is locking sysprocplan. Thanks again, your help is always grateful. Corey Gorman CMGI Solutions, Consultant Current Client : Columbia House