(I)-Tracing Methods (II)-Object Type(Part-1) (III)-Object Type(Part-2) (IV)-Sql Executions (IV) (V)-Contentions and Scalability
Oracle extends relational model by introducing object-oriented user-defined flavor: object types (ADTs).
Studied tracing methods in previous Blog, this Blog will discuss Row Cache Object Gets, Row Cache Latch Gets when using object types in Plsql and Java.
The previous Blogs recorded my slow and long journey in understanding Row Cache Objects and their Latches:
1. java stored procedure calls and latch: row cache objects 2. dbms_aq.dequeue - latch: row cache objects on AIX 3. nls_database_parameters, dc_props, latch: row cache objects 4. row cache mutex in Oracle 12.2.0.1.0 5. Oracle ROWCACHE Views and Contents 6. Oracle row cache objects Event: 10222, Dtrace Scripts (I)Note: All tests are done in Oracle 12.1.0.2.
1. Plsql Function with object types
Assume a Plsql Function foo is defined as:
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
(see appended Test Code).
2. Plsql Dynamic Call
Call foo 100 times:
BEGIN :1 := foo(:2, :3, :4); END;
(see Blog: Oracle row cache objects Event: 10222, Dtrace Script (I)):
begin
trc_start(4294967295);
foo_proc(100);
trc_stop;
end;
/
-- 10222 trace level 4294967295, CID 11, 17 in same Addr --
*************** CID Stats with Address ****************
CID = 17 ADDR = 178D70438 CNT = 515
CID = 11 ADDR = 16F1C6A48 CNT = 515
CID = 7 ADDR = 17199E2D0 CNT = 501
*************** CID Stats ****************
CID = 17 CNT = 515 (dc_global_oids)
CID = 11 CNT = 515 (dc_object_ids/dc_objects)
CID = 7 CNT = 528 (dc_user_grants/dc_users)
*************** CID Stats Summary ****************
CNT = 1558
CID = 17 dc_global_oids
CID = 11 dc_object_ids
(renamed as dc_objects in 10g, still visible in v$rowcache_parent.
see MOS: How to Match a Row Cache Object Child Latch to its Row Cache (Doc ID 468334.1))
CID = 7 dc_user_grants
(SUBORDINATE, its PARENT: dc_users)
1 for T_OBJ_RET 1 for T_OBJ_IN 1 for T_OBJ_OUT 2 for T_OBJ_INOUTso 5 dc_object_ids GETs are needed for each foo Call (T_OBJ_INOUT requires 2), 100 Calls resulted in 500 GETs. (see later discussion in Section 4. Plsql Dynamic Call with trace level 15).
Each dc_object_ids GET is started by one dc_global_oids (CID = 17) GET, and terminated by one dc_users (CID = 7) GET. They are processed sequentially as:
dc_global_oids CID = 17 GET dc_object_ids CID = 11 GET dc_users CID = 7 GETIn total, it amounts to 5*3*100=1500 Row Cache Objects GETs for 100 foo Calls, which are:
500 dc_global_oids (CACHE#=17) 500 dc_objects (CACHE#=8) 500 dc_users (CACHE#=10)As we learned, for each Row Cache Objects GET, it requires 3 "latch: row cache objects" GETs.
That is 3*1500 = 4500 Latch GETs for 100 foo Calls.
The 3 consecutive "latch: row cache objects" GETs for one Row Cache Object Get are at 3 "Where" Locations:
kqrpre: find obj kqreqd kqreqd: regetQuery below can also be used to watch Row Cache Objects GETs and the respective "latch: row cache objects" GETs. (restricted to latch#: 8, 9, 10 in Oracle 12.1.0.2)
with rc as (
select min(parameter) rc_parameter, sum(gets) rc_gets, sum(getmisses) rc_getmisses,
decode(substr(parameter, 1, 9), 'dc_users', 8, 'dc_object', 9, 'dc_global', 10) la_child#,
listagg(cache#, ', ') within group (order by cache#) cache#_list,
listagg(parameter, ', ') within group (order by length(parameter)) parameter_list
from v$rowcache where cache# in (7,10, 8,11, 17) and gets > 0
group by substr(parameter, 1, 9))
select latch#, child#, name, gets, misses, sleeps, spin_gets, rc.*, 3*gets RC_theory_gets
from v$latch_children la, rc
where la.latch# = 411 and la.child# in (8, 9, 10)
and la.child# = rc.la_child#
order by la.child#;
select cache#, cache_name, count(distinct address)
from V$ROWCACHE_PARENT
where cache_name like 'dc_objects%'
group by cache#, cache_name order by cache#;
CACHE# CACHE_NAME COUNT(DISTINCTADDRESS)
------ ---------- ----------------------
8 dc_objects 7010
11 dc_objects 810
exec import_10222_trc_proc(1, 'testdb_ora_25675_rco_trc_09_12_29.trc');
select cid, cid_type, cid_subordinate#, cid_parameter, count(*) cnt, sum(elapsed_us) elapsed_us from (
select ts_us - lag(ts_us) over(partition by run order by line_nbr) elapsed_us, t.*
from import_10222_trc t
where run=1
and sql like 'begin :1 := foo(:2, :3, :4); end;%'
order by run, line_nbr)
group by cid, cid_type, cid_subordinate#, cid_parameter order by cnt desc;
CID CID_TYPE CID_SUBORDINATE# CID_PARAMETER CNT ELAPSED_US
--- ------------ ----------------- --------------- ---- -----------
17 PARENT dc_global_oids 500 133632
11 500 94677
7 SUBORDINATE 0 dc_users 500 235832
3. 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
Dtrace output (CID: V$ROWCACHE.CACHE#) shows:
*************** CID Stats with Address ****************
CID = 17 ADDR = 172298F88 CNT = 10
CID = 7 ADDR = 171958160 CNT = 11
CID = 8 ADDR = 16F367C50 CNT = 20
CID = 10 ADDR = 17199E2D0 CNT = 35
CID = 17 ADDR = 17279C738 CNT = 703
CID = 11 ADDR = 16F367C50 CNT = 712
CID = 7 ADDR = 17199E2D0 CNT = 705
*************** CID Stats ****************
CID = 8 CNT = 24
CID = 10 CNT = 43
CID = 17 CNT = 724
CID = 11 CNT = 712
CID = 7 CNT = 716
*************** CID Stats Summary ****************
CNT = 2219
2 for T_OBJ_RET 1 for T_OBJ_IN 2 for T_OBJ_OUT 2 for T_OBJ_INOUTComparing to Plsql Call, only T_OBJ_IN requires 1 GET, all other 3 requires 2 GETs.
So 7 dc_object_ids GETs for each foo Call in Java, 2 more GETs than Plsql.
In total, it amounts to 7*3*100=1500=2100 Row Cache Objects GETs for 100 foo Calls.
And it requires 3*2100 = 6300 Latch GETs for 100 foo Calls.
4. Plsql Dynamic Call with trace level 15
If setting 10222 trace level to 15 (instead of max: 4294967295), and call foo 100 times:
begin
trc_start(15);
foo_proc(100);
trc_stop;
end;
/
-- 10222 trace level 15, CID 11, 17 in different Addr --
*************** CID Stats with Address ****************
CID = 11 ADDR = 16F1C6A48 CNT = 15
CID = 17 ADDR = 178D70438 CNT = 15
CID = 17 ADDR = 17AE5F738 CNT = 100 --T_OBJ_RET
CID = 17 ADDR = 17194DE90 CNT = 100 --T_OBJ_IN
CID = 17 ADDR = 171AB8238 CNT = 100 --T_OBJ_OUT
CID = 17 ADDR = 16EFB4A18 CNT = 200 --T_OBJ_INOUT
CID = 11 ADDR = 16F367C50 CNT = 100 --1 T_OBJ_RET
CID = 11 ADDR = 16F190060 CNT = 100 --1 T_OBJ_IN
CID = 11 ADDR = 16F184B20 CNT = 100 --1 T_OBJ_OUT
CID = 11 ADDR = 16F183A60 CNT = 200 --2 T_OBJ_INOUT
CID = 7 ADDR = 171921A90 CNT = 502
*************** CID Stats ****************
CID = 17 CNT = 515
CID = 11 CNT = 515
CID = 7 CNT = 528
*************** CID Stats Summary ****************
CNT = 1558
all CID 11 in the same addrss for all 4 Object Types; all CID 17 in the same addrss for all 4 Object Types;So this trace level can dictate memory allocation algorithm, probably some powerful logic behind the trace level.
Check statistics of dc_objects in V$ROWCACHE_PARENT again, now it increased to 814 for CACHE#=11 dc_objects. whereas in Section 2, it is 810, exactly 4 more for 4 Object Types.
select cache#, cache_name, count(distinct address)
from V$ROWCACHE_PARENT
where cache_name like 'dc_objects%'
group by cache#, cache_name order by cache#;
CACHE# CACHE_NAME COUNT(DISTINCTADDRESS)
------ ---------- ----------------------
8 dc_objects 7010
11 dc_objects 814
5. Plsql Static Call
If call foo 100 times in the usual static way (see appended code):
begin
trc_start(4294967295);
foo_proc_static(100);
trc_stop;
end;
/
*************** CID Stats with Address ****************
CID = 17 ADDR = 178D70438 CNT = 3
CID = 11 ADDR = 16F1C6A48 CNT = 3
CID = 7 ADDR = 1716FDFB0 CNT = 2
*************** CID Stats ****************
CID = 17 CNT = 3
CID = 11 CNT = 3
CID = 7 CNT = 2
*************** CID Stats Summary ****************
CNT = 8
6. dc_global_oids
Pick one foo execution from trace file, all 4 Binds are underlined with a 32 length hex value.
=====================
PARSING IN CURSOR #18446604434611618256 len=35 dep=0 uid=49 oct=47 lid=49
tim=13815978483655 hv=1325814719 ad='16eff1050' sqlid='8nkt65d7hcnxz'
BEGIN :1 := K.FOO(:2, :3, :4); END;
...
BINDS #18446604434611618256:
Bind#0
oacflg=03 fl2=1000000 frm=00 csi=02 siz=2560 off=0
7807E9A68357082FE054005056984D97
Bind#1
oacflg=03 fl2=1000000 frm=00 csi=02 siz=0 off=640
7807E9A682B5082FE054005056984D97
Bind#2
oacflg=03 fl2=1000000 frm=00 csi=02 siz=0 off=1280
7807E9A682BA082FE054005056984D97
Bind#3
oacflg=03 fl2=1000000 frm=00 csi=02 siz=0 off=1920
77DDFDFCEB484AF6E054005056984D97
kqrpad: new po 17ae5f738 from kqrpre1.1
KQR Parent Cache Object:
kqrReadFromDB : kqrpre1.1 po=17ae5f738 flg=8000 cid=17 eq=16d24be18 idx=0 dsflg=0
...
kqrfrpo: Free po 17ae5f738
select g.*, o.obj#, o.name, o.oid$
from sys.oid$ g,
sys.obj$ o
where g.oid$ in ('7807E9A68357082FE054005056984D97',
'7807E9A682B5082FE054005056984D97',
'7807E9A682BA082FE054005056984D97',
'77DDFDFCEB484AF6E054005056984D97')
and g.obj# = o.obj#;
USER# OID$ OBJ# INDEX# OBJ# NAME OID$
------ -------------------------------- -------- ------- -------- ------------ --------------------------------
1 7807E9A68357082FE054005056984D97 2360168 0 2360168 T_OBJ_RET 7807E9A68357082FE054005056984D97
1 7807E9A682B5082FE054005056984D97 2360169 0 2360169 T_OBJ_IN 7807E9A682B5082FE054005056984D97
1 7807E9A682BA082FE054005056984D97 2360170 0 2360170 T_OBJ_OUT 7807E9A682BA082FE054005056984D97
1 77DDFDFCEB484AF6E054005056984D97 2360171 0 2360171 T_OBJ_INOUT 77DDFDFCEB484AF6E054005056984D97
Therefore dc_global_oids is probably used to map OID$.oid$ to OBJ$.
Since each Bind is 640 length ("off" difference is 640, total "siz=2560"), they are not stored in DC. For each foo execution, they are read into DC from Buffer Cache by "kqrReadFromDB" as "kqrpad: new po", and evicted as "kqrfrpo: Free po" (see later discussion). Hence each dc_object_ids (CID = 11) is preceded by a dc_global_oids (CID = 17).
If run dc_global_oids query in Blog: Oracle ROWCACHE Views and Contents, one can also see those 4 Objects.
7. Row Cache Internals: kqrReadFromDB and kqrfrpo
10222 trace shows that Row Cache is not able to cache those Object Types and the call statement. Each time it needs to invoke "kqrReadFromDB" to create a new DC row, use it, and then evicted by "kqrfrpo : freed to heap". In the following example, it took 427 us for one such Read.
Since 10046 trace (see next Section) does not show any Disk(Physical) Read, probably "kqrReadFromDB" is a subroutine to read DC row from Buffer Cache. In that case, reading one DC row looks like a single row distilling process from a Buffer Cache Block (e.g 8K), which may contains multiple DC rows.
kqrpre1 : done po=16f1c6a48 cid=11 flg=2 hash=abe278ab 36ab78b6 eq=17179fc10
obobn=2360168 obname=T_OBJ_RET obtyp=13 obsta=1 obflg=0 SQL=begin :1 := test_fun_1(:2, :3, :4); end;
time=1582475964
--===== START kqrpad: new po =====--
--===== Not kept, each time new loaded =====--
kqrpad: new po 17199e2d0 from kqrpre1.1
KQR Parent Cache Object:
pob = 17199e2d0 flg = 00000000 cid = 7 xcb = 0 hbk = 17199e8a8
hbk=[17199e8b8,17199e8b8] hbk=[17199e8d8,17199e8d8] hbk=[17199e8f8,17199e8f8]
own=17199e3a0[17199e3a0,17199e3a0] wat=17199e3b0[17199e3b0,17199e3b0] mod=0
kqrReadFromDB : kqrpre1.2 po=17199e2d0 flg=8000 cid=7 eq=17179f7d0 idx=1 dsflg=0
kqrReadFromDB : kqrpre1.3 po=17199e2d0 flg=8000 cid=7 eq=17179f7d0 idx=0 dsflg=0
kqrpre1 : done po=17199e2d0 cid=7 flg=2 hash=de7751cd 395edb55 eq=17179f7d0 SQL=begin :1 := foo(:2, :3, :4); end; time=1582476252
kqrmupin : kqrpspr2 Unpin po 17199e2d0 cid=7 flg=2 hash=395edb55 time=1582476298
kqrmupin : free po=17199e2d0 flg=2
kqrfrpo: Free po 17199e2d0
KQR Parent Cache Object:
pob = 17199e2d0 flg = 00000002 cid = 7 xcb = 0 hbk = 17199e8a8
hbk=[1800df578,1800df578] hbk=[180055cf8,180055cf8] hbk=[17199e8f8,17199e8f8]
own=17199e3a0[17199e3a0,17199e3a0] wat=17199e3b0[17199e3b0,17199e3b0] mod=0
kqrfrpo : freed to heap po=17199e2d0 time=1582476391
--===== END: kqrfrpo : freed to heap =====--
--===== Elapsed 1582476391-1582475964=427 us =====--
kqrmupin : kqrpspr2 Unpin po 16f1c6a48 cid=11 flg=2 hash=36ab78b6 time=1582476413
8. 10046 Trace file
Here the TKProf output of 10046 Trace file created from above 100 times Java Call in Section 3 for dc_global_oids, dc_objects, and dc_users respectively.
Each recursive query made about 700 logical Reads for 100 foo Executions, no disk access.
============================= 100 foo Executions =============================
SQL ID: 8nkt65d7hcnxz Plan Hash: 0
BEGIN :1 := K.FOO(:2, :3, :4); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.88 0.89 0 132 0 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.88 0.89 0 132 0 100
============================= dc_global_oids =============================
SQL ID: 0gx3b09qrx9f5 Plan Hash: 1964104430
select obj#,index#
from
oid$ where user#=:1 and oid$=:2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 714 0.12 0.12 0 0 0 0
Fetch 714 0.00 0.00 0 2853 0 711
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1429 0.13 0.13 0 2853 0 711
TABLE ACCESS BY INDEX ROWID OID$ (cr=3 pr=0 pw=0 time=12 us cost=2 size=29 card=1)
INDEX UNIQUE SCAN I_OID1 (cr=3 pr=0 pw=0 time=11 us cost=1 size=0 card=1)(object id 501)
============================= dc_objects =============================
SQL ID: 1p5grz1gs7fjq Plan Hash: 813480514
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1,
spare2, spare3, signature, spare7, spare8, spare9
from
obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null
and linkname is null and subname is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 731 0.17 0.17 0 0 0 0
Fetch 731 0.00 0.00 0 2921 0 728
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1463 0.18 0.18 0 2921 0 728
TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=3 pr=0 pw=0 time=15 us cost=2 size=106 card=1)
INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=14 us cost=2 size=0 card=1)(object id 1591124)
********************************************************************************
SQL ID: 87gaftwrm2h68 Plan Hash: 1072382624
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname
from
obj$ o where o.obj#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 712 0.07 0.07 0 0 0 0
Fetch 712 0.00 0.00 0 2848 0 712
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1425 0.08 0.08 0 2848 0 712
TABLE ACCESS BY INDEX ROWID BATCHED OBJ$ (cr=4 pr=0 pw=0 time=13 us cost=2 size=51 card=1)
INDEX RANGE SCAN I_OBJ1 (cr=3 pr=0 pw=0 time=9 us cost=2 size=0 card=1)(object id 1591123)
============================= dc_users =============================
SQL ID: 2tkw12w5k68vd Plan Hash: 1457651150
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,
decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,
ext_username,spare2
from
user$ where name=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 755 0.00 0.00 0 0 0 0
Execute 755 0.07 0.07 0 0 0 0
Fetch 755 0.00 0.00 0 2265 0 755
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2265 0.08 0.08 0 2265 0 755
TABLE ACCESS BY INDEX ROWID USER$ (cr=3 pr=0 pw=0 time=34 us cost=1 size=314 card=1)
INDEX UNIQUE SCAN I_USER1 (cr=2 pr=0 pw=0 time=18 us cost=1 size=0 card=1)(object id 1591161)
********************************************************************************
SQL ID: 4kt3cun8s5fp5 Plan Hash: 2709293936
select name,password,datats#,tempts#,type#,defrole,resource$, ptime,
decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,
ext_username,spare2
from
user$ where user#=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 714 0.00 0.00 0 0 0 0
Execute 714 0.07 0.07 0 0 0 0
Fetch 714 0.00 0.00 0 2142 0 714
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2142 0.08 0.08 0 2142 0 714
TABLE ACCESS CLUSTER USER$ (cr=3 pr=0 pw=0 time=11 us cost=1 size=314 card=1)
INDEX UNIQUE SCAN I_USER# (cr=2 pr=0 pw=0 time=5 us cost=1 size=0 card=1)(object id 1591131)
********************************************************************************
SQL ID: 8t373z5u9ztup Plan Hash: 0
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 4 0.00 0.00 0 0 0 0
Execute 4 0.02 0.03 3 20 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.03 0.04 3 20 0 4
********************************************************************************
9. Summary
For each foo Call in Dynamic Plsql, there are 5 dc_object_ids (CID = 11) GETs as follows:
1 for T_OBJ_RET 1 for T_OBJ_IN 1 for T_OBJ_OUT 2 for T_OBJ_INOUTEach dc_object_ids GET is started by one dc_global_oids (CID = 17) GET, and terminated by one dc_users (CID = 7) GET.
So total 5*3 = 15 Row Cache Objects GETs, which demand 15*3=45 Row Cache Latch GETs.
In case of Java, however, for each foo Call, there are 7 dc_object_ids (CID = 11) GETs as follows:
2 for T_OBJ_RET 1 for T_OBJ_IN 2 for T_OBJ_OUT 2 for T_OBJ_INOUTSo total 7*3 = 21 Row Cache Objects GETs, which demand 21*3=63 Row Cache Latch GETs.
10. Plsql Test Code
--=========== object types parameters (IN, OUT, IN OUT) ===========--
create or replace type t_obj_ret force as object (id number, name varchar2(30));
/
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));
/
--=========== function foo ===========--
create or replace function foo (
p_in in t_obj_in := null
,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
if p_in is null then
l_ret := t_obj_ret(9876, 'p_in is NULL');
else
l_ret := t_obj_ret(p_in.id + 112, p_in.name);
end if;
p_out := t_obj_out(p_inout.id, p_inout.name);
p_inout.id := l_ret.id;
return l_ret;
end;
/
--=========== Plsql Dynamic Call ===========--
create or replace procedure foo_proc (p_cnt number) as
l_stmt varchar2(100);
l_ret t_obj_ret;
l_in t_obj_in;
l_out t_obj_out;
l_inout t_obj_inout;
begin
l_in := t_obj_in(1122, 'T_OBJ_IN');
--l_in := null; -- call by NULL, same cid GETs
l_inout := t_obj_inout(1144, 'T_OBJ_INOUT');
l_stmt := q'[begin :1 := foo(:2, :3, :4); end;]';
for i in 1..p_cnt loop
execute immediate l_stmt
using OUT l_ret, IN l_in, OUT l_out, IN OUT l_inout;
end loop;
dbms_output.put_line('l_ret.id=' ||l_ret.id);
end;
/
-- exec foo_proc(100);
--=========== Plsql Static Call ===========--
-- less than 10 CID Calls
create or replace procedure foo_proc_static (p_cnt number) as
l_ret t_obj_ret;
l_in t_obj_in;
l_out t_obj_out;
l_inout t_obj_inout;
begin
l_in := t_obj_in(1122, 'T_OBJ_IN');
l_inout := t_obj_inout(1144, 'T_OBJ_INOUT');
for i in 1..p_cnt loop
l_ret := foo(l_in, l_out, l_inout);
end loop;
dbms_output.put_line('l_ret.id=' ||l_ret.id);
end;
/
-- exec foo_proc_static(100);
--=========== Trace start and stop ===========--
create or replace procedure trc_start(p_level number := 4294967295, p_incl number := 3) as
l_ident_trc varchar2(100);
l_ident_hpf varchar2(100);
begin
l_ident_trc := 'rco_trc_'||to_char(sysdate, 'HH24_MI_SS');
l_ident_hpf := 'rco_hpf_'||to_char(sysdate, 'HH24_MI_SS')||'.hpf';
dbms_application_info.set_module('ksun_mod1', 'ksun_act1');
execute immediate q'[alter session set max_dump_file_size = UNLIMITED]';
execute immediate 'alter session set tracefile_identifier=''' || l_ident_trc || '''';
if p_incl >= 2 then
dbms_monitor.session_trace_enable(waits=>true, binds=>true);
end if;
if p_incl >= 3 then
sys.dbms_hprof.start_profiling('HPROF_DIR', l_ident_hpf);
end if;
if p_incl >= 1 then
execute immediate
'alter session set events ''10222 trace name context forever, level ' || p_level || '''';
end if;
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 q'[alter session set events '10222 trace name context off']';
execute immediate 'alter session set session_cached_cursors = '||l_intval;
end;
/
11. Java Test Code
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.ResultSet;
import java.math.BigDecimal;
import java.util.Vector;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
// RCOObjTypeJDBC1
// 0: (no trace, wait); 1: (trace, wait); 2: (no trace, no wait)
// RCOObjTypeJDBC1 "jdbc:oracle:thin:k/s@testDB:1521:testDB" 100 0
public class RCOObjTypeJDBC1 {
static String TRCSTART = "begin trc_start; end;";
static String TRCSTOP = "begin trc_stop; end;";
// parameters(IN, OUT, IN OUT) different Types
static String TESTSTMT = "BEGIN :1 := K.FOO(:2, :3, :4); END;";
public static void main(String[] args) {
String jdbcURL = args[0];
int loopCNT = Integer.parseInt(args[1]);
int trc = Integer.parseInt(args[2]);
Connection conn = null;
CallableStatement cStmt;
for(int i=0; i < 1; i++){
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver ?");
e.printStackTrace();
return;
}
try {
conn = DriverManager.getConnection(jdbcURL);
if (trc < 2) {
Thread.sleep(30*1000); // wait to prepare dtrace
}
if (trc == 1) {
cStmt = conn.prepareCall(TRCSTART);
cStmt.execute();
cStmt.close();
}
dbcall(conn, loopCNT);
if (trc == 1) {
cStmt = conn.prepareCall(TRCSTOP);
cStmt.execute();
cStmt.close();
}
conn.close();
} catch (Exception e) {
System.out.println("Connection Failed! Check output console");
e.printStackTrace();
return;
}
}
}
static void dbcall(Connection conn, int loopNo) {
CallableStatement cStmt;
if (conn != null) {
try {
cStmt = conn.prepareCall(TESTSTMT);
StructDescriptor dpIn = StructDescriptor.createDescriptor("K.T_OBJ_IN", conn);
StructDescriptor dpInOut = StructDescriptor.createDescriptor("K.T_OBJ_INOUT", conn);
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 < loopNo; i++){
cStmt.registerOutParameter(1, OracleTypes.STRUCT, "K.T_OBJ_RET");
//cStmt.registerOutParameter(1, OracleTypes.STRUCT, "K.T_OBJ_RET_SUPER");
cStmt.setObject(2, objIn);
//call by NULL, same cid GETs
//cStmt.setNull(2, OracleTypes.STRUCT, "K.T_OBJ_IN");
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("objOut.id =" + idVal.intValue());
cStmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
} else {
System.out.println("Failed to make connection!");
}
}
}
