10 Replies Latest reply on Aug 1, 2016 9:09 AM by Beauty_and_dBest

    JDBC Connection Leaks & Locks

    Beauty_and_dBest

      Hi ALL,

       

      EBS R12.1.3

      OL5

      11gR1

       

      How to you identify concurrent programs which are in "run-away" or "abnormal" status, causing memory leaks and locks?

       

      We often encountered JDBC Connection Leaks & Locks.

      The main cause of the issue seem to be a custom program that is not written properly or have encountered unhandled exceptions, hence cuasing memory leaks and locks?

      This cause the users not able to login to the application login page.

       

       

      This blog  have similar issues> Mukul's Oracle Technology Blog: JDBC Connections leakage and optimization in Oracle Apps R12.

      I do not get much what is the final recommended solution?

      Did it say we have to revise the existing custom program?

       

       

      Please help...

       

      Kind regards,

      jc

        • 1. Re: JDBC Connection Leaks & Locks
          Pravin Takpire

          Few things that you can implement

          1.) Run a cronjob/monitor server resources like CPU/Memory/LOAD AVG etc, which will send you alerts.

          2.) Also monitor frmweb and other processes causing 100% CPU usages

          3.) Start monitoring execution of Custom Programs ( long running one )

          4.) also check for Inactive Sessions in database.

          5.) Also monitor how many of them are forms sessions, JDBC sessions and monitor them, kill/clear them when ever necessary.

           

          JDBC sessions mostly comes from login sessions and not from Concurrent Requests. So also keep watch on settings of session timeout parameters.

           

          Scripts for all above requirements you can get it from internet, just google it.

           

          Keep in mind that these scripts should be able to send you alerts via email or sms etc and then you can take timely action. also you can set up nightly OACORE bounce.

           

          regards

          Pravin

          1 person found this helpful
          • 2. Re: JDBC Connection Leaks & Locks
            Beauty_and_dBest

            Thanks Pravin and ALL,

             

            How to you boince OACORE?

            Is there a cron script to schedule it at midnight?

            What issues does bouncing OACORE resolved?

             

            Does it clear all inactive sessions running more than two (2) days?

            I have monitor session using the OAM. And I found out lots of inactive sessions lingered for more the two days or even more that a week?

            Capture3.GIF

            How do you clear them up?

             

            Kind regards,

            • 3. Re: JDBC Connection Leaks & Locks
              Pravin Takpire

              OACORE is nothing but apache and other application services. You can bounce them from scripts in $ADMIN_SCRIPTS_HOME.

               

              all these session with e:fwk:sso are login sessions. Change the settings for session time out parameters.

              regards

              Pravin

              1 person found this helpful
              • 4. Re: JDBC Connection Leaks & Locks
                happy10319

                Pravin,

                How to :

                Run a cronjob/monitor server resources like CPU/Memory/LOAD AVG etc, which will send you alerts.

                 

                Thank you.

                • 5. Re: JDBC Connection Leaks & Locks
                  Beauty_and_dBest

                  Thanks Pravin and ALL,

                   

                  RE: all these session with e:fwk:sso are login sessions. Change the settings for session time out parameters.


                  Does it mean the users did not logout for days or weeks? or this is a runaway zombie process? Can I kill these processes/sessions?



                  Kind regards,

                  • 6. Re: JDBC Connection Leaks & Locks
                    Pravin Takpire

                    Yes many users do that. they don't logout, just close the browser or let laptop go to sleep/lock. You can safely kill them. also if you set up correct time out parameters sessions automatically clear or you cron job to clear them

                    regards

                    Pravin

                    • 7. Re: JDBC Connection Leaks & Locks
                      Beauty_and_dBest

                      Thanks Pravin and ALL,

                       

                      Does it degrades performance if the session is not logged off? Or it will be just okay becuase it will be reused the next time the users logged?

                      Does it accumulates

                       

                      Afaik we have set up correct time out parameters sessions automatically, but it did not work

                      I guess it is setup by default right?

                       

                      1. Can you give me the correct way of setting it? Maybe we have  missed some steps.

                      2. Also can you the the other way using a cron to kill it? Can you share a script to kill sessions logged-in  more than 2 days?

                       

                       

                      Kind regards,

                      • 8. Re: JDBC Connection Leaks & Locks
                        Pravin Takpire

                        Yes unnecessary inactive sessions cause problem with database. So better clear inactive sessions.

                        I cant give you scripts as whatever scripts I have are proprietary type, but you can search on internet you may get it.

                        For sessions time out parameters give me current values.

                        regards

                        Pravin

                        • 9. Re: JDBC Connection Leaks & Locks
                          Beauty_and_dBest

                          Hi Pravin,

                           

                          I am not sure if the settings are correct.

                          Are you referring to the following?

                          Capture.GIF

                           

                          Set E-Business Suite Timeout Parameters and Profiles

                          • ICX Timeout Profile Values
                          The following E-Business Suite profile options control screen timeouts for Forms, as well as Self Service sessions.  Again, please note, some of the ICX profiles also control Forms Session timeouts!  This can be confusing since Inter-Cartridge Exchange (ICX) is often associated with Self Service applications. This is no longer the case since the release of Framework for the ICX Profiles control the timeout functionality.

                           

                          Parameter

                          Default

                          Recommendation

                          ICX:Session Timeout

                          None

                          30 (minutes)

                          ICX: Limit Time

                          4 (hours)

                          4 (hours)

                          ICX: Limit Connect

                          1000

                          2000

                          ·         ICX:Session Timeout - This profile option determines the length of time (in minutes) of inactivity in a user's form session before the session is disabled.  Note that disabled does not mean terminated or killed.  The user is provided the opportunity to re-authenticate and re-enable their timed-out session. If the re-authentication is successful, the disabled session is re-enabled and no work is lost. Otherwise, the session is terminated without saving pending work.  This functionality is available via Patch 2012308 (included in 11.5.7, FND.E).  Note: Setting the profile value to greater than 30 minutes can drain the JVM resources and cause ‘out of memory’ errors.

                          ·         ICX: Limit time - This profile option defines the maximum connection time for a connection – regardless of user activity.  If 'ICX:Session Timeout' is set to NULL, then the session will last only as long as 'ICX: Limit Time', regardless of user activity.

                          ·         ICX: Limit connect - This profile option defines the maximum number of connection requests a user can make in a single session. Note that other EBS internal checks will generate connection requests during a user session, so it is not just user activity that can increment the count.

                           

                           

                           

                           

                          KInd regards,

                          • 10. Re: JDBC Connection Leaks & Locks
                            Beauty_and_dBest

                            Hi Pravin and ALL,

                             

                            I found this query in google:

                             

                            Sometimes all this session timeout mechanism doesn't work perfect, if you want to manually kill all Form sessions idle for 3 hours execute the following.

                            SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') || TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 
                            'SSSSS'), 'HH24:MI:SS') LOGON, v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process unix_appl, v$session.USERNAME, STATUS, OSUSER, MACHINE,
                            v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE, 'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;'
                            kill_sql FROM v$session, v$process WHERE v$session.paddr = v$process.addr AND v$session.status = 'INACTIVE' AND v$session.username = 'APPS' AND
                            v$session.last_call_et/3600 > 3 AND v$session.action LIKE 'FRM%' --AND v$session.module in ('INVTTMTX','INVTVQOH','INVTOTRX') ORDER BY logon_time ASC;



                            Is this okay? Are forms idle for 3 hours be justifiable to kill? or I change it to 24 hrs?


                            Kind regards,