Most applications use JDBC connection pools and as a result there are databases with hundreds of
connections: min pool size gets increased to 30 or 50, there is more than one pool, and there are
multiple application servers.
Question: how much OS memory gets used by these connections?
To find the answer I conducted a simple test:
OS: AIX 7.1
Oracle version: 11.2.0.4
I was trying to create large number of connections so there will be noticeable impact on memory.
To create a connection that sleep for a while and then exits I used script "sleep.sql"
begin
dbms_lock.sleep(&&1);
end;
/
exit
This was called 700 times with 3600 seconds sleep time and 2 second delay between connections:
sqlplus / @sleep 3600 > /dev/null 2>&1 &
sleep 2
sqlplus / @sleep 3600 > /dev/null 2>&1 &
sleep 2
sqlplus / @sleep 3600 > /dev/null 2>&1 &
sleep 2
sqlplus / @sleep 3600 > /dev/null 2>&1 &
sleep 2
sqlplus / @sleep 3600 > /dev/null 2>&1 &
sleep 2
<repeat 700 times>
Results were monitored using HP Performance Manager, metric GLB_MEM_ACTIVE_VIRT.
Alternatively one can use svmon, it gives the same value in the column "virtual".
> svmon
size inuse free pin virtual mmode
memory 29360128 26168187 3191941 3213856 15874405 Ded
Results show that each connection used approx 18.3 MB:
- active virtual memory before the test: 61,500 MB
- active virtual memory with 700 connections: 74,300 MB
- difference: 12,800 MB
I attached Excel file with memory graph.
One could improve the test by running SQL*Plus on a different server.