Re: SYSPROCPLAN locked using Stored Procedures

I wanted to share the following personal e-mail I received from David Berg (dberg@informix.com) with Informix that has helped us clear up this problem. I tend to think that this is not a PB-related issue; although, I could be wrong. I had a similar experience with Version 5.01 back in 1992, but I thought that that issue was long-since resolved. I'll explain. Sysprocplan contains the optimized query plan for a procedure. That plan is updated at runtime if a structural change had been made to any database object in the query tree, or if UPDATE STATISTICS is run on any table in the query tree. Any time the plan is updated, locks must be placed on the respective rows in sysprocplan. Now, at the time I had the experience, two factors were at play: 1. I was creating or operating on temp tables in the SPL. This constitutes a structural change to a database object in the query tree; therefore, the query plan was updated at runtime. 2. The SPL I was executing was contained within the bounds of a transaction; therefore the updated rows of sysprocplan remained Xlocked until the transaction was committed. Any other users attempting to execute the same SPL were locked out. (Keep in mind that PB, at least in its earlier versions, is ANSI compliant; thus all database accesses are always contained in a transaction. I got around that by tricking PB into doing otherwise, but my SPL was still contained in my own transaction.) Now, at my behest, SPL was changed in a subsequent version (5.03?) so that query plan updates that were solely the result of temp tables were not written to sysprocplan. This alleviated much of my problems at the time. The other thing I always did was UPDATE STATISTICS FOR PROCEDURE every time I UPDATEd STATISTICS FOR TABLE. Thus, the query plans were always current at runtime. Now, I don't know if you're running into anything similar to the above. Why are query plans being updated at runtime? Is it because you haven't run U.S. for your procedures? Are you creating or operating on temp tables in your procedures? Are tables in the query tree being altered? Then, are your procedures being executed within the bounds of a transaction, thus holding the locks on sysprocplan until the transaction is committed? Another thing you can try is running the procedures with SET OPTIMIZATION LOW; this will tell the optimizer not to attempt reoptimizing the procedures at runtime, even if they would normally be reoptimized. Hope this helps. - David