0

We have 3 app servers behind load balancer and each app server is connected to Oracle database. We have setup connection pool of minimum 8 size and max 32 size. Everytime I query database to see active connections. I do not see more than 2 active connections per server . I know that during peek business hours ,we have more than 50 users accessing application ( and hence database). However why I do not see more than 2 connections active at database end. Question 1 ) How to find if jdbc connections on app server live/active at any moment. Question 2 ) How to find if few connection are lost and do not reach database.

Amit More
  • 1
  • How are you identifying "active" connections? Do you mean _any_ connections to the database, or only connections that show as "active" in the v$session view? "Lost" connections would have to be identified in the application (client) logs. – pmdba Sep 16 '21 at 15:45
  • I m using SELECT COUNT (*) , machine , status,OSUSER from V$SESSION group by machine, status,OSUSER ORDER BY machine, status; machine gives me each of app server name and count of active & inactive connections. – Amit More Sep 16 '21 at 16:47
  • "ACTIVE" from v$session only indicates whether a session is executing SQL code in the exact instant that you run your query. It is not an indicator of whether the connection is in use by the application. Most connections will show as "IDLE" most of the time as most SQL executes very quickly; the session spends a lot of time waiting for the next instruction from the application. The total number of application user sessions - not just the "ACTIVE" ones - should fall within the parameters of your connection pool. – pmdba Sep 16 '21 at 17:06
  • Can you elaborate more on last line "The total number of application user sessions - not just the "ACTIVE" ones - should fall within the parameters of your connection pool." – Amit More Sep 16 '21 at 17:58
  • The total number of active _and_ idle connections should fall between the min and max from your connection pool. – pmdba Sep 16 '21 at 18:37
  • Yes they are within min (8) and max (32) connection parameter. IF V$SESSION is not right approch Q 1 ) How to find if jdbc connections on app server live/active at any moment. Question 2 ) How to find if few connection are lost and do not reach database. – Amit More Sep 16 '21 at 20:13
  • Then I don't understand your question: the sessions exist in the database and are apparently processing requests. If you're not seeing more active at a time it could be because processing happens too fast for you to see it with a query of v$session, or because your pool is oversized. – pmdba Sep 16 '21 at 20:16
  • Sorry let me rephrase my question. How can I know if I m reaching max connections from app server side. I have seen from database using V@SESSION ,that max 5 connections are active .So I m unable to figure out if I m reaching max connection on app server side. This will be more towards app side and not database ,but how to find max live/active connection to database. – Amit More Sep 17 '21 at 14:07

0 Answers0