Let us learn Oracle Database Administration skills: April 2024

Sunday, April 28, 2024

Oracle Database and Java Universal Connection Pool

Objective: Asses how does an application (Universal Connection Pool - JDBC) connection pool work in oracle database

Reference urls:

https://github.com/oracle-samples/oracle-db-examples/blob/main/java/jdbc/BasicSamples/UCPTimeouts.java

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-diagnosability.html

https://www.oracle.com/uk/database/technologies/appdev/jdbc-downloads.html

Step 1)

Understand what is connection pool and why it is needed

Connection types:

Dedicated ( 1 client - 1 server)

Shared ( Many Client - 1 or more shared server) - DXXX

Pooled ( Many Client - 1 or more pooled dedicated server) - CMAN (NXXX)

Website -> service call (java program -> UCP -> jdbc connection) -> oracle db

Step 2)

Download and edit the needed UCP sample file from oracle public repo in github

Download necessary jdbc.jar and ucp.jar (you will need on top jdk 8+)

Step 3)

Prepare the sql statement to used in oracle db to assess the pool statistics

The below code can be downloaded from github: https://github.com/svrajadba/ucp.git

--cpool.sql

col machine for a45

col program for a45

col username for a20

col event for a40

col total_process for a10

show parameter process;

select count(1) from v$process;

select a.value total_process

,b.used_process

,(a.value-b.used_process) free_process

from v$parameter a

,(select count(1) used_process from v$process) b

where a.name='processes';

select machine,count(1) from v$session group by machine order by 1;

select machine,program,count(1) from v$session group by machine,program order by 1,2;

select machine,program,username,count(1) from v$session group by machine,program,username order by 1,2,3;

select machine,program,username,count(1) from v$session where username='SCH_HELLOWORLD' and program='UCPTimeouts' group by machine,program,username order by 1,2,3;

select sid,serial#,username,machine,status,state,logon_time,sql_id,sql_exec_start,event,prev_sql_id,prev_exec_start from v$session where username='SCH_HELLOWORLD' and program='UCPTimeouts' order by sid;

#cpool.ksh

#!/bin/ksh

export ORACLE_SID=ORA19C

export ORAENV_ASK=NO

. oraenv >/dev/null 2>&1

tstmp=$(echo $(date '+%d%m%y_%H%M%S'))

sqlplus '/as sysdba' << SQLEOF >${tstmp}_cpoolstatus_check.out

set echo on

set feedback on

set lines 1200 pages 3000 colsep , time on timing on trim on trims on

alter session set nls_Date_format='DD/MON/YYYY HH24:MI:SS';

@cpool.sql

exit;

SQLEOF

echo "Output file: " ${tstmp}_cpoolstatus_check.out

Step 4)

Understand the UCP program test flow

A) test routine 1: The connection pool reaches its max limit and a new incoming client request fails to create a new connection

B) test routine 2: The available connection after reaching demoInactiveConnectionTimeout value in idle time is terminated and process resource is released

C) test routine 3: The barrowed connection after reaching demoTimeToLiveConnectionTimeout value in time irrespective of active or inactive is released to available status in connection pool

D) test routine 4: The barrowed connection after reaching demoAbandonedConnectionTimeout value in inactive status is released to available status in connection pool

28Apr2024_UCP_function.jpg ‎- Photos

Step 5)

Compile the code

javac -classpath ./ojdbc8.jar:./ucp.jar:. UCPTimeouts.java

ignore warning:

Note: UCPTimeouts.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

You should get the class file now: UCPTimeouts.class

Output:

[oracle@vcentos79-oracle-sa1 vagrant]$ ls -altr UCPTim*.java

-rwxrwxrwx. 1 vagrant vagrant 13600 Apr 28 08:11 UCPTimeouts.java

[oracle@vcentos79-oracle-sa1 vagrant]$ javac -classpath ./ojdbc8.jar:./ucp.jar:. UCPTimeouts.java

Note: UCPTimeouts.java uses or overrides a deprecated API.

Note: Recompile with -Xlint:deprecation for details.

[oracle@vcentos79-oracle-sa1 vagrant]$ ls -altr ./ojdbc8.jar ./ucp.jar

-rwxrwxrwx. 1 vagrant vagrant 6980481 Apr 25 11:20 ./ojdbc8.jar

-rwxrwxrwx. 1 vagrant vagrant 1485969 Apr 25 11:20 ./ucp.jar

[oracle@vcentos79-oracle-sa1 vagrant]$ ls -altr *.class

...

-rwxrwxrwx. 1 vagrant vagrant 8927 Apr 28 09:40 UCPTimeouts.class

[oracle@vcentos79-oracle-sa1 vagrant]$