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

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

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