too many statements in memory

As always, thanks in advance. I have a plague here made up with many aplications that use java and weblogic conexion pool that are not releasing results ending up consuming too much memory because of having lots of statements and creating V segments. onstat -g stm 4615984|wc -l8697 Did anybody find something like this? I'm not sure if they can configure a weblogic conection pool to do this or its just java code done wrong.

Hi, I would say you have to take a look in the java code which is probably not releasing the statements and/or resultsets. The connection pool cannot do that if the client code leaves statements open. This responsibility of the programmer to free everything after usage, unless it is a re-usable cursor (which is not uncommon, but the re-using should be done in this case and cannot lead to such a number of unclosed statements). In case you are using a tool like hibernate, there should not be any issue, because normally such tools do close for their internal statements when transaction is finished. Without some samples of code, I believe you cannot solve this only by looking in the database environment or the app server config. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "JACOBO BALBUENA" <jacobo.bc@gmail.com> An: ids@iiug.org Gesendet: Mittwoch, 13. April 2016 17:18:19 Betreff: too many statements in memory [36974] As always, thanks in advance. I have a plague here made up with many aplications that use java and weblogic conexion pool that are not releasing results ending up consuming too much memory because of having lots of statements and creating V segments. onstat -g stm 4615984|wc -l8697 Did anybody find something like this? I'm not sure if they can configure a weblogic conection pool to do this or its just java code done wrong. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks a lot, just wanted to know if there was any kind of way of telling the pool to not release statements because developers keep saying "I didnt change anything"

You should run something like: onstat -g stm SID | awk '{for (a=3;a<=NF;a++) {printf "%s ",$a};printf "\\ "}' | sort | uniq -c | sort -nk 1 If you see a lot of repetitions for a single statement (or a few) then the code is wrong and they have to change it. It really won't matter if they changed it or not.... Maybe the sessions are lasting longer now... This will consume resources on the database server and on the application server. This is a common situation in particular with Java applications. If the sessions are frequently refreshed it can go unnoticed for a while. I've seen some critical situations caused by this... one time a session had ~1GB (onstat -g ses | sort -nk 7 ). The engine was terribly slow... It took me 15m to killl the session. Once it finished the engine got back to normal. On other ocasion it grew up until it reached 200MB. Then the session was closed. Presumably because the application serverkilled the thread because it was also consuming too much memory on the application server side. This error is very easy to make when coding in Java.... sometimes a preparedStatement() ends up inside the loop.... on other situations the execution does not use prepared statements explicitly etc. Regards On Wed, Apr 13, 2016 at 4:45 PM, JACOBO BALBUENA <jacobo.bc@gmail.com> wrote: > Thanks a lot, just wanted to know if there was any kind of way of telling > the > pool to not release statements because developers keep saying "I didnt > change > anything" > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b2e1505785c3e053060629c

Yes, it is a very common java issue with connection pools. Just look for ifx_autofree and set it to 1, in your connection property or inside your jdbc string. Em 13/04/2016 12:19 PM, JACOBO BALBUENA <jacobo.bc@gmail.com> escreveu: As always, thanks in advance. I have a plague here made up with many aplications that use java and weblogic conexion pool that are not releasing results ending up consuming too much memory because of having lots of statements and creating V segments. onstat -g stm 4615984|wc -l8697 Did anybody find something like this? I'm not sure if they can configure a weblogic conection pool to do this or its just java code done wrong. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.