Connection Pool

This document is about a performance improvement by using connection pool option.Connection pool is an option in Pro*C/C++ programming. Through this option, the application response time could be saved.

 

When a program sends a request to an Oracle database, an open connection between the application and the database is required. The open connection, named a round trip, consumes server resources, like CPU and memory. Oracle Pro*C/C++ CPOOL option provides a way to reduce server resources consumptions on making the round trip.

 

Below test data is based on 100 connections between the application and the database.When CPOOL option is not in use, the requests require 100 open connections. However, when CPOOL option is used, it is not necessarily to create 100 connections, which potentially to save server resources and shorten the runtime response.

 

A program is built for the testing. The elements of the program would be as follows:

1. Multi-threads application

2. Thread function may be an output to the screen, inserting, or updating data, etc.

3. Time duration

4. Join-able thread,which resources can be recycled after the thread is over.

5. Stack limit may be checked.

6. Mutex may not be considered to keep the application simple as much as possible.

 

Option #1: cpool=yescmin=32 cmax=70 cincr=3

Option #2: cpool=yescmin=35 cmax=76 cincr=3

Option #3: cpool=no

 

Times Option #1     Option#3

1         3.168836     6.258422 

2         4.15932       4.272355 

3         3.164903     4.176438

4         4.167968     4.231537

5         3.16096       4.215119 

6         6.199934     4.224345

7         3.17522        4.214227 

8         4.163007     4.196586

9         3.164319     4.189533

10        4.181868    4.227321

avg     3.8706335    4.4205883 12.44%

 

Times    Option#2 Option#3

1.            3.282291 4.345065 

2.            3.272687 4.180738 

3.            3.289279 4.247147 

4.            3.275381 4.277969 

5.            3.281474 4.25995 

6.            3.287688 4.243699 

7.            3.279833 4.227971 

8.            6.275289 4.208495 

9.            3.267163 4.202406 

10            4.279275 4.28394 

avg         3.679036 4.247738 13.39%

 

Regarding to using CPOOL option, above two options are provided. Each option tests 10 times.The runtime unit is second. Comparing with the option#1 and the option #3,the application response time saves 12.44%; the option #2 and the option #3 response time saves 13.39%. Therefore, from above test results, using connection option does save server resources and application response time.

 

Testing platform:

Oracle database 18c

RedHat 7

2 CPU 4G RAM