9 Replies Latest reply: Aug 5, 2014 10:48 AM by jgarry RSS

    Inactive sessions causing high memory and swap utilization

    XerXi

      Hello.

       

      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.

        • 1. Re: Inactive sessions causing high memory and swap utilization
          sol.beach

          XerXi wrote:

           

          Hello.

           

          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.

          • 2. Re: Inactive sessions causing high memory and swap utilization
            XerXi

            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?

            • 3. Re: Inactive sessions causing high memory and swap utilization
              Mark D Powell

              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.

              • 4. Re: Inactive sessions causing high memory and swap utilization
                sol.beach

                XerXi wrote:

                 

                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.

                • 5. Re: Inactive sessions causing high memory and swap utilization
                  jgarry

                  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.

                  • 6. Re: Inactive sessions causing high memory and swap utilization
                    JustinCave

                    What is the query you are using to see this memory utilization?  I'm not disputing that you are experiencing a problem.  But the query that shows the issue would dramatically help narrow down the source of the problem.

                     

                    Justin

                    • 7. Re: Inactive sessions causing high memory and swap utilization
                      XerXi

                      I use prstat and swap commands where I see 97% usage of memory and swap. Furthermore all databases on the server hang and in the alert logs are messages like "out of process memory". Sessions in 2 of the dbs:

                      sessions1 - Pastebin.com

                      sessions2 - Pastebin.com

                      • 8. Re: Inactive sessions causing high memory and swap utilization
                        sol.beach

                        how do you know or determine which instance consumed  all the RAM & SWAP?

                        • 9. Re: Inactive sessions causing high memory and swap utilization
                          jgarry

                          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.