how to cap sessions memory limit for STORED PROCEDURES/FUNCTION

Hi Friends, colleagues etc my customer is running 14.10 FC9 Workgroup Edition (max 24 Gb of SHMEM in total) (just upgraded for 14.10 FC5) on linux REd hat. He is intensively using stored procedures, and among those, some recursive ones. Fortunately, we identified some procedures that, according to data configuration, can grab 100s of megabytes of SHMVIRT, and somethimes hit against SHMTOTAL. Those episodes generally require to reboot the server because it remains in unstable state, sessions hang and no way to free SHMEM segments. Well I bet some of you have experienced this kind of situation. I have been doing tests scenarios using the SESSION_LIMIT_MEMORY parameter, but my conclusion is that this parameter is effectively when a query consumes memory for data set/sort etc, and apparently has no effect on capping the stored procedure memory pool. Can anyone confirm this behaviour, and above all give suggestions on how to restrain the memory consumed by a session, be it pure session stack or stored procedure. No need to tell me I should work on monitoring the stored procedures, I know what I have to do :-) and this task is scheduled for this week onsite. The real question here is that the only solution is to rebounce the instance to get out of trouble, and this is a real pain in the ... for my customer. Rebouncing sometimes twice a week really makes noise in the production factory unit... Any ideas/findings ? Thanks Eric ------------------------------ [eric] [Vercelletto] [] [Founder] [kandooerp.org] [Pont l'Abbé] [France] [+33 626 52 50 68] Disclaimer: My own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ------------------------------

Hi Eric, since UDR memory pools are session specific/owned, they should definitely fall under SESSION_LIMIT_MEMORY rule. I know of a defect, though, that could render this limit ineffective: IT42269: SESSIONS MIGHT SLIP THROUGH SESSION_LIMIT_MEMORY CHECK AND GROW FURTHER WITHOUT BEING TERMINATED (fixed from 14.10.xC10 onwards.) You'd see this in 'onstat -g ses <sid>' as e.g. Session Limits Limit Current Memory(KB) 10000 89878 So Current value significantly above Limit - which would indicate the memory counts, but the rule doesn't get enforced (due to that defect). An alternate explanation might be if the user running those stored procedures some qualifies as DBSA, so would be exempt from session limits; such user's sessions, in onstat -g ses 0|<sid>, would not have such Session Limits section. HTH, Andreas ------------------------------ Andreas Legner ------------------------------

Hi Andreas Interesting info. The question is that we just moved from FC5 to FC9, because customer doesn't like installing the very last fixpack in general. So I will proceed this way: Check who runs the UDR, if Informix or proc set as DBA, I will test with a 'normal' user Test with FC10 Will keep community updated BTW I have another customer who has exactly the same problem. Who be good finding a solution Thanks bunches BR Eric