Store procedures freezing on sysprocplan lock

Hi Family. Like Billy, I'm baaaack! In the new position I have gotten myself into, I notice that many apps overuse stored procedures i.e. huge utilities that should have been written in 4GL or ESQL. This, despite my preaching (when I was an instructor @Informix) that they would be complaining about poor performance. Yeah, I have met a couple of former students here. The big problem, until I can start weaning them from this SPL abuse, is that often, one monolithic SP calls many others who, in turn, call still more. Suddenly, the app that called the SP is frozen and the SP itself seems to be doing nothing. When someone looks at syslocks they find that the session has several locks on sysprocplan but that is hardly relevant. I noted this morning that the frozen session was the only session with ANY locks on sysprocplan. Of course, this is *not* repeatable on demand; a stored procedure call that has been working fine for weeks will suddenly freeze up as I have just described. HMmm.. I just realized that I have not checked if the apparently frozen session is *waiting* on any locks. I'll check into that next time it happens. Does this situation seem familiar to anyone? Why is the session putting locks on sysproplan anyway? How often does it have to reoptimize the procedure already? -- Jake (The signature should look kinda familiar .. ;-) +---- Jacob Salomon -- DBA JSalomon@bn.com ----------------------------+ |--------------- Obligatory sesquipedalian obfuscation: ---------------| | An object of igneous, sedimentary or metamorphic mineral in combined | | states of elevated linear and rotational kinetic energy acquires no | | accumulation of bryophytic vegetation | +----------------------------------------------------------------------+ --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---

Whenever you alter any of the tables it references... Ciao, Marco ______________________________________________________________________________ Marco Greco <marco@informix.com> Standard disclaimers apply! Informix faq http://www.iiug.org/techinfo/faq/informix.htm 4glworks http://www.ctonline.it/~marcog Informix on Linux http://www.ctonline.it/~marcog/ifmxlinux.htm Jacob Salomon wrote: > > Hi Family. > > Like Billy, I'm baaaack! > > In the new position I have gotten myself into, I notice that many apps > overuse stored procedures i.e. huge utilities that should have been > written in 4GL or ESQL. This, despite my preaching (when I was an > instructor @Informix) that they would be complaining about poor > performance. Yeah, I have met a couple of former students here. > > The big problem, until I can start weaning them from this SPL abuse, is > that often, one monolithic SP calls many others who, in turn, call > still more. Suddenly, the app that called the SP is frozen and the SP > itself seems to be doing nothing. When someone looks at syslocks they > find that the session has several locks on sysprocplan but that is > hardly relevant. I noted this morning that the frozen session was the > only session with ANY locks on sysprocplan. > > Of course, this is *not* repeatable on demand; a stored procedure call > that has been working fine for weeks will suddenly freeze up as I have > just described. > > HMmm.. I just realized that I have not checked if the apparently frozen > session is *waiting* on any locks. I'll check into that next time it > happens. > > Does this situation seem familiar to anyone? > > Why is the session putting locks on sysproplan anyway? How often does > it have to reoptimize the procedure already? > > -- Jake (The signature should look kinda familiar .. ;-) > +---- Jacob Salomon -- DBA JSalomon@bn.com ----------------------------+ > |--------------- Obligatory sesquipedalian obfuscation: ---------------| > | An object of igneous, sedimentary or metamorphic mineral in combined | > | states of elevated linear and rotational kinetic energy acquires no | > | accumulation of bryophytic vegetation | > +----------------------------------------------------------------------+ > > --== Sent via Deja.com http://www.deja.com/ ==-- > ---Share what you know. Learn what you don't.---