Note: All tests are done with java 8 and ojdbc7.jar on Oracle 12.1.0.2.0.
See appended Test Code.
1. Test and Reasoning
Run:
java OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5
then execute query below, watch its output:
select executions, sql_text, v.*
from v$sql v
where lower(sql_text) like '%dbms_pickler%'
or sql_text like '%NOPARALLEL%';
2 begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;
2 SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#, ..., SO.NAME
FROM SYS.ATTRIBUTE$ A, SYS.TYPE$ T, SYS.TYPE$ AT, SYS. "_CURRENT_EDITION_OBJ" ATO,
SYS.USER$ ATU, SYS. "_CURRENT_EDITION_OBJ" SO, SYS.USER$ SU
WHERE T.TVOID = :B1 AND A.ATTR_TOID = ATO.OID$ ...
ORDER BY ATTRIBUTE#
Since we have one call of StructDescriptor for Oracle Object "TEST_OBJ",
and one call of ArrayDescriptor for Oracle Object Collection (nested table) "TEST_OBJ_ARRAY":
StructDescriptor structDP = StructDescriptor.createDescriptor("TEST_OBJ", conn);
ArrayDescriptor arrayDP = ArrayDescriptor.createDescriptor("TEST_OBJ_ARRAY", conn);
so there are two respective calls of dbms_pickler.get_type_shape to lookup their metadata in Oracle database.Whereas with ojdbc5.jar, it looks like:
dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7)
and there are no two NOPARALLEL hinted sys.type$ queries because there are no calls of dbms_pickler.get_metadata.
Further tested in Oracle 11g dbms_pickler or 12cR1 with ojdbc5.jar, it is not able to observe this behavior.
Comparing output of
SQL > describe dbms_pickler
between 11g and 12cR1, we can see that 11g two APIs dbms_pickler.get_type_shape have 6 parameters; whereas 12cR1 APIs are enhanced with two more overloaded functions having 9 parameters. That could explain the difference:
11g dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7)
12cR1 dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10)
Hence it is possible the regression was introduced in 12cR1 with ojdbc7.jar.
For each oracle connection, the created instances of StructDescriptor and ArrayDescriptor are cached, the subsequent repeated call gets the cached result (one optimization). That is why even we have a loop calls, only two calls of dbms_pickler.get_type_shape are sent to database.
As a further test, we can provoke a java dump by:
SQL> drop type test_obj force;
and get an exception call stack:
java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "TEST_OBJ"
at oracle.sql.TypeDescriptor.getTypeDescriptor(TypeDescriptor.java:1042)
at oracle.sql.StructDescriptor.isValidObject(StructDescriptor.java:89)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:114)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:74)
at OracleJDBC.dbcall(OracleJDBC.java:60)
at OracleJDBC.main(OracleJDBC.java:43)
Here the code path:
OracleJDBC.java
StructDescriptor sDescr = StructDescriptor.createDescriptor("TEST_OBJ", connection);
--->
oracle.sql.StructDescriptor.java
structdescriptor = new StructDescriptor(sqlname, connection);
--->
oracle.jdbc.oracore.OracleTypeADT
init(OracleConnection oracleconnection)
-> initMetadata(oracleconnection);
-> initMetadata12(OracleConnection oracleconnection)
--->
dbms_pickler
-> get_type_shape
-> get_metadata
The above test is performed with 12c deprecated oracle.sql.STRUCT and oracle.sql.ARRAY (first Java Test Code).Run the same test with new wrapped API (second Java Test Code), and provoke the same exception. We can see both implementations are internally identical, but the deprecated API looks more obvious.
java.sql.SQLException: Fail to construct descriptor: Unable to resolve type "TEST_OBJ"
at oracle.sql.TypeDescriptor.getTypeDescriptor(TypeDescriptor.java:1042)
at oracle.sql.StructDescriptor.isValidObject(StructDescriptor.java:89)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:114)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:74)
at oracle.jdbc.driver.PhysicalConnection.createStruct(PhysicalConnection.java:8732)
at OracleJDBC.dbcall(OracleJDBC.java:61)
at OracleJDBC.main(OracleJDBC.java:42)
Also tested with Java Stored Procedure in Oracle RDBMS embedded JVM (see Test Code: Java Stored Procedure) by:
SQL > exec dbcallCtrl(3, 5);
It has the same behavior.
2. Trace file and HProfile
In the Test Code, we also make the Event 10046 Trace and HProfile, here excerpted outputs:
(a). Row Trace File
begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 <--- oacdty=02: SQLT_NUM: number
oacflg=03 fl2=1000000 frm=01 csi=873 siz=24 off=0
kxsbbbfp=fffffd7ffbe8afe8 bln=22 avl=00 flg=05
Bind#1
oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00 <--- oacdty=01: SQLT_CHR: varchar2
oacflg=03 fl2=1000010 frm=01 csi=873 siz=32767 off=0
kxsbbbfp=fffffd7ffbe81fe8 bln=32767 avl=08 flg=05
value="TEST_OBJ"
...
Bind#1
oacdty=01 mxl=32767(32767) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000010 frm=01 csi=873 siz=32767 off=0
kxsbbbfp=fffffd7ffbea3000 bln=32767 avl=14 flg=05
value="TEST_OBJ_ARRAY"
(b). TKPROF Trace File
begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.01 0 59 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 59 0 2
---- SQL_ID: 7sz0r2npbx7dp
SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#, DECODE(AT.TYPECODE, 9,
DECODE(A.CHARSETFORM, 2, 'NVARCHAR2', ATO.NAME), 96, DECODE(A.CHARSETFORM,
2, 'NCHAR', ATO.NAME), 112, DECODE(A.CHARSETFORM, 2, 'NCLOB', ATO.NAME),
ATO.NAME), DECODE(BITAND(AT.PROPERTIES, 64), 64, NULL, ATU.NAME), NULL,
A.ATTR_TOID, DECODE(BITAND(T.PROPERTIES, 65536), 65536, 'NO', 'YES'),
SU.NAME, SO.NAME
FROM
SYS.ATTRIBUTE$ A, SYS.TYPE$ T, SYS.TYPE$ AT, SYS. "_CURRENT_EDITION_OBJ" ATO,
SYS.USER$ ATU, SYS. "_CURRENT_EDITION_OBJ" SO, SYS.USER$ SU WHERE T.TVOID =
:B1 AND A.ATTR_TOID = ATO.OID$ AND ATO.OWNER# = ATU.USER# AND A.TOID =
T.TVOID AND T.PACKAGE_OBJ# IS NULL AND AT.TVOID = A.ATTR_TOID AND
AT.SUPERTOID = SO.OID$ (+) AND SO.OWNER# = SU.USER# (+) ORDER BY ATTRIBUTE#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 37 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 37 0 2
---- SQL_ID: 9vv8244bcq529
SELECT /*+ NOPARALLEL */ 1, U.NAME, O.NAME, O.OID$
FROM
SYS. "_CURRENT_EDITION_OBJ" O, SYS.USER$ U, SYS.TYPE$ T WHERE T.SUPERTOID =
:B1 AND T.TVOID = O.OID$ AND O.SUBNAME IS NULL AND O.OWNER# = U.USER#
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 6 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 6 0 0
Note that sys.USER$ is a very thick declared table. For each row, it can requires 3 8-KB Blocks(See Blog: Redo/Undo explosion from thick declared table insert)
SQL> select count(*) col_cnt, sum(data_length) max_rowsize, max(data_length) max_col_len
from dba_tab_cols
where owner='SYS' and table_name='USER$';
COL_CNT MAX_ROWSIZE MAX_COL_LEN
------- ----------- -----------
30 18670 4000
(c). HProfile
Function Elapsed Time (microsecs) Data sorted by Total Subtree Elapsed Time (microsecs)
10517 microsecs (elapsed time) & 20 function calls
| Subtree | Ind% | Function | Ind% | Descendants | Ind% | Calls | Ind% | Function Name |
|---|---|---|---|---|---|---|---|---|
| 10517 | 100% | 11 | 0.1% | 10506 | 99.9% | 3 | 15.0% | __plsql_vm |
| 10506 | 99.9% | 374 | 3.6% | 10132 | 96.3% | 3 | 15.0% | __anonymous_block |
| 10112 | 96.1% | 3945 | 37.5% | 6167 | 58.6% | 2 | 10.0% | SYS.DBMS_PICKLER.GET_TYPE_SHAPE (Line 384) |
| 1708 | 16.2% | 1708 | 16.2% | 0 | 0.0% | 2 | 10.0% | SYS.DBMS_PICKLER.__static_sql_exec_line141 (Line 141) |
| 1263 | 12.0% | 1263 | 12.0% | 0 | 0.0% | 2 | 10.0% | SYS.DBMS_PICKLER.__static_sql_exec_line236 (Line 236) |
| 1094 | 10.4% | 1094 | 10.4% | 0 | 0.0% | 2 | 10.0% | SYS.DBMS_PICKLER.__static_sql_exec_line175 (Line 175) |
| 1067 | 10.1% | 1067 | 10.1% | 0 | 0.0% | 2 | 10.0% | SYS.DBMS_PICKLER.__static_sql_exec_line211 (Line 211) |
| 1035 | 9.8% | 1035 | 9.8% | 0 | 0.0% | 2 | 10.0% | SYS.DBMS_PICKLER.__sql_fetch_line186 (Line 186) |
| 20 | 0.2% | 20 | 0.2% | 0 | 0.0% | 1 | 5.0% | K.TRC_STOP.TRC_STOP (Line 1) |
| 0 | 0.0% | 0 | 0.0% | 0 | 0.0% | 1 | 5.0% | SYS.DBMS_HPROF.STOP_PROFILING (Line 63) |
(d). dbms_pickler.get_type_shape Test
In above TKPROF output, we saw two NOPARALLEL hinted queries, which are two REF CURSOR created when calling dbms_pickler.get_type_shape.
Make a direct Plsql call of dbms_pickler.get_type_shape, and look its output, we can get some understanding of its functionalities.
-------------------------- ATTR_RC: sql_id: 7sz0r2npbx7dp ----------------
--OPEN ATTR_RC FOR
SELECT /*+ NOPARALLEL */ 1, A.NAME, A.ATTRIBUTE#,
DECODE(AT.TYPECODE,
9, DECODE(A.CHARSETFORM, 2, 'NVARCHAR2', ATO.NAME),
96, DECODE(A.CHARSETFORM, 2, 'NCHAR', ATO.NAME),
112, DECODE(A.CHARSETFORM, 2, 'NCLOB', ATO.NAME),
ATO.NAME) at_typecode,
DECODE(BITAND(AT.PROPERTIES, 64), 64, NULL, ATU.NAME) at_properties, NULL,
A.ATTR_TOID,
DECODE(BITAND(T.PROPERTIES, 65536), 65536, 'NO', 'YES') t_properties,
SU.NAME, SO.NAME
FROM SYS.ATTRIBUTE$ A,
SYS.TYPE$ T, SYS.TYPE$ AT,
SYS."_CURRENT_EDITION_OBJ" ATO, SYS.USER$ ATU,
SYS."_CURRENT_EDITION_OBJ" SO, SYS.USER$ SU
WHERE T.TVOID = TYPOID --'7B54200768642599E054005056984D97'
AND A.ATTR_TOID = ATO.OID$ AND
ATO.OWNER# = ATU.USER# AND
A.TOID = T.TVOID AND
T.PACKAGE_OBJ# IS NULL AND
AT.TVOID = A.ATTR_TOID AND
AT.SUPERTOID = SO.OID$ (+) AND SO.OWNER# = SU.USER# (+)
ORDER BY ATTRIBUTE#;
-------------------------- SUBTYPE_RC: sql_id: 9vv8244bcq529 ------------------
--OPEN SUBTYPE_RC FOR
SELECT /*+ NOPARALLEL */ 1, U.NAME, O.NAME, O.OID$
FROM SYS."_CURRENT_EDITION_OBJ" O,
SYS.USER$ U, SYS.TYPE$ T
WHERE T.SUPERTOID = TYPOID --'7B54200768642599E054005056984D97'
AND T.TVOID = O.OID$ AND
O.SUBNAME IS NULL AND
O.OWNER# = U.USER#;
-------------------------- Create Table -----------------------
create table attr_rc_pickler_table as
select /*+ noparallel */ 1 c1, a.name, a.attribute#,
decode(at.typecode,
9, decode(a.charsetform, 2, 'nvarchar2', ato.name),
96, decode(a.charsetform, 2, 'nchar', ato.name),
112, decode(a.charsetform, 2, 'nclob', ato.name),
ato.name) at_typecode,
decode(bitand(at.properties, 64), 64, null, atu.name) at_properties, 'null' c_null,
a.attr_toid,
decode(bitand(t.properties, 65536), 65536, 'no', 'yes') t_properties,
su.name su_name, so.name so_name
from sys.attribute$ a,
sys.type$ t, sys.type$ at,
sys."_CURRENT_EDITION_OBJ" ato, sys.user$ atu,
sys."_CURRENT_EDITION_OBJ" so, sys.user$ su
where 1=2;
alter table attr_rc_pickler_table modify (su_name null);
alter table attr_rc_pickler_table modify (so_name null);
create table subtype_rc_pickler_table as
select /*+ noparallel */ 1 c1, u.name uname, o.name oname, o.oid$
from sys."_CURRENT_EDITION_OBJ" o,
sys.user$ u, sys.type$ t
where 1=2;
alter table subtype_rc_pickler_table modify (uname null);
alter table subtype_rc_pickler_table modify (oname null);
-------------- dbms_pickler.get_type_shape Plsql Call ----------------
create or replace procedure dbms_pkl_get_type_shape(p_typename varchar2, p_insert boolean := false) as
l_ret number;
l_fulltypename varchar2(32767) := p_typename; --'test_obj'
l_typoid raw(32767);
l_version binary_integer;
l_tds long raw(32767);
l_instantiable varchar2(32767);
l_supertype_owner varchar2(32767);
l_supertype_name varchar2(32767);
l_attr_rc dbms_pickler.type_attr_ref_cursor;
l_subtype_rc dbms_pickler.type_subtype_ref_cursor;
type attr_rc_type is table of attr_rc_pickler_table%rowtype index by pls_integer;
l_attr_rc_tab attr_rc_type;
type subtype_rc_type is table of subtype_rc_pickler_table%rowtype index by pls_integer;
l_subtype_rc_tab subtype_rc_type;
begin
l_ret := dbms_pickler.get_type_shape(
l_fulltypename,
l_typoid,
l_version,
l_tds,
l_instantiable,
l_supertype_owner,
l_supertype_name,
l_attr_rc,
l_subtype_rc);
dbms_output.put_line('');
dbms_output.put_line('------ get_type_shape typename = '||p_typename||' ------ ');
dbms_output.put_line('get_type_shape return ='||l_ret);
dbms_output.put_line('get_type_shape fulltypename ='||l_fulltypename);
dbms_output.put_line('get_type_shape typoid ='||l_typoid);
dbms_output.put_line('get_type_shape version ='||l_version);
dbms_output.put_line('get_type_shape tds ='||l_tds);
dbms_output.put_line('get_type_shape instantiable ='||l_instantiable);
dbms_output.put_line('get_type_shape supertype_owner='||l_supertype_owner);
dbms_output.put_line('get_type_shape supertype_name ='||l_supertype_name);
-- not yet fetched, always 0
--dbms_output.put_line('dbms_pickler.get_type_shape l_attr_rc%rowcount ='||l_attr_rc%rowcount);
dbms_output.put_line('------*** type_attr_ref_cursor ***------ ');
--open l_attr_rc(l_typoid); -- already opened
if l_attr_rc%isopen then
dbms_output.put_line('get_type_shape l_attr_rc%isopen ='||'true');
else
dbms_output.put_line('get_type_shape l_attr_rc%isopen ='||'false');
end if;
fetch l_attr_rc bulk collect into l_attr_rc_tab;
dbms_output.put_line('get_type_shape l_attr_rc%rowcount ='||l_attr_rc%rowcount);
if p_insert then
forall i in 1..l_attr_rc_tab.count
insert into attr_rc_pickler_table values l_attr_rc_tab(i);
dbms_output.put_line('attr_rc_pickler_table inserted sql%rowcount ='||sql%rowcount);
commit;
end if;
close l_attr_rc;
dbms_output.put_line('------*** type_subtype_ref_cursor ***------ ');
if l_subtype_rc%isopen then
dbms_output.put_line('get_type_shape l_subtype_rc%isopen ='||'true');
else
dbms_output.put_line('get_type_shape l_subtype_rc%isopen ='||'false');
end if;
fetch l_subtype_rc bulk collect into l_subtype_rc_tab;
dbms_output.put_line('get_type_shape l_subtype_rc%rowcount ='||l_subtype_rc%rowcount);
if p_insert then
forall i in 1..l_subtype_rc_tab.count
insert into subtype_rc_pickler_table values l_subtype_rc_tab(i);
dbms_output.put_line('subtype_rc_pickler_table inserted sql%rowcount ='||sql%rowcount);
commit;
end if;
close l_subtype_rc;
end;
/
-------------- Test Output ----------------
--exec dbms_pkl_get_type_shape('TEST_OBJ');
exec dbms_pkl_get_type_shape('TEST_OBJ', TRUE);
------ get_type_shape typename = TEST_OBJ ------
get_type_shape return =0
get_type_shape fulltypename =K.TEST_OBJ
get_type_shape typoid =7B54200768642599E054005056984D97
get_type_shape version =1
get_type_shape tds =0000001C260100010002002900000000000F0608000701908100002A0007000A
get_type_shape instantiable =YES
get_type_shape supertype_owner=
get_type_shape supertype_name =
------*** type_attr_ref_cursor ***------
get_type_shape l_attr_rc%isopen =true
get_type_shape l_attr_rc%rowcount =2
------*** type_subtype_ref_cursor ***------
get_type_shape l_subtype_rc%isopen =true
get_type_shape l_subtype_rc%rowcount =0
-------------- Select REF CURSOR fetched Result ----------------
select * from attr_rc_pickler_table;
select * from subtype_rc_pickler_table;
3. Type Descriptor Retrieval Performance Fix
Comparing code of 12c deprecated APIs,
StructDescriptor structDP = StructDescriptor.createDescriptor("TEST_OBJ", conn);
ArrayDescriptor arrayDP = ArrayDescriptor.createDescriptor("TEST_OBJ_ARRAY", conn);
oneObj = new STRUCT(structDP, conn, objField);
objArray = new ARRAY(arrayDP, conn, objVector.toArray());
with that of new APIs,
objStruct[i] = conn.createStruct("TEST_OBJ", objField);
objArray = conn.createOracleArray("TEST_OBJ_ARRAY", objStruct);
and refer to the implementation of new APIs in PhysicalConnection.java of ojdbc7.jar:
oracle.jdbc.driver.PhysicalConnection.java
...
public Struct createStruct(String s, Object aobj[])
{
StructDescriptor structdescriptor = StructDescriptor.createDescriptor(s, this);
return new STRUCT(structdescriptor, this, aobj);
}
public Array createOracleArray(String s, Object obj)
{
return createARRAY(s, obj);
}
public ARRAY createARRAY(String s, Object obj)
{
ArrayDescriptor arraydescriptor = ArrayDescriptor.createDescriptor(s, this);
return new ARRAY(arraydescriptor, this, obj);
}
we can see that new API createStruct / createARRAY enclose both createDescriptor and constructor of deprecated API as one call.From code path (call stack), we know the performance is caused by an execution of
"begin :1 := dbms_pickler.get_type_shape(:2,:3,:4,:5,:6,:7,:8,:9,:10); end;"
located in OracleTypeADT.java, which is invoked by TypeDescriptor.getTypeDescriptor following the call of StructDescriptor.createDescriptor.Oracle MOS:
Intermittent Poor Performance Retrieving PL/SQL Object Types (Doc ID 1222553.1)
reveals 3 new API functions in PhysicalConnection.java:
getAllTypeDescriptorsInCurrentSchema()
getTypeDescriptorsFromListInCurrentSchema()
getTypeDescriptorsFromList()
which return TypeDescriptors for the given Objects. In fact, it is a call of query:
"SELECT schema_name, typename, typoid, typecode, version, tds FROM TABLE(private_jdbc.Get_Type_Shape_Info(?))"
where private_jdbc.Get_Type_Shape_Info is implemented as:
dbms_pickler.get_type_shape(schema_name, v_in.typename, v_out.typoid, v_out.version, v_out.tds, lds_temp)
It has only 6 parameters, and is exact the same call as ojdbc5.jar.private_jdbc is probably the MOS mentioned internal PL/SQL package introduced in RDBMS 11.
MOS Doc ID 1222553.1 claims:
Only when using the new API methods will Get_Type_Shape_Info be used behind the scenes rather than DBMS_PICKLER.
However test shows that DBMS_PICKLER is still called because PRIVATE_JDBC.Get_Type_Shape_Info delegates the call to DBMS_PICKLER.get_type_shape, but only with 6 parameters same as with ojdbc5.jar. It can be confirmed with HProfile.
Therefore, if we use the deprecated API with getTypeDescriptorsFromList(), the perfromance is back to ojdbc5.jar.
See appended Java Code:
Test Code: Java (Type Descriptor Retrieval Performance Fix by getTypeDescriptorsFromList)
However, with 12c new API createStruct / createARRAY, there seems no way to call getTypeDescriptorsFromList() because both createDescriptor and constructor are cemented in one single call.
One can also use dtrace to get more info about subroutine: pckltds in the call:
dbms_pickler.get_type_shape
---> dbms_pickler.get_descriptors
---> external name "pckltds"
dtrace -w -n 'pid$target::*pckltds*:entry {@[pid, ustack(20, 0)] = count() ; stop(); exit(0);}' -p 28016
4. Test Code: DB
drop table test_obj_tab;
drop type test_obj_array force;
drop type test_obj force;
create or replace type test_obj as object (
id number(8)
,name varchar2(100));
/
create or replace type test_obj_array as table of test_obj;
/
create table test_obj_tab(id number(8), name varchar2(100), ins_time date);
select * from test_obj_tab order by ins_time desc;
create or replace procedure trc_start as
l_ident_trc varchar2(100);
l_ident_hpf varchar2(100);
begin
l_ident_trc := 'dbms_pickler_trc_'||to_char(sysdate, 'HH24_MI_SS');
l_ident_hpf := 'dbms_pickler_hpf_'||to_char(sysdate, 'HH24_MI_SS')||'.hpf';
dbms_application_info.set_module('ksun_mod1', 'ksun_act1');
execute immediate 'alter session set tracefile_identifier=''' || l_ident_trc || '''';
dbms_monitor.session_trace_enable(waits=>true, binds=>true);
sys.dbms_hprof.start_profiling('HPROF_DIR', l_ident_hpf);
end;
/
create or replace procedure trc_stop as
l_ret binary_integer;
l_intval integer;
l_strval varchar2(100);
begin
sys.dbms_hprof.stop_profiling;
l_ret := dbms_utility.get_parameter_value('session_cached_cursors', intval => l_intval, strval => l_strval);
dbms_output.put_line('l_intval='||l_intval||', l_strval='||l_strval);
execute immediate 'alter session set session_cached_cursors = 0';
dbms_monitor.session_trace_disable;
execute immediate 'alter session set session_cached_cursors = '||l_intval;
end;
/
create or replace function ins_tab(p_array test_obj_array) return number as
l_rowcount integer;
begin
insert into test_obj_tab select t.id, t.name, sysdate from table(cast (p_array as test_obj_array)) t;
l_rowcount := sql%rowcount;
commit;
return l_rowcount;
end;
/
5. Test Code: Java (12c deprecated oracle.sql.STRUCT, oracle.sql.ARRAY)
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.jdbc.OracleTypes;
// OracleJDBC jdbcURL loopCNT objCNT
// OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5
public class OracleJDBC {
static String TRCSTART = "begin trc_start; end;";
static String TRCSTOP = "begin trc_stop; end;";
static String INSTAB = "begin :1 := ins_tab(:2); end;";
public static void main(String[] args) {
String jdbcURL = args[0];
int loopCNT = Integer.parseInt(args[1]);
int objCNT = Integer.parseInt(args[2]);
CallableStatement cStmt;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver ?");
e.printStackTrace();
return;
}
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcURL);
cStmt = conn.prepareCall(TRCSTART);
cStmt.execute();
cStmt.close();
for(int p=0; p < loopCNT; p++){
dbcall(conn, p+1, objCNT);
}
cStmt = conn.prepareCall(TRCSTOP);
cStmt.execute();
cStmt.close();
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
static void dbcall(Connection conn, int loopNo, int objCNT) {
CallableStatement cStmt;
Object [] objField = new Object[2];
STRUCT oneObj = null;
Vector objVector = new Vector();
ARRAY objArray = null;
if (conn != null) {
System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
try {
StructDescriptor structDP = StructDescriptor.createDescriptor("TEST_OBJ", conn);
ArrayDescriptor arrayDP = ArrayDescriptor.createDescriptor("TEST_OBJ_ARRAY", conn);
for(int i=0; i < objCNT; i++){
objField[0] = i;
objField[1] = "Name_" + i;
oneObj = new STRUCT(structDP, conn, objField);
objVector.add(oneObj);
}
objArray = new ARRAY(arrayDP, conn, objVector.toArray());
cStmt = conn.prepareCall(INSTAB);
cStmt.registerOutParameter(1, OracleTypes.INTEGER);
cStmt.setArray(2, objArray);
cStmt.execute();
int insRows = cStmt.getInt(1);
cStmt.close();
System.out.println("ARRAY Size = " + objArray.length() + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
}
System.out.println("END");
} else {
System.out.println("Failed to make connection!");
}
}
}
6. Test Code: Java (12c new API)
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Struct;
import java.sql.Array;
import java.sql.SQLException;
import java.util.Vector;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;
// OracleJDBC jdbcURL loopCNT objCNT
// OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5
public class OracleJDBC {
static String TRCSTART = "begin trc_start; end;";
static String TRCSTOP = "begin trc_stop; end;";
static String INSTAB = "begin :1 := ins_tab(:2); end;";
public static void main(String[] args) {
String jdbcURL = args[0];
int loopCNT = Integer.parseInt(args[1]);
int objCNT = Integer.parseInt(args[2]);
CallableStatement cStmt;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver ?");
e.printStackTrace();
return;
}
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcURL);
cStmt = conn.prepareCall(TRCSTART);
cStmt.execute();
cStmt.close();
for(int p=0; p < loopCNT; p++){
dbcall(conn, p+1, objCNT);
}
cStmt = conn.prepareCall(TRCSTOP);
cStmt.execute();
cStmt.close();
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
static void dbcall(Connection conn, int loopNo, int objCNT) {
CallableStatement cStmt;
Object [] objField = new Object[2];
Struct[] objStruct = new Struct[objCNT];
Array objArray = null;
if (conn != null) {
System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
try {
for(int i=0; i < objCNT; i++){
objField[0] = i;
objField[1] = "Name_" + i;
objStruct[i] = conn.createStruct("TEST_OBJ", objField);
}
objArray = ((OracleConnection) conn).createOracleArray("TEST_OBJ_ARRAY", objStruct);
cStmt = conn.prepareCall(INSTAB);
cStmt.registerOutParameter(1, OracleTypes.INTEGER);
cStmt.setArray(2, objArray);
cStmt.execute();
int insRows = cStmt.getInt(1);
cStmt.close();
//Object[] objArrayIns = (Object[])objArray.getArray();
System.out.println("ARRAY Size = " + ((Object[])objArray.getArray()).length + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
}
System.out.println("SUC END");
} else {
System.out.println("Failed to make connection!");
}
}
}
7. Test Code: Java (Java Stored Procedure in Oracle RDBMS embedded JVM)
create or replace java source named "OracleJDBC" as
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Struct;
import java.sql.Array;
import java.sql.SQLException;
import java.util.Vector;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.driver.OracleDriver;
// dbcallCtrl (3, 5)
public class OracleJDBC {
static String TRCSTART = "begin trc_start; end;";
static String TRCSTOP = "begin trc_stop; end;";
static String INSTAB = "begin :1 := ins_tab(:2); end;";
public static void dbcallCtrl(int loopCNT, int objCNT) throws SQLException{
Connection conn = new OracleDriver().defaultConnection();
CallableStatement cStmt;
try {
cStmt = conn.prepareCall(TRCSTART);
cStmt.execute();
cStmt.close();
for(int p=0; p < loopCNT; p++){
dbcall(conn, p+1, objCNT);
}
cStmt = conn.prepareCall(TRCSTOP);
cStmt.execute();
cStmt.close();
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
}
}
static void dbcall(Connection conn, int loopNo, int objCNT) {
CallableStatement cStmt;
Object [] objField = new Object[2];
Struct[] objStruct = new Struct[objCNT];
Array objArray = null;
if (conn != null) {
System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
try {
for(int i=0; i < objCNT; i++){
objField[0] = i;
objField[1] = "Name_" + i;
objStruct[i] = conn.createStruct("TEST_OBJ", objField);
}
objArray = ((OracleConnection) conn).createOracleArray("TEST_OBJ_ARRAY", objStruct);
cStmt = conn.prepareCall(INSTAB);
cStmt.registerOutParameter(1, OracleTypes.INTEGER);
cStmt.setArray(2, objArray);
cStmt.execute();
int insRows = cStmt.getInt(1);
cStmt.close();
//Object[] objArrayIns = (Object[])objArray.getArray();
System.out.println("ARRAY Size = " + ((Object[])objArray.getArray()).length + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
}
System.out.println("SUC END");
} else {
System.out.println("Failed to make connection!");
}
}
}
/
create or replace procedure dbcallCtrl(loopNo number, objCNT number) as
language java name 'OracleJDBC.dbcallCtrl(int, int)';
/
--set serveroutput on size 50000
--exec dbms_java.set_output(50000);
--exec dbcallCtrl(3, 5);
8. Test Code: Java (Type Descriptor Retrieval Performance Fix by getTypeDescriptorsFromList)
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.sql.TypeDescriptor;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;
// OracleJDBC jdbcURL loopCNT objCNT
// OracleJDBC "jdbc:oracle:thin:k/s@testDB:1521:testDB" 3 5
public class OracleJDBC {
static String TRCSTART = "begin trc_start; end;";
static String TRCSTOP = "begin trc_stop; end;";
static String INSTAB = "begin :1 := ins_tab(:2); end;";
public static void main(String[] args) {
String jdbcURL = args[0];
int loopCNT = Integer.parseInt(args[1]);
int objCNT = Integer.parseInt(args[2]);
CallableStatement cStmt;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver ?");
e.printStackTrace();
return;
}
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcURL);
cStmt = conn.prepareCall(TRCSTART);
cStmt.execute();
cStmt.close();
String[][] schemaAndTypeNamePairs = {{"K","TEST_OBJ"}, {"K","TEST_OBJ_ARRAY"}};
TypeDescriptor[] sqlTypes = ((OracleConnection) conn).getTypeDescriptorsFromList(schemaAndTypeNamePairs);
for(int p=0; p < loopCNT; p++){
dbcall(conn, p+1, objCNT, sqlTypes);
}
cStmt = conn.prepareCall(TRCSTOP);
cStmt.execute();
cStmt.close();
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
static void dbcall(Connection conn, int loopNo, int objCNT, TypeDescriptor[] sqlTypes) {
CallableStatement cStmt;
Object [] objField = new Object[2];
STRUCT oneObj = null;
Vector objVector = new Vector();
ARRAY objArray = null;
if (conn != null) {
System.out.println("You made it, call DBMS_PICKLER to select TYPE info from sys.type$ !");
try {
StructDescriptor structDP = (StructDescriptor)sqlTypes[0];
ArrayDescriptor arrayDP = (ArrayDescriptor)sqlTypes[1];
for(int i=0; i < objCNT; i++){
objField[0] = i;
objField[1] = "Name_" + i;
oneObj = new STRUCT(structDP, conn, objField);
objVector.add(oneObj);
}
objArray = new ARRAY(arrayDP, conn, objVector.toArray());
cStmt = conn.prepareCall(INSTAB);
cStmt.registerOutParameter(1, OracleTypes.INTEGER);
cStmt.setArray(2, objArray);
cStmt.execute();
int insRows = cStmt.getInt(1);
cStmt.close();
System.out.println("ARRAY Size = " + objArray.length() + ", DB Inserted Rows = " + insRows + ", loops = " + loopNo);
} catch (SQLException e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
}
System.out.println("END");
} else {
System.out.println("Failed to make connection!");
}
}
}
