1 2 Previous Next 19 Replies Latest reply: Apr 5, 2013 6:57 PM by JustinCave Go to original post RSS
      • 15. Re: query for Inactive sessions consuming memory
        Pointer
        The nagging question for me is why make it so hard? If you set an IDLE_TIME in a profile and set resource_limit to TRUE most of the work is done for you.
        Setting IDLE_TIME in a profile and assigning it to a user is one of the best practise.
        Our applicaiton works on JDBC connection pooling and setting an IDLE_TIME has a negative impact on the application performance and behaviour.
        • 16. Re: query for Inactive sessions consuming memory
          sb92075
          Pointer wrote:
          Our applicaiton works on JDBC connection pooling
          If the application was fixed such that it did not leave idle session dangling for days, then nothing else needs to be done.

          Root cause & real solution are within the application & NOTHING should be done at the database level!
          • 17. Re: query for Inactive sessions consuming memory
            Pointer
            What memory, exactly, are you concerned about?
            I'm concerned about PGA memory which might not be released back to OS.
            If the connection is not dead and the client is still around and expecting the session to be there, the fact that it is inactive isn't a big deal. It's not uncommon, for example, for a middle tier application server to open a >>bunch of connections for a connection pool that is sized for the high end of the application's usage patterns. Connections in those pools can easily be inactive for a few day. Killing those sessions, though, will often >>create errors for the application (many applications don't handle a connection being killed gracefully) or will create far more performance problems by forcing the application to open a physical connection while a human >>is waiting for the results and by creating a connection storm on the database when the application realizes that dozens of its connections are bad.
            Thanks for such detailed explanation, our middletier application uses connection pooling to connect with the database (dedicated server sessions) with tcp/ip calls from an ESB layer which communicates using API's and PL/SQLs.Ideally the inactive sessions should be closed once the transaction is completed but it seems it's not happening as we are using JDBC14 10g drivers while our Oracle database is 11gR2.

            Regards,
            ~Pointer
            • 18. Re: query for Inactive sessions consuming memory
              Pointer
              If the application was fixed such that it did not leave idle session dangling for days, then nothing else needs to be done.
              Root cause & real solution are within the application & NOTHING should be done at the database level!
              Very true, Yes, I agree, however being a DBA i should report these issues to the developers by identifying the list of INACTIVE sessions consuming memory and causing the PGA memory to grow bigger.
              • 19. Re: query for Inactive sessions consuming memory
                JustinCave
                Pointer wrote:
                What memory, exactly, are you concerned about?
                I'm concerned about PGA memory which might not be released back to OS.
                Unless you have done something truly unusual, an inactive session isn't going to hold any more memory than is required for the existance of the session itself.
                .Ideally the inactive sessions should be closed once the transaction is completed but it seems it's not happening as we are using JDBC14 10g drivers while our Oracle database is 11gR2.
                Why is that ideal? The whole point of a connection pool is that the middle tier has a pool of connections to the database that are generally inactive. When the application needs a connection, it gets it from the pool. When the application finishes with the connection, it is released back to the pool. Opening and closing connections is expensive and creates performance issues on both the middle tier and on the database server which is why the application server generally incurs that cost once and keeps the connection open for many days. Closing a physical connection after every transaction would defeat the purpose of a connection pool and would likely lead to a bunch of performance related issues.

                Your middle tier application server may have an option to recycle connections in the connection pool after some number of uses. Some may have an option to recycle connections that have been open for a lengthy period of time. If you believe that the middle tier connection pool is keeping too many connections in the pool, you can talk with the application team about reducing the size of the connection pool.

                In the vast majority of situations, though, inactive connections are not something that you should be worrying about.

                Justin
                1 2 Previous Next