Re: Executing stored procedures - performance problem

Oleg V. Besedin wrote: > > recently we found a kind of interesting behaviour of our Informix system. > Stored procedures which normally work within 2 - 3 minutes started > to take 8 - 9 hours to complete (180 times performance drop - ha-ha). We recently had a drastic performance problem related to SPL. We recently installed a substantial schema change which included dropping and recreating every tyigger and stored procedure. One subtle thing about stored procedures that we learned was that at compile-time, they inherit the current environment setting for PDQPRIORITY. Well, the operator's environment during the drop/add phase had PDQPRIORITY set to 85. This can be a nasty problem to diagnose, since SPL statements do NOT show up in an sqexplain.out, nor can they be monitored within onstat -g ses xxxx. There is also no way to determine what PDQPRIORITY is associated with a given stored procedure. We wound up dropping and recreating the procedures again, this time guaranteeing that the environment did not have PDQPRIORITY set at all. We also reconfigured the engine (temporarily) to have a MAXPDQPRIORITY of 0. That helped correct the performance immediately. When an SQL statement is executed that the optimizer determines is best done via PDQ, the engine will queue up the SQL statement in a PDQ queue, where it will wait for a calculated amount of resource availability (based on PDQPRIORITY, MAXPDQPRIORITY, etc.). Until the resources become available, the session appears to do nothing. If the calculated amount is high, the session can wait a very long time (hours??) before it even starts processing. This queue wait can be detected via onstat -g mgm IF the SQL belong directly to a USER session. If the SQL statement in queue belongs to a stored procedure, you do not see it at all. Try this: Change the engine's MAXPDQPRIORITY to zero (onmode -D 0), and execute the procedure to see if PDQ is the culprit. If it is, do one (or more) of the following: - Cut back the engine's MAXPDQPRIORITY in onconfig - Explicity set the PDQPRIORITY within the SPL and drop/create - Set PDQPRIORITY to the desired value, and run UPDATE STATISTICS FOR PROCEDURE xxx - Call Informix, and tell them it is a serious flaw not being able to determine PDQ scheduling information for executing stored procedures. Hope this helps... Paul Kapp ----> PLKapp@aol.com -OR- plk@interactive.visa.com