Re: SYSPROCPLAN locked using Stored Procedures

davin_harvey@dsti.co.uk wrote: > > We had major problems with this a while back=2E I eventually > traced i= > t=20 > to the engine updating stats for a procedure when you execute the > proc= > =20 > if a table in that proc has changed=2E It's actually in a > manual=20 > somewhere=2E If the proc is executed in a transaction, then > exclusive= > =20 > locks are placed on all the sysprocplan rows for that proc=2E > Our=20 > solution was to update stats for procedures as a separate > routine=20 > AFTER the rest of the database has had its stats updated=2E > =20 > Davin This is exactly the right answer. Run UPDATE STATISTICS for the procedure before running. If a table changes, the plan will be updated when the procedure is run. You can now break up your UPDATE STATISTICS into several commands, and even run them in parallel. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+