(I)-Tracing Methods (II)-Object Type(Part-1) (III)-Object Type(Part-2) (IV)-Sql Executions (IV) (V)-Contentions and Scalability
Continue from Blog Part-1 (Row Cache Objects, Row Cache Latch on Object Type: Plsql vs Java Call (Part-1) (II) ), this Blog will explore more object-oriented features of Object Types, and demonstrate the impact on Row Cache Object GETs, and Row Cache Latch GETs.
Based on Part-1 Test Code, we define two super classes (see appended PLSQL Test Code):
t_obj_ref_super for t_obj_ref t_obj_ret_super for t_obj_retThen we try different ways of using them in Plsql code and Java code, and monitor the consequence on Row Cache Object GETs.
In Section 9. Plsql DataType Test of this Blog, we added the test of Plsql DataType: record, nested table, associative Array in both Plsql dynamic call and Java call (with JDBC Connection Pool).
Note: All tests are done in Oracle 12.1.0.2.
1. Code Changes
Compare to Part-1, following changes are made in In this Part-2 Blog (see appended Test Code).
-. Setup a super Class: T_OBJ_RET_SUPER, and put T_OBJ_RET under it. -. Introduce a new Object Type: T_OBJ_REF, and its super Class: T_OBJ_REF_SUPER. -. Create a new procedure test_ref_call using above Object Types. -. Add a new line in Part-1 function foo to invoke test_ref_call.Here the new procedure test_ref_call (V1), and the modified function foo.
---=========== V1, t_obj_ret, callforward with 0 t_obj_ref CID call, 2 t_obj_ret CID call ==========---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
---=====================---
create or replace function foo (
p_in in t_obj_in
,p_out out t_obj_out
,p_inout in out t_obj_inout) return t_obj_ret
as
l_ret t_obj_ret;
begin
-- l_ret.id return 1122+112=1234
l_ret := t_obj_ret(p_in.id + 112, p_in.name);
p_out := t_obj_out(p_inout.id, p_inout.name);
p_inout.id := l_ret.id;
-- Subroutine using T_OBJ_REF added in Part-2
test_ref_call(l_ret, p_in.id);
return l_ret;
end;
/
2. Case_1: No Super Class Call
In Case_1, test_ref_call calls t_obj_ret.callforward, without using any super classes.
---=========== V1, t_obj_ret, callforward with 0 t_obj_ref CID call, 2 t_obj_ret CID call ==========---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
2.1. Plsql Dynamic Call
Same as Part-1, Call foo 100 times:
BEGIN :1 := foo(:2, :3, :4); END;
begin
trc_start(4294967295);
foo_proc(100);
trc_stop;
end;
/
*************** CID Stats with Address ****************
CID = 17 ADDR = 17124A3B0 CNT = 503
CID = 11 ADDR = 16F100318 CNT = 502
CID = 7 ADDR = 1717B2250 CNT = 497
*************** CID Stats ****************
CID = 17 CNT = 509
CID = 11 CNT = 509 --1 T_OBJ_RET, 1 T_OBJ_IN, 1 T_OBJ_OUT, 2 T_OBJ_INOUT
CID = 7 CNT = 530
*************** CID Stats Summary ****************
CNT = 1554
2.2. Java Call
Call foo 100 times in Java CallableStatement:
BEGIN :1 := K.FOO(:2, :3, :4); END;
java RCOObjTypeJDBC1 "jdbc:oracle:thin:k/s@testDB:1521:testDB" 100 1
*************** CID Stats with Address ****************
CID = 17 ADDR = 178D70438 CNT = 701
CID = 11 ADDR = 16F1C6A48 CNT = 696
CID = 7 ADDR = 172403178 CNT = 700
*************** CID Stats ****************
CID = 17 CNT = 708
CID = 11 CNT = 704 -- 2 T_OBJ_RET, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT
CID = 7 CNT = 715
*************** CID Stats Summary ****************
CNT = 2160
3. Case_2: using Super Class: t_obj_ref_super
Redefine test_ref_call (V2) to call t_obj_ret.callforward_super, instead of callforward. The difference is callforward_super's first parameter is super class of t_obj_ref.
callforward (p_obj_ref in out nocopy t_obj_ref, p_id number, p_name varchar2) callforward_super(p_obj_ref in out nocopy t_obj_ref_super, p_id number, p_name varchar2)Now we can see there is 1 additional T_OBJ_REF Row Cache Object Get in comparing to above foo call in both Plsql and Java.
---=========== V2, using Super Class: t_obj_ref_super: 1 t_obj_ref CID call, 2 t_obj_ret CID call ==========---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward_super(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
3.1 Plsql Dynamic Call
---=========== V2, test_ref_call ==========---
*************** CID Stats with Address ****************
CID = 17 ADDR = 172298F88 CNT = 100 --T_OBJ_RET
CID = 17 ADDR = 17124A5E0 CNT = 100 --T_OBJ_IN
CID = 17 ADDR = 17124A810 CNT = 100 --T_OBJ_OUT
CID = 17 ADDR = 17179CB18 CNT = 101 --T_OBJ_REF
CID = 17 ADDR = 17124A3B0 CNT = 200 --T_OBJ_INOUT
CID = 11 ADDR = 16DA41CD8 CNT = 100 --T_OBJ_RET
CID = 11 ADDR = 16DA41478 CNT = 100 --T_OBJ_IN
CID = 11 ADDR = 16DA418A8 CNT = 100 --T_OBJ_OUT
CID = 11 ADDR = 16F367C50 CNT = 101 --T_OBJ_REF
CID = 11 ADDR = 16DA41048 CNT = 200 --T_OBJ_INOUT
CID = 7 ADDR = 1716FDFB0 CNT = 606
*************** CID Stats ****************
CID = 17 CNT = 616
CID = 11 CNT = 616 -- 1 T_OBJ_RET, 1 T_OBJ_IN, 1 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
CID = 7 CNT = 633
*************** CID Stats Summary ****************
CNT = 1877
3.2. Java Call
--------------- Java Level 4294967295 --------------
---=========== V2, test_ref_call ==========---
CID = 17 ADDR = 172298F88 CNT = 797
CID = 11 ADDR = 16F1A1B90 CNT = 797
CID = 7 ADDR = 17169DA78 CNT = 798
----------------------
CID = 17 CNT = 829
CID = 11 CNT = 813 -- 2 T_OBJ_RET, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
CID = 7 CNT = 822
----------------------
CNT = 2542
4. Case_3: using Super Class: t_obj_ret_super
Take above test_ref_call, change its first paramter to super class:
t_obj_ret_superas the first paramter, instead of t_obj_ret.
Test shows that there is one more T_OBJ_RET Row Cache Object Get in comparing to above foo call in both Plsql and Java.
---=========== V3, using Super Class: t_obj_ret_super: 1 t_obj_ref CID call, 3 t_obj_ret CID call ==========---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret_super, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward_super(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
4.1 Plsql Dynamic Call
---=========== V3, test_ref_call ==========---
*************** CID Stats with Address ****************
CID = 17 ADDR = 17179CB18 CNT = 699
CID = 11 ADDR = 16F100318 CNT = 697
CID = 7 ADDR = 171705DB0 CNT = 695
*************** CID Stats ****************
CID = 17 CNT = 708
CID = 11 CNT = 708 -- 2 T_OBJ_RET, 1 T_OBJ_IN, 1 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
CID = 7 CNT = 718
*************** CID Stats Summary ****************
CNT = 2140
4.2. Java Call
---=========== V3, test_ref_call ==========---
*************** CID Stats with Address ****************
CID = 17 ADDR = 17C2A5200 CNT = 909
CID = 11 ADDR = 16F1A1B90 CNT = 896
CID = 7 ADDR = 171705DB0 CNT = 911
*************** CID Stats ****************
CID = 17 CNT = 930
CID = 11 CNT = 914 -- 3 T_OBJ_RET, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
CID = 7 CNT = 923
*************** CID Stats Summary ****************
CNT = 2845
5. Case_4: Java register super Class as OutParameter
Now we can try to use super class in Java Class. In Java Test Code (see Blog Part-1) , replace line:
cStmt.registerOutParameter(1, OracleTypes.STRUCT, "K.T_OBJ_RET");
cStmt.registerOutParameter(1, OracleTypes.STRUCT, "K.T_OBJ_RET_SUPER");
1 T_OBJ_RET CID call 2 T_OBJ_RET_SUPER CID callsinstead of
3 T_OBJ_RET CID callsin the previous test.
6. Case_5: Call by NULL value
If invoking the function by NULL value in Plsql Dynamic or Java, the cid GETs are not changed.
See Test Code (out-commented) in Blog: Row Cache Objects, Row Cache Latch on Object Type: Plsql vs Java Call (Part-1) (II)
7. Summary
The number of GETs is increased with the number of Super Classes inteventions.
Case_1: No Super Class Call: same as Part-1
Plsql Dynamic Call:
1 T_OBJ_RET, 1 T_OBJ_IN, 1 T_OBJ_OUT, 2 T_OBJ_INOUT
5*3 = 15 Row Cache Objects GETs, 15*3=45 Row Cache Latch GETs.
Java Call:
2 T_OBJ_RET, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT
7*3 = 21 Row Cache Objects GETs, 21*3=63 Row Cache Latch GETs.
Case_2: using Super Class: t_obj_ref_super: 1 additional T_OBJ_REF
Plsql Dynamic Call:
1 T_OBJ_RET, 1 T_OBJ_IN, 1 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
6*3 = 18 Row Cache Objects GETs, 18*3=54 Row Cache Latch GETs.
Java Call :
2 T_OBJ_RET, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
8*3 = 24 Row Cache Objects GETs, 24*3=72 Row Cache Latch GETs.
Case_3: using Super Class: t_obj_ret_super: 1 additional T_OBJ_RET
Plsql Dynamic Call:
2 T_OBJ_RET, 1 T_OBJ_IN, 1 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
7*3 = 21 Row Cache Objects GETs, 21*3=63 Row Cache Latch GETs.
Java Call :
3 T_OBJ_RET, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
9*3 = 27 Row Cache Objects GETs, 27*3=81 Row Cache Latch GETs.
Case_4: Java register super Class as OutParameter: 1 T_OBJ_RET, 2 T_OBJ_RET_SUPER
Java Call:
1 T_OBJ_RET, 2 T_OBJ_RET_SUPER, 1 T_OBJ_IN, 2 T_OBJ_OUT, 2 T_OBJ_INOUT, 1 T_OBJ_REF
9*3 = 27 Row Cache Objects GETs, 27*3=81 Row Cache Latch GETs.
Case_5: Call by NULL value
Plsql Dynamic Call: no influence, same cid GETs
Java Call: no influence, same cid GETs
More complexity can be further investigated, for example, Object Collections (Associative array, Nested table, Varray),
attribute Object Types.In Section 9. Plsql DataType Test of this Blog, we also made Plsql DataType Test in Plsql dynamic call and Java call (with JDBC Connection Pool).
8. Plsql Test Code
------------------------- parameters(IN, OUT, IN OUT) different Types -------------------
create or replace type t_obj_in force as object (id number, name varchar2(30));
/
create or replace type t_obj_out force as object (id number, name varchar2(30));
/
create or replace type t_obj_inout force as object (id number, name varchar2(30));
/
------------------------- t_obj_ref_super, t_obj_ref -------------------
create or replace type t_obj_ref_super force is object (dummy number
,member procedure callbackward(p_id number, p_name varchar2)
)
not final
not instantiable
/
create or replace type t_obj_ref force under t_obj_ref_super (id number, name varchar2(30)
,constructor function t_obj_ref (p_id number) return self as result
,overriding member procedure callbackward(p_id number, p_name varchar2)
);
/
create or replace type body t_obj_ref as
constructor function t_obj_ref (p_id number) return self as result is
begin
id := p_id;
name := 'T_OBJ_REF.name';
return;
end;
overriding member procedure callbackward(p_id number, p_name varchar2) as
l_dummy varchar2(100);
begin
id := p_id;
name := p_name;
l_dummy := 'callbackward: '||p_id||'_'||p_name;
debug(l_dummy);
end;
end;
/
------------------------- t_obj_ret_super, t_obj_ret -------------------
create or replace type t_obj_ret_super force as object (dummy number
,member procedure callforward (p_obj_ref in out nocopy t_obj_ref, p_id number, p_name varchar2)
,member procedure callforward_super(p_obj_ref in out nocopy t_obj_ref_super, p_id number, p_name varchar2))
not final
not instantiable
/
create or replace type t_obj_ret force under t_obj_ret_super (id number, name varchar2(30)
,constructor function t_obj_ret (p_id number, p_name varchar2) return self as result
,overriding member procedure callforward (p_obj_ref in out nocopy t_obj_ref, p_id number, p_name varchar2)
,overriding member procedure callforward_super(p_obj_ref in out nocopy t_obj_ref_super, p_id number, p_name varchar2))
/
create or replace type body t_obj_ret as
constructor function t_obj_ret (p_id number, p_name varchar2) return self as result is
begin
id := p_id;
name := p_name;
return;
end;
overriding member procedure callforward(p_obj_ref in out nocopy t_obj_ref, p_id number, p_name varchar2) as
begin
p_obj_ref.callbackward(p_id, p_name);
end;
overriding member procedure callforward_super(p_obj_ref in out nocopy t_obj_ref_super, p_id number, p_name varchar2) as
begin
p_obj_ref.callbackward(p_id, p_name);
end;
end;
/
-------------------------------------------------------------------
---=== V1, t_obj_ret, callforward with 0 t_obj_ref CID call, 2 t_obj_ret CID call in Java ===---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
---=== V2, using Super Class: t_obj_ref_super: 1 t_obj_ref CID call, 2 t_obj_ret CID call in Java ===---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward_super(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
---=== V3, using Super Class: t_obj_ret_super: 1 t_obj_ref CID call, 3 t_obj_ret CID call in Java ===---
create or replace procedure test_ref_call(p_obj_ret in out nocopy t_obj_ret_super, p_id number)
as
l_obj_ref t_obj_ref;
begin
l_obj_ref := new t_obj_ref(p_id);
p_obj_ret.callforward_super(l_obj_ref, p_id, 'TEST_REF_PROC_1.name');
end;
/
---=====================---
create or replace function foo (
p_in in t_obj_in
,p_out out t_obj_out
,p_inout in out t_obj_inout) return t_obj_ret
as
l_ret t_obj_ret;
begin
-- l_ret.id return 1122+112=1234
l_ret := t_obj_ret(p_in.id + 112, p_in.name);
p_out := t_obj_out(p_inout.id, p_inout.name);
p_inout.id := l_ret.id;
-- T_OBJ_REF call line added in Part-2
test_ref_call(l_ret, p_in.id);
return l_ret;
end;
/
9. Plsql DataType Test
We tested Plsql DataType: record, nested table, associative Array in Plsql dynamic call and Java call. Here the test result and test code.
------ Plsql DataType Test Result: Plsql dynamic vs. Java ------
1. Plsql DataType: record
Plsql dynmic Call: no CID increase
Java Call: each Call, 2 Calls of ('dc_global_oids' on "TEST_JDBC_PKG'", 'dc_objects' on "TEST_JDBC_PKG", 'dc_users' on "K")
2. Plsql DataType: nested table (@TODO varray)
Plsql dynmic Call: no CID increase
Java Call: each Call, 2 Calls of ('dc_global_oids' on "TEST_JDBC_PKG'", 'dc_objects' on "TEST_JDBC_PKG", 'dc_users' on "K")
3. Plsql DataType: associative Array - scalar type
Plsql dynmic Call: no CID increase
Java Call: no CID increase
4. Plsql DataType: associative Array - record type
Plsql dynmic Call: no CID increase
Java Call: NOT supported. Only support basic scalar types (NUMERIC and VARCHAR)
See: Database JDBC Developer's Guide and Reference, Accessing PL/SQL Index-by Tables
(https://docs.oracle.com/cd/B28359_01/java.111/b31224/oraint.htm#BABBGDFA)
---------------- Plsql DataType SetUp -------------------
create or replace package k.TEST_JDBC_PKG is
type PLSQL_RECORD is record(name varchar2(100), id pls_integer);
type PLSQL_TAB is table of number;
type PLSQL_AARRAY is table of varchar2(10) index by pls_integer;
type PLSQL_RECARY is table of PLSQL_RECORD index by pls_integer;
end;
/
-- Plsql dynmic Call: no CID increase
-- Java Call: each Call, 2 x ('dc_global_oids' on "TEST_JDBC_PKG'", 'dc_objects' on "TEST_JDBC_PKG", 'dc_users' on "K")
create or replace procedure foo_proc_record (o_rc out nocopy TEST_JDBC_PKG.PLSQL_RECORD) as
begin
o_rc.name := 'ksun';
o_rc.id := 1122;
end;
/
-- Plsql dynmic Call: no CID increase
-- Java Call: each Call, 1 x ('dc_global_oids' on "TEST_JDBC_PKG'", 'dc_objects' on "TEST_JDBC_PKG", 'dc_users' on "K")
create or replace procedure foo_proc_tab (o_tab out nocopy TEST_JDBC_PKG.PLSQL_TAB) as
begin
--initialization mandatory; otherwise, ORA-06531: Reference to uninitialized collection
o_tab := new TEST_JDBC_PKG.PLSQL_TAB();
o_tab.extend(2);
o_tab(1) := 11;
o_tab(2) := 22;
end;
/
-- Plsql dynmic Call: no CID increase
-- Java Call: no CID increase
create or replace procedure foo_proc_aarray (o_ary out nocopy TEST_JDBC_PKG.PLSQL_AARRAY) as
begin
o_ary(1) := 'aaa';
o_ary(2) := 'bbb';
end;
/
-- Plsql dynmic Call: no CID increase
-- Java Call: NOT supported. Only support basic scalar types (NUMERIC and VARCHAR)
-- See: Database JDBC Developer's Guide and Reference, Accessing PL/SQL Index-by Tables
-- (https://docs.oracle.com/cd/B28359_01/java.111/b31224/oraint.htm#BABBGDFA)
create or replace procedure foo_proc_recary (o_ray out nocopy TEST_JDBC_PKG.PLSQL_RECARY) as
begin
o_ray(1).name := 'aaaa';
o_ray(1).id := 111;
o_ray(2).name := 'bbbb';
o_ray(2).id := 222;
end;
/
---------------- Plsql Test Code -------------------
create or replace procedure foo_proc_record_loop (p_cnt number) as
l_stmt varchar2(100);
l_record TEST_JDBC_PKG.PLSQL_RECORD;
begin
l_stmt := q'[begin K.foo_proc_record(:1); end;]';
for i in 1..p_cnt loop
execute immediate l_stmt using OUT l_record;
end loop;
dbms_output.put_line('l_record.name=' ||l_record.name);
end;
/
--exec foo_proc_record_loop(10);
create or replace procedure foo_proc_tab_loop (p_cnt number) as
l_stmt varchar2(100);
l_tab TEST_JDBC_PKG.PLSQL_TAB := new TEST_JDBC_PKG.PLSQL_TAB();
begin
l_stmt := q'[begin K.foo_proc_tab(:1); end;]';
--l_tab.extend(2);
for i in 1..p_cnt loop
execute immediate l_stmt using OUT l_tab;
end loop;
dbms_output.put_line('l_tab.count=' ||l_tab.count);
dbms_output.put_line('l_tab(2)=' ||l_tab(2));
end;
/
--exec foo_proc_tab_loop(10);
create or replace procedure foo_proc_aarray_loop (p_cnt number) as
l_stmt varchar2(100);
l_ary TEST_JDBC_PKG.PLSQL_AARRAY;
begin
l_stmt := q'[begin K.foo_proc_aarray(:1); end;]';
--l_tab.extend(2);
for i in 1..p_cnt loop
execute immediate l_stmt using OUT l_ary;
end loop;
dbms_output.put_line('l_ary.count=' ||l_ary.count);
dbms_output.put_line('l_ary(2)=' ||l_ary(2));
end;
/
--exec foo_proc_aarray_loop(10);
create or replace procedure foo_proc_recary_loop (p_cnt number) as
l_stmt varchar2(100);
l_ray TEST_JDBC_PKG.PLSQL_RECARY;
begin
l_stmt := q'[begin K.foo_proc_recary(:1); end;]';
--l_tab.extend(2);
for i in 1..p_cnt loop
execute immediate l_stmt using OUT l_ray;
end loop;
dbms_output.put_line('l_ray.count=' ||l_ray.count);
dbms_output.put_line('l_ray(2).id=' ||l_ray(2).id);
end;
/
--exec foo_proc_recary_loop(10);
//------------ Java Test Code with Connection Pool ------------//
/**
Tested with OracleDataSource Connection Pool, can also tested with PoolDataSourceFactory (UCP).
-- setup CLASSPATH
export CLASSPATH=$CLASSPATH:java-path:$ORACLE_HOME/jdbc/lib/ojdbc8.jar:$ORACLE_HOME/ucp/lib/ucp.jar:ODSTestDBOraclePool-path
-- Compile
/usr/java/bin/javac ODSJDBCUtils.java ODSTestDBOraclePoolThread.java ODSTestDBOraclePool.java
-- Run
/usr/java/bin/java ODSTestDBOraclePool
-- Example
/usr/java/bin/java -Xmx30m -Xms8m ODSTestDBOraclePool 3 100 1000 2
-- useCase
1. SQL Object Type Test, "BEGIN :1 := K.FOO(:2, :3, :4); END;"
2. Plsql DataType Record, "BEGIN K.FOO_PROC_RECORD(:1); END;"
3. Plsql DataType Nested Table, "BEGIN K.FOO_PROC_TAB(:1); END;"
4. Plsql DataType Associative Array, "BEGIN K.FOO_PROC_AARRAY(:1); END;"
-- Ref: Connect to Oracle using a connection pool (https://www.rgagnon.com/javadetails/java-0545.html)
*/
//------------------------- ODSJDBCUtils.java -------------------------------//
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.OracleConnectionCacheManager;
import java.util.Properties;
import java.sql.*;
public class ODSJDBCUtils {
private final static String CACHE_NAME = "KSUN_CACHE";
private static OracleDataSource ods = null;
static {
System.out.println("OracleDataSource Initialization");
try {
ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:k/s@testdb:1521:testdb"); // thin Use TNS listener
//ods.setURL("jdbc:oracle:oci8:k/s@testdb:1521:testdb"); //Maybe for Accessing PL/SQL Index-by Tables
//ods.setURL("jdbc:oracle:thin:@"); // thin Using Bequeath Connection, no need of TNS:no listener. thin support Bequeath from JDBC 23c
//{ods.setURL("jdbc:oracle:oci:@");} // oci Use Bequeath Connection, no need of TNS listener
//{ods.setURL("jdbc:oracle:oci:@estdb:1521:testdb");} // oci Use TNS listener
//ods.setURL("jdbc:oracle:oci8:k/s@testdb:1521:testdb"); //Maybe for Accessing PL/SQL Index-by Tables
ods.setUser("k");
ods.setPassword("k");
// caching parms
ods.setConnectionCachingEnabled(true);
ods.setConnectionCacheName(CACHE_NAME);
Properties cacheProps = new Properties();
cacheProps.setProperty("MinLimit", "2");
cacheProps.setProperty("MaxLimit", "60");
cacheProps.setProperty("InitialLimit", "2");
cacheProps.setProperty("ConnectionWaitTimeout", "5");
cacheProps.setProperty("ValidateConnection", "true");
ods.setConnectionCacheProperties(cacheProps);
}
catch (SQLException e) {
e.printStackTrace();
}
}
private ODSJDBCUtils() { }
public static Connection getConnection() throws SQLException {
return getConnection("env. unspecified");
}
public static Connection getConnection(String thrd)
throws SQLException
{
System.out.println("Request connection for " + thrd);
if (ods == null) {
throw new SQLException("OracleDataSource is null.");
}
return ods.getConnection();
}
public static void closePooledConnections() throws SQLException{
if (ods != null ) {
ods.close();
}
}
public static void listCacheInfos() throws SQLException{
OracleConnectionCacheManager occm = OracleConnectionCacheManager.getConnectionCacheManagerInstance();
System.out.println (occm.getNumberOfAvailableConnections(CACHE_NAME) + " connections are available in cache " + CACHE_NAME);
System.out.println (occm.getNumberOfActiveConnections(CACHE_NAME) + " connections are active");
}
}
//-------------------------- ODSTestDBOraclePoolThread.java ------------------------------//
import java.sql.*;
import java.util.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.*;
import java.math.BigDecimal;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.sql.Datum;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
public class ODSTestDBOraclePoolThread implements Runnable {
private int noThread = 0;
private int loopCNT = 0;
private int sleepMs = 1000;
private int useCase = 2;
static String TESTSTMT_OBJ = "BEGIN :1 := K.FOO(:2, :3, :4); END;";
static String TESTSTMT_RECORD = "BEGIN K.FOO_PROC_RECORD(:1); END;";
static String TESTSTMT_TAB = "BEGIN K.FOO_PROC_TAB(:1); END;";
static String TESTSTMT_AARRAY = "BEGIN K.FOO_PROC_AARRAY(:1); END;";
ODSTestDBOraclePoolThread(int n, int loopCount, int sleepMili, int useCaseDef) {
noThread = n;
loopCNT = loopCount;
sleepMs = sleepMili;
useCase = useCaseDef;
}
static void objCall(Connection conn, int loopCNT) {
CallableStatement cStmt;
if (conn != null) {
try {
cStmt = conn.prepareCall(TESTSTMT_OBJ);
StructDescriptor dpIn = StructDescriptor.createDescriptor("K.T_OBJ_IN", conn);
StructDescriptor dpInOut = StructDescriptor.createDescriptor("K.T_OBJ_INOUT", conn);
System.out.println("Call dbcall");
Object [] objField = new Object[2];
objField[0] = new Integer(1122);
objField[1] = new String("T_OBJ_IN.name");
STRUCT objIn = new STRUCT(dpIn, conn, objField);
STRUCT objInOut = new STRUCT(dpInOut, conn, objField);
STRUCT objRet = null;
STRUCT objOut = null;
BigDecimal idVal;
for(int i=0; i < loopCNT; i++){
cStmt.registerOutParameter(1, OracleTypes.STRUCT, "K.T_OBJ_RET");
cStmt.setObject(2, objIn);
cStmt.setObject(4, objInOut);
cStmt.registerOutParameter(3, OracleTypes.STRUCT, "K.T_OBJ_OUT");
cStmt.registerOutParameter(4, OracleTypes.STRUCT, "K.T_OBJ_INOUT");
cStmt.execute();
objRet = (STRUCT)cStmt.getObject(1);
objOut = (STRUCT)cStmt.getObject(3);
objInOut = (STRUCT)cStmt.getObject(4);
}
idVal = (BigDecimal)objOut.getAttributes()[0];
System.out.println(TESTSTMT_OBJ + " *** objOut.id =" + idVal.intValue());
cStmt.close();
} catch (SQLException e) {e.printStackTrace();}
} else {
System.out.println("Failed to make connection!");
}
System.out.println("*** objCall ***");
}
static void recordCall(Connection conn, int loopCNT) {
CallableStatement cStmt;
STRUCT aRecord = null;
String rName = null;
BigDecimal rId = null;
if (conn != null) {
try {
cStmt = conn.prepareCall(TESTSTMT_RECORD);
for(int i=0; i < loopCNT; i++){
cStmt.registerOutParameter(1, OracleTypes.STRUCT, "K.TEST_JDBC_PKG.PLSQL_RECORD");
cStmt.execute();
aRecord = (STRUCT)cStmt.getObject(1);
rName = (String)aRecord.getAttributes()[0];
rId = (BigDecimal)aRecord.getAttributes()[1];
}
System.out.println(TESTSTMT_RECORD + " *** Name = " + rName + " ,ID = " + rId);
cStmt.close();
} catch (SQLException e) {e.printStackTrace();}
} else {
System.out.println("Failed to make connection!");
}
}
static void tabCall(Connection conn, int loopCNT) {
CallableStatement cStmt;
ARRAY aVArray = null;
BigDecimal[] numArray = null;
BigDecimal aNum = null;
if (conn != null) {
try {
cStmt = conn.prepareCall(TESTSTMT_TAB);
for(int i=0; i < loopCNT; i++){
cStmt.registerOutParameter(1, OracleTypes.ARRAY, "K.TEST_JDBC_PKG.PLSQL_TAB");
cStmt.execute();
aVArray = (ARRAY)cStmt.getArray(1);
numArray = (BigDecimal[])aVArray.getArray();
aNum = (BigDecimal)numArray[0];
}
System.out.println(TESTSTMT_TAB + " *** numArray.length = " + numArray.length);
System.out.println(TESTSTMT_TAB + " *** numArray[0] = " + (aNum==null? "NULL": aNum.intValue()));
cStmt.close();
} catch (SQLException e) {e.printStackTrace();}
} else {
System.out.println("Failed to make connection!");
}
}
static void aarrayCall(Connection conn, int loopCNT) {
OracleCallableStatement cStmt;
int maxAArrayLen = 10;
int elementMaxLen = 20;
Datum[] valAArray = new Datum[0];
if (conn != null) {
try {
cStmt = (OracleCallableStatement)conn.prepareCall(TESTSTMT_AARRAY);
for(int i=0; i < loopCNT; i++){
cStmt.registerIndexTableOutParameter (1, maxAArrayLen, OracleTypes.VARCHAR, elementMaxLen);
cStmt.execute();
valAArray = cStmt.getOraclePlsqlIndexTable(1);
}
System.out.println(TESTSTMT_AARRAY + " *** valAArray.length = " + valAArray.length);
for(int i = 0; i < valAArray.length; i++)
System.out.println (TESTSTMT_AARRAY + " *** valAArray[i] = " + valAArray[i].stringValue());
cStmt.close();
} catch (SQLException e) {e.printStackTrace();}
} else {
System.out.println("Failed to make connection!");
}
}
public void run() {
System.out.println("Starting Thread " + noThread);
while (true) {
try {
Connection conn = ODSJDBCUtils.getConnection("Thread: " + noThread);
if (useCase == 1) {objCall (conn, loopCNT);}
else if (useCase == 2) {recordCall(conn, loopCNT);}
else if (useCase == 3) {tabCall (conn, loopCNT);}
else if (useCase == 4) {aarrayCall(conn, loopCNT);}
else {System.out.println ("Give me your Test");}
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset =
stmt.executeQuery("select 'Run UseCase: " + useCase + "' from dual");
while (rset.next())
System.out.println (rset.getString(1) + ", Thread: " + noThread);
rset.close();
stmt.close();
ODSJDBCUtils.listCacheInfos();
conn.close();
System.out.println ("rset/stmt/conn.close OK");
}
catch (SQLException e) { e.printStackTrace();}
finally {
System.out.println ("Sleeping Thread: " + noThread);
try {Thread.sleep(sleepMs);} catch(Exception e) { }
}
}
}
}
//------------------------ ODSTestDBOraclePool.java --------------------------------//
import java.net.URL;
import java.sql.*;
public class ODSTestDBOraclePool {
public static void main(String[] args) throws SQLException {
int threadCNT = Integer.parseInt(args[0]);
int loopCount = Integer.parseInt(args[1]);
int sleepMili = Integer.parseInt(args[2]);
int useCase = Integer.parseInt(args[3]);
for(int i=1; i <= threadCNT; i++){
new Thread( new ODSTestDBOraclePoolThread(i, loopCount, sleepMili, useCase)).start();
}
}
}
