JDBC Connection pool tuning

[Latifa]

Hi Erman,

Customers reached the limit (200VU) with pool problems and threads.

Any advice to configure the JVMs ?

Actuelly we have :
• 2 JVM oacore à 2Go (oacore_server2 and oacore_server21)
• 2 JVM forms à 2Go (forms_server2 et forms_server21)

Each JVM of 2Go supports between 150 and 180 users. So in this state we have passed the 200VU.

Regards,
Latifa

[ErmanArslansOracleBlog]

Hi,

The recommended values for JVMs(manged servers) heap sizes are roughly 512 MB

for 50 EBS users and 2048 MB for 200 EBS users.

So when we have more than 200 users, we should add more weblogic managed

servers for the service group (for ex: oacore) that is affected from the load. Likewise,

if our server can’t handle the load , we should add more application servers to make

our EBS system fullfill the needs of heavy application processing.

[Latifa]

Hi Erman,

Thank you for your reply.

How and where should I check and configure all these informations ? Is there any procedure to follow ?

regards,
Latifa

[Latifa]

Customer is doing limits tests of the infrastructure. He says that:

Tir to limits (500VU max) => Limite de l'infrastructure reached at 200VU with problems of connection pool de connexion and thread.

If I understand well that servers ressources are not welle used so not well configured ?!  

[ErmanArslansOracleBlog]

I don't know what the word "Tır" means . It is french maybe :)
Anyways, what I understand is you have a connection pool limit and you are reaching to that.

[Latifa]

Sorry I forgot to translate. It means test :)

[ErmanArslansOracleBlog]

Hi Latifa,

check this -> Oracle E-Business Suite 12.2 Data Source Connection Pool Diagnostics (Doc ID 1940996.1)

[ErmanArslansOracleBlog]

Check your connection pool limit using weblogic console (increase it if necessary) and also ensure there is not problematic configuration that may stop you while creating connections. (such as processes and session counts, such as OS ulimits)

[Latifa]

I learned previously how to connect to weblogic console with your help :).

checking weblogic console for pool connection gives

initiale Capacity :   5
 
maximale: Capacity   500
 
minimale Capacity :  5

Which value should be modified ?

How to check this:
" there is not problematic configuration that may stop you while creating connections. (such as processes and session counts, such as OS ulimits) "


regards,
Latifa

[ErmanArslansOracleBlog]

okay, your connection pool is good.
But if you reach 500 connection, it will stop you.
Maybe , that guy was talking about this? :)

For db side, you can check using sqlplus;

sqlplus "/as sysdba"
show parameter processes
show parameter sessions

also, for Os side (I assume it is Linux) , you can use ulimit -a to check

[ErmanArslansOracleBlog]

If needed, maximale should be increased..

[Latifa]

I increased maximal capacity to 1000 and minimal capacity to 10.

But I should add JVMs I think ? How to do that ?

[ErmanArslansOracleBlog]

JVM means managed server in EBS apps tier, Latifa.

As I said before, when we have more than 200 users, we should add more weblogic managed servers for the service group (for ex: oacore) that is affected from the load.

to add a managed server, you can follow ->
Managing Configuration of Oracle HTTP Server and Web Application Services in Oracle E-Business Suite Release 12.2 (Doc ID 1905593.1)
4.1 Adding a new managed server

[Latifa]

Thank you Erman. I'll apply this note to add JVMs

[Latifa]

Hi Erman,

I added managed servers (2 oacores and 2 forms). However, customer observed some Apache repartition problem and physical access to the DB (actually we have :
sga_max_size                         big integer 14G
sga_target                           big integer 14G

Customer guess that the DB pool is the root cause ?!

Please see the attached file .

Regards

[ErmanArslansOracleBlog]

Did you increse the connection pool max connections as I said you before?
Did you check the db tier and ensure it is not reaching the processes/sessions limit?

-you can use v$resource_limit to check the db tier. (you can see the max utilization there)
-you can use show parameter processes and session to check the processes and sessions max values
-you can use weblogic console to increasea the max connection of the related connection pool

[ErmanArslansOracleBlog]

Bytheway, I analyzed the error message that you sent to me via email.
It is completely datasource related.
Do the things that I recommended in my previous update.

Important note for your next upload:

In order to upload a file here ->
You should first create an account .
With your account logged in, click reply. While you are replying you should click on "More>Upload a file" to upload a file.