We have an Oracle Database Enterprise Edition 11.2 running on Solaris SPARC 64-bit OS. We are facing issues from time to time cause by inactive sessions in the database that are consuming large amounts of memory and swap (even exhausting it). This issue is fixed by killing them but eventually this builds up. How come when a session is inactive (and the LAST_CALL value is big) it uses so much resources? It seems it builds up by the past queries that are run through the connection.
Don't ask here.
Ask the folks who designed & wrote the application code.
>This issue is fixed by killing them but eventually this builds up.
This only permanent fix is to modify the application code.
XerXi, Sol has a point. Normally an inactive session only causes problems if it is holding a lock an active session wants or you have so many inactive sessions that you approach your process limitation. The fact that your inactive session are holding significant memory resources goes directly back to the application coding. Is connection pooling in use? If a lot of these sessions are part of a connection pool then modification of the connection pool setting to more aggressively clean up unused sessions may be your best short-term solution. You would still have the question as to what the application is doing that consumes so much memory, but if you need immediate relief this is worth looking into if a lot of these sessions are in fact connection pooled.
HTH -- Mark D Powell --
OS - One source of potentially heavy memory use by a session is the use of pl/sql collections to hold data.
So I thought, but is this true: "Resource usage builds up query after query are run through a session even after they are executed"? If not, what is the reason for such a build-up?
the reason for the "build-up" is due to flawed application design or implementation.
If the application code never releases resources or never closes cursors, the database can not be blamed.
Oracle database only reacts to what the client directs it to do.
Go talk to the application developers & support folks.
We can not fix this for you.
Sol.Beach and Mark are correct, but there may be something you can do. For example, it could be that there are settings in your client software that tell it to reuse existing connections. How old are these connections? If they are, for example, from people forgetting to sign off when they leave, and you can identify which processes are not supposed to be running at particular times, you can simply kill them off from the OS with a script in the middle of the night. If you do such a thing, of course you should record which processes before killing. This may also give you a list of people or business processes to modify.
So it's ok for sessions to be logged on for a month? If so, you need to seriously decrease memory usage for all instances.
I think it is more likely you should find out why they are logged on for so long, figure out what should be a reasonable maximum time, and lay waste to the galaxy. Or at least see if there is a timeout available.