Thursday, May 25, 2017

JDBC, Oracle object/collection, dbms_pickler, NOPARALLEL sys.type$ query

When using the JDBC Thin driver to invoke PL/SQL programs with Oracle objects / collections, we can observe dbms_pickler calls, and two NOPARALLEL hinted sys.type$ queries having the same number of executions as dbms_pickler. These can result in intermittent performance degradation.

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) for 'dbms_pickler_hpf_15_09_56'

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!");
    }
  }
}