3

If I have an Oracle Autonomous Database in an exadata vm with 1 OCPU, how should I configure my jdbc connection pool? How many connections should I specify? What is the maximum amount?

Pika Supports Ukraine
  • 3,612
  • 10
  • 26
  • 42
David Fdez.
  • 168
  • 1
  • 2
  • 13
  • Unless you do not specify your requirements, you cannot expect to get useful answers. – U. Windl Apr 08 '19 at 19:42
  • 1
    When you have physical hardware there's an easy formula to try to find the best pool size for performance (see https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing). But in this case, when you create an autonomous database from control panel, you don't know nothing about hardware, cores, threads, etc. and java web applications must use connection pools to connect to. – David Fdez. Apr 09 '19 at 07:09
  • 1
    Here is a wild guess: start with 5 and see how it goes. If you don't have enough connections in the pool check if the reasons are valid. Are the connections released to the pool as soon as possible? etc. If you must increase the max size increase it little by little. – Jean de Lavarene Apr 09 '19 at 14:43

2 Answers2

3

The link David Fdez provided above is correct.

As a rule of thumb for maximum performance the maximum number of connections to an Oracle database should be a small number times the number of hardware threads the server has. This is the total across all clients. See https://www.youtube.com/watch?v=Oo-tBpVewP4.

This is not always practical. If there are hundreds of clients connecting to a small server, even at one connection per client that limit is exceeded. That's ok. The database will still work just fine but it will not achieve the maximum performance it is capable of; it will be spending more time context switching than is ideal. The rule of thumb is still useful. It tells us that each client should only have one connection, not 20.

The objection is that with only one connection the client requests will have to wait for a connection. That is true but if there were 20 connections the clients would not be waiting for a connection. Instead they would be waiting even longer while the server was context switching between the huge number of database processes. The overall performance of the system will be much worse with 20 connections per client rather than 1.

Douglas Surber
  • 622
  • 4
  • 9
1

With just 1 OCPU you should be able to utilize at least 100 sessions (subject to use of appropriate service and parallelism, etc.). Please also see, Use Database Resident Connection Pooling with Autonomous Database

RaviSharma-Oracle
  • 171
  • 1
  • 5