I was recently asked about handling string aggregation using XMLAGG rather than using Oracle Data Cartridge Interface (ODCI).
Set up a test case to benchmark this phenomena.
rajesh@ORA10GR2> create or replace type str_agg as object
2 (
3 data SYS.ODCIVARCHAR2LIST,
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT str_agg )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT str_agg ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN str_agg,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT str_agg,
22 ctx2 IN str_agg)
23 return number
24 );
25 /
2 (
3 data SYS.ODCIVARCHAR2LIST,
4
5 static function
6 ODCIAggregateInitialize(sctx IN OUT str_agg )
7 return number,
8
9 member function
10 ODCIAggregateIterate(self IN OUT str_agg ,
11 value IN varchar2 )
12 return number,
13
14 member function
15 ODCIAggregateTerminate(self IN str_agg,
16 returnValue OUT varchar2,
17 flags IN number)
18 return number,
19
20 member function
21 ODCIAggregateMerge(self IN OUT str_agg,
22 ctx2 IN str_agg)
23 return number
24 );
25 /
Type created.
Elapsed: 00:00:01.62
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace type body str_agg
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT str_agg)
5 return number
6 is
7 begin
8 sctx := str_agg( SYS.ODCIVARCHAR2LIST() );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT str_agg,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 data.extend;
18 data(data.count) := value;
19 return ODCIConst.Success;
20 end;
21
22 member function ODCIAggregateTerminate(self IN str_agg,
23 returnValue OUT varchar2
24 flags IN number)
25 return number
26 is
27 l_data varchar2(4000);
28 begin
29 for x in ( select column_value from TABLE(data) order by 1
30 loop
31 l_data := l_data || ',' || x.column_value;
32 end loop;
33 returnValue := ltrim(l_data,',');
34 return ODCIConst.Success;
35 end;
36
37 member function ODCIAggregateMerge(self IN OUT str_agg,
38 ctx2 IN str_agg)
39 return number
40 is
41 begin
42 for i in 1 .. ctx2.data.count
43 loop
44 data.extend;
45 data(data.count) := ctx2.data(i);
46 end loop;
47 return ODCIConst.Success;
48 end;
49 end;
50 /
rajesh@ORA10GR2>
rajesh@ORA10GR2> create or replace type body str_agg
2 is
3
4 static function ODCIAggregateInitialize(sctx IN OUT str_agg)
5 return number
6 is
7 begin
8 sctx := str_agg( SYS.ODCIVARCHAR2LIST() );
9 return ODCIConst.Success;
10 end;
11
12 member function ODCIAggregateIterate(self IN OUT str_agg,
13 value IN varchar2 )
14 return number
15 is
16 begin
17 data.extend;
18 data(data.count) := value;
19 return ODCIConst.Success;
20 end;
21
22 member function ODCIAggregateTerminate(self IN str_agg,
23 returnValue OUT varchar2
24 flags IN number)
25 return number
26 is
27 l_data varchar2(4000);
28 begin
29 for x in ( select column_value from TABLE(data) order by 1
30 loop
31 l_data := l_data || ',' || x.column_value;
32 end loop;
33 returnValue := ltrim(l_data,',');
34 return ODCIConst.Success;
35 end;
36
37 member function ODCIAggregateMerge(self IN OUT str_agg,
38 ctx2 IN str_agg)
39 return number
40 is
41 begin
42 for i in 1 .. ctx2.data.count
43 loop
44 data.extend;
45 data(data.count) := ctx2.data(i);
46 end loop;
47 return ODCIConst.Success;
48 end;
49 end;
50 /
Type body created.
Elapsed: 00:00:00.43
rajesh@ORA10GR2>
rajesh@ORA10GR2> CREATE or replace
2 FUNCTION stragg_fnc(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING str_agg;
5 /
rajesh@ORA10GR2>
rajesh@ORA10GR2> CREATE or replace
2 FUNCTION stragg_fnc(input varchar2 )
3 RETURN varchar2
4 PARALLEL_ENABLE AGGREGATE USING str_agg;
5 /
Function created.
Elapsed: 00:00:00.18
rajesh@ORA10GR2> create table t
2 nologging
3 as
4 select ceil(level/4) as x,
5 case when mod(level,4) = 0 then 'A'
6 when mod(level,4) = 1 then 'B'
7 when mod(level,4) = 2 then 'C'
8 when mod(level,4) = 3 then 'D'
9 end as val
10 from dual
11 connect by level <= 500000;
rajesh@ORA10GR2> create table t
2 nologging
3 as
4 select ceil(level/4) as x,
5 case when mod(level,4) = 0 then 'A'
6 when mod(level,4) = 1 then 'B'
7 when mod(level,4) = 2 then 'C'
8 when mod(level,4) = 3 then 'D'
9 end as val
10 from dual
11 connect by level <= 500000;
Table created.
Elapsed: 00:00:02.75
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
rajesh@ORA10GR2>
rajesh@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.68
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
rajesh@ORA10GR2>
exec runstats_pkg.rs_start;
select x,stragg_fnc(val)
from t
group by x;
from t
group by x;
exec runstats_pkg.rs_middle;
select x,
rtrim(xmlagg(xmlelement(e, val || ',')).extract ('//text()'), ',') enames
from T
group by x;
rtrim(xmlagg(xmlelement(e, val || ',')).extract ('//text()'), ',') enames
from T
group by x;
exec runstats_pkg.rs_stop;
RunStats results show's me this.
rajesh@ORA10GR2> exec runstats_pkg.rs_stop;
Run1 ran in 13372 hsecs
Run2 ran in 22016 hsecs
run 1 ran in 60.74% of the time
Run1 ran in 13372 hsecs
Run2 ran in 22016 hsecs
run 1 ran in 60.74% of the time
Name Run1 Run2 Diff
STAT...global enqueue get time 0 1 1
STAT...calls to kcmgcs 4 5 1
STAT...calls to kcmgas 1 0 -1
STAT...redo entries 9 10 1
STAT...redo ordering marks 1 0 -1
STAT...redo subscn max counts 1 0 -1
STAT...gc local grants 1 0 -1
STAT...workarea executions - o 9 8 -1
STAT...parse time cpu 1 2 1
STAT...sorts (rows) 1,049,827 1,049,826 -1
LATCH.session switching 2 3 1
LATCH.SGA IO buffer pool latch 0 1 1
LATCH.ktm global data 1 0 -1
LATCH.global KZLD latch for me 0 1 1
LATCH.library cache pin alloca 1 2 1
LATCH.SQL memory manager latch 0 1 1
LATCH.ncodef allocation latch 2 3 1
STAT...messages sent 2 0 -2
STAT...session cursor cache hi 4 2 -2
STAT...sorts (memory) 4 2 -2
LATCH.event group latch 0 2 2
LATCH.gcs remastering latch 0 2 2
LATCH.FAL subheap alocation 3 5 2
LATCH.FAL request queue 3 5 2
LATCH.list of block allocation 0 2 2
LATCH.Shared B-Tree 4 2 -2
LATCH.kwqbsn:qsga 4 2 -2
LATCH.JS Sh mem access 0 2 2
STAT...ges messages sent 2 5 3
STAT...free buffer requested 7 10 3
STAT...parse count (total) 8 5 -3
LATCH.sequence cache 0 3 3
STAT...opened cursors current 5 1 -4
STAT...enqueue releases 6 2 -4
STAT...shared hash latch upgra 1 5 4
STAT...gc current blocks recei 1 5 4
STAT...index scans kdiixs1 4 0 -4
LATCH.process group creation 0 4 4
LATCH.FIB s.o chain latch 0 4 4
LATCH.OS process: request allo 0 4 4
LATCH.ges process table freeli 0 4 4
LATCH.archive control 4 8 4
LATCH.user lock 0 4 4
LATCH.ASM map operation freeli 0 4 4
STAT...enqueue requests 7 2 -5
STAT...gcs messages sent 2 7 5
STAT...consistent changes 37 32 -5
LATCH.dml lock allocation 2 7 5
LATCH.JS mem alloc latch 0 5 5
LATCH.JS queue access latch 0 5 5
STAT...db block changes 48 42 -6
LATCH.channel handle pool latc 0 6 6
LATCH.OS process 0 6 6
LATCH.library cache lock alloc 19 13 -6
LATCH.KFMD SGA 0 6 6
STAT...global enqueue gets syn 13 20 7
STAT...global enqueue releases 13 20 7
STAT...no work - consistent re 832 825 -7
STAT...opened cursors cumulati 13 5 -8
STAT...bytes sent via SQL*Net 2,025,450 2,025,441 -9
LATCH.business card 4 14 10
STAT...db block gets 41 28 -13
STAT...db block gets from cach 41 28 -13
LATCH.FOB s.o list latch 0 13 13
LATCH.parameter list 7 22 15
LATCH.JS broadcast load blnc l 27 45 18
LATCH.resmgr:schema config 20 0 -20
LATCH.ges deadlock list 38 60 22
LATCH.JS broadcast add buf lat 30 52 22
LATCH.JS broadcast drop buf la 30 52 22
STAT...redo size 2,964 2,940 -24
STAT...undo change vector size 2,196 2,220 24
LATCH.ges resource scan list 28 52 24
LATCH.KMG MMAN ready and start 45 73 28
LATCH.error message lists 14 43 29
LATCH.session timer 47 76 29
LATCH.transaction branch alloc 47 76 29
LATCH.lgwr LWN SCN 45 76 31
LATCH.mostly latch-free SCN 45 76 31
LATCH.OS process allocation 45 77 32
LATCH.archive process latch 51 83 32
LATCH.Consistent RBA 40 72 32
LATCH.ASM network background l 54 88 34
LATCH.ksuosstats global area 49 14 -35
LATCH.post/wait queue 0 50 50
LATCH.ASM db client latch 91 148 57
LATCH.resmgr group change latc 113 49 -64
STAT...bytes received via SQL* 10,136 10,201 65
LATCH.name-service pending que 130 198 68
STAT...workarea memory allocat 25 -45 -70
LATCH.parallel query stats 105 32 -73
LATCH.PL/SQL warning settings 102 21 -81
LATCH.compile environment latc 99 13 -86
LATCH.resmgr:actses change gro 99 13 -86
LATCH.redo allocation 130 224 94
LATCH.name-service memory obje 178 296 118
LATCH.query server freelists 216 79 -137
LATCH.resmgr:free threads list 198 26 -172
LATCH.dummy allocation 198 26 -172
LATCH.parameter table allocati 198 26 -172
LATCH.session state list latch 198 24 -174
LATCH.undo global data 77 261 184
LATCH.resmgr:active threads 218 26 -192
LATCH.longop free list parent 329 532 203
LATCH.process queue 319 99 -220
LATCH.ges process hash list 112 372 260
LATCH.session idle bit 2,752 2,442 -310
LATCH.client/application info 541 193 -348
LATCH.queued dump request 669 1,101 432
LATCH.message pool operations 625 137 -488
LATCH.library cache lock 1,860 1,267 -593
LATCH.parallel query alloc buf 844 228 -616
LATCH.file cache latch 0 715 715
LATCH.ges resource table freel 143 869 726
LATCH.threshold alerts latch 3 730 727
LATCH.ges timeout list 44 805 761
LATCH.process allocation 1,229 2,040 811
LATCH.session allocation 1,007 175 -832
LATCH.name-service namespace b 1,342 2,254 912
LATCH.name-service request que 1,491 2,428 937
LATCH.ges caches resource list 1,940 3,121 1,181
LATCH.JS queue state obj latch 1,782 2,970 1,188
LATCH.gcs opaque info freelist 9,748 11,084 1,336
STAT...recursive cpu usage 1,738 21 -1,717
LATCH.SQL memory manager worka 3,085 5,056 1,971
LATCH.ges domain table 4,022 6,108 2,086
STAT...DB time 4,784 6,895 2,111
LATCH.KJC message pool free li 3,205 5,596 2,391
LATCH.ges enqueue table freeli 4,304 6,789 2,485
STAT...CPU used when call star 4,254 6,838 2,584
STAT...CPU used by this sessio 4,254 6,839 2,585
LATCH.KFK SGA context latch 5,681 8,894 3,213
LATCH.ges group table 5,928 9,165 3,237
LATCH.sort extent pool 7,512 12,011 4,499
LATCH.enqueues 7,593 12,456 4,863
LATCH.active checkpoint queue 9,026 14,493 5,467
LATCH.redo writing 9,241 14,859 5,618
LATCH.ges resource hash list 7,309 13,422 6,113
LATCH.active service list 7,006 746 -6,260
LATCH.ges process parent latch 8,727 15,286 6,559
LATCH.enqueue hash chains 11,088 18,105 7,017
LATCH.transaction allocation 7,200 14,402 7,202
STAT...Elapsed Time 13,391 22,034 8,643
LATCH.multiblock read objects 25,342 38,017 12,675
LATCH.channel operations paren 15,999 32,736 16,737
LATCH.messages 27,444 45,354 17,910
LATCH.object queue header heap 36,106 57,968 21,862
LATCH.process queue reference 57,824 84,210 26,386
LATCH.gcs shadows freelist 36,199 69,637 33,438
LATCH.simulator hash latch 120,246 189,659 69,413
LATCH.checkpoint queue latch 154,709 248,320 93,611
LATCH.gcs resource freelist 112,884 210,168 97,284
LATCH.cache buffers lru chain 157,487 265,648 108,161
STAT...execute count 125,009 5 -125,004
STAT...session uga memory 392,448 261,632 -130,816
LATCH.KJCT flow control latch 245,838 425,612 179,774
LATCH.ASM map operation hash t 308,505 522,227 213,722
STAT...index fetch by key 250,890 21 -250,869
STAT...rows fetched via callba 250,890 21 -250,869
STAT...table fetch by rowid 250,893 21 -250,872
STAT...calls to get snapshot s 375,902 28 -375,874
LATCH.gcs resource hash 648,925 1,074,796 425,871
LATCH.object queue header oper 724,401 1,153,719 429,318
LATCH.KCL gc element parent la 636,220 1,077,684 441,464
STAT...buffer is not pinned co 501,786 42 -501,744
STAT...recursive calls 625,057 4 -625,053
LATCH.cache buffers chains 2,977,887 3,713,272 735,385
LATCH.gcs partitioned table ha 1,050,572 1,789,356 738,784
STAT...consistent gets - exami 752,677 64 -752,613
STAT...consistent gets 753,519 900 -752,619
STAT...consistent gets from ca 753,519 900 -752,619
STAT...session logical reads 753,560 928 -752,632
LATCH.shared pool simulator 129,635 882,814 753,179
LATCH.library cache pin 4,104 1,750,700 1,746,596
LATCH.kokc descriptor allocati 2 1,746,654 1,746,652
LATCH.shared pool 2,465 2,624,667 2,622,202
LATCH.library cache 7,512 2,630,087 2,622,575
STAT...global enqueue get time 0 1 1
STAT...calls to kcmgcs 4 5 1
STAT...calls to kcmgas 1 0 -1
STAT...redo entries 9 10 1
STAT...redo ordering marks 1 0 -1
STAT...redo subscn max counts 1 0 -1
STAT...gc local grants 1 0 -1
STAT...workarea executions - o 9 8 -1
STAT...parse time cpu 1 2 1
STAT...sorts (rows) 1,049,827 1,049,826 -1
LATCH.session switching 2 3 1
LATCH.SGA IO buffer pool latch 0 1 1
LATCH.ktm global data 1 0 -1
LATCH.global KZLD latch for me 0 1 1
LATCH.library cache pin alloca 1 2 1
LATCH.SQL memory manager latch 0 1 1
LATCH.ncodef allocation latch 2 3 1
STAT...messages sent 2 0 -2
STAT...session cursor cache hi 4 2 -2
STAT...sorts (memory) 4 2 -2
LATCH.event group latch 0 2 2
LATCH.gcs remastering latch 0 2 2
LATCH.FAL subheap alocation 3 5 2
LATCH.FAL request queue 3 5 2
LATCH.list of block allocation 0 2 2
LATCH.Shared B-Tree 4 2 -2
LATCH.kwqbsn:qsga 4 2 -2
LATCH.JS Sh mem access 0 2 2
STAT...ges messages sent 2 5 3
STAT...free buffer requested 7 10 3
STAT...parse count (total) 8 5 -3
LATCH.sequence cache 0 3 3
STAT...opened cursors current 5 1 -4
STAT...enqueue releases 6 2 -4
STAT...shared hash latch upgra 1 5 4
STAT...gc current blocks recei 1 5 4
STAT...index scans kdiixs1 4 0 -4
LATCH.process group creation 0 4 4
LATCH.FIB s.o chain latch 0 4 4
LATCH.OS process: request allo 0 4 4
LATCH.ges process table freeli 0 4 4
LATCH.archive control 4 8 4
LATCH.user lock 0 4 4
LATCH.ASM map operation freeli 0 4 4
STAT...enqueue requests 7 2 -5
STAT...gcs messages sent 2 7 5
STAT...consistent changes 37 32 -5
LATCH.dml lock allocation 2 7 5
LATCH.JS mem alloc latch 0 5 5
LATCH.JS queue access latch 0 5 5
STAT...db block changes 48 42 -6
LATCH.channel handle pool latc 0 6 6
LATCH.OS process 0 6 6
LATCH.library cache lock alloc 19 13 -6
LATCH.KFMD SGA 0 6 6
STAT...global enqueue gets syn 13 20 7
STAT...global enqueue releases 13 20 7
STAT...no work - consistent re 832 825 -7
STAT...opened cursors cumulati 13 5 -8
STAT...bytes sent via SQL*Net 2,025,450 2,025,441 -9
LATCH.business card 4 14 10
STAT...db block gets 41 28 -13
STAT...db block gets from cach 41 28 -13
LATCH.FOB s.o list latch 0 13 13
LATCH.parameter list 7 22 15
LATCH.JS broadcast load blnc l 27 45 18
LATCH.resmgr:schema config 20 0 -20
LATCH.ges deadlock list 38 60 22
LATCH.JS broadcast add buf lat 30 52 22
LATCH.JS broadcast drop buf la 30 52 22
STAT...redo size 2,964 2,940 -24
STAT...undo change vector size 2,196 2,220 24
LATCH.ges resource scan list 28 52 24
LATCH.KMG MMAN ready and start 45 73 28
LATCH.error message lists 14 43 29
LATCH.session timer 47 76 29
LATCH.transaction branch alloc 47 76 29
LATCH.lgwr LWN SCN 45 76 31
LATCH.mostly latch-free SCN 45 76 31
LATCH.OS process allocation 45 77 32
LATCH.archive process latch 51 83 32
LATCH.Consistent RBA 40 72 32
LATCH.ASM network background l 54 88 34
LATCH.ksuosstats global area 49 14 -35
LATCH.post/wait queue 0 50 50
LATCH.ASM db client latch 91 148 57
LATCH.resmgr group change latc 113 49 -64
STAT...bytes received via SQL* 10,136 10,201 65
LATCH.name-service pending que 130 198 68
STAT...workarea memory allocat 25 -45 -70
LATCH.parallel query stats 105 32 -73
LATCH.PL/SQL warning settings 102 21 -81
LATCH.compile environment latc 99 13 -86
LATCH.resmgr:actses change gro 99 13 -86
LATCH.redo allocation 130 224 94
LATCH.name-service memory obje 178 296 118
LATCH.query server freelists 216 79 -137
LATCH.resmgr:free threads list 198 26 -172
LATCH.dummy allocation 198 26 -172
LATCH.parameter table allocati 198 26 -172
LATCH.session state list latch 198 24 -174
LATCH.undo global data 77 261 184
LATCH.resmgr:active threads 218 26 -192
LATCH.longop free list parent 329 532 203
LATCH.process queue 319 99 -220
LATCH.ges process hash list 112 372 260
LATCH.session idle bit 2,752 2,442 -310
LATCH.client/application info 541 193 -348
LATCH.queued dump request 669 1,101 432
LATCH.message pool operations 625 137 -488
LATCH.library cache lock 1,860 1,267 -593
LATCH.parallel query alloc buf 844 228 -616
LATCH.file cache latch 0 715 715
LATCH.ges resource table freel 143 869 726
LATCH.threshold alerts latch 3 730 727
LATCH.ges timeout list 44 805 761
LATCH.process allocation 1,229 2,040 811
LATCH.session allocation 1,007 175 -832
LATCH.name-service namespace b 1,342 2,254 912
LATCH.name-service request que 1,491 2,428 937
LATCH.ges caches resource list 1,940 3,121 1,181
LATCH.JS queue state obj latch 1,782 2,970 1,188
LATCH.gcs opaque info freelist 9,748 11,084 1,336
STAT...recursive cpu usage 1,738 21 -1,717
LATCH.SQL memory manager worka 3,085 5,056 1,971
LATCH.ges domain table 4,022 6,108 2,086
STAT...DB time 4,784 6,895 2,111
LATCH.KJC message pool free li 3,205 5,596 2,391
LATCH.ges enqueue table freeli 4,304 6,789 2,485
STAT...CPU used when call star 4,254 6,838 2,584
STAT...CPU used by this sessio 4,254 6,839 2,585
LATCH.KFK SGA context latch 5,681 8,894 3,213
LATCH.ges group table 5,928 9,165 3,237
LATCH.sort extent pool 7,512 12,011 4,499
LATCH.enqueues 7,593 12,456 4,863
LATCH.active checkpoint queue 9,026 14,493 5,467
LATCH.redo writing 9,241 14,859 5,618
LATCH.ges resource hash list 7,309 13,422 6,113
LATCH.active service list 7,006 746 -6,260
LATCH.ges process parent latch 8,727 15,286 6,559
LATCH.enqueue hash chains 11,088 18,105 7,017
LATCH.transaction allocation 7,200 14,402 7,202
STAT...Elapsed Time 13,391 22,034 8,643
LATCH.multiblock read objects 25,342 38,017 12,675
LATCH.channel operations paren 15,999 32,736 16,737
LATCH.messages 27,444 45,354 17,910
LATCH.object queue header heap 36,106 57,968 21,862
LATCH.process queue reference 57,824 84,210 26,386
LATCH.gcs shadows freelist 36,199 69,637 33,438
LATCH.simulator hash latch 120,246 189,659 69,413
LATCH.checkpoint queue latch 154,709 248,320 93,611
LATCH.gcs resource freelist 112,884 210,168 97,284
LATCH.cache buffers lru chain 157,487 265,648 108,161
STAT...execute count 125,009 5 -125,004
STAT...session uga memory 392,448 261,632 -130,816
LATCH.KJCT flow control latch 245,838 425,612 179,774
LATCH.ASM map operation hash t 308,505 522,227 213,722
STAT...index fetch by key 250,890 21 -250,869
STAT...rows fetched via callba 250,890 21 -250,869
STAT...table fetch by rowid 250,893 21 -250,872
STAT...calls to get snapshot s 375,902 28 -375,874
LATCH.gcs resource hash 648,925 1,074,796 425,871
LATCH.object queue header oper 724,401 1,153,719 429,318
LATCH.KCL gc element parent la 636,220 1,077,684 441,464
STAT...buffer is not pinned co 501,786 42 -501,744
STAT...recursive calls 625,057 4 -625,053
LATCH.cache buffers chains 2,977,887 3,713,272 735,385
LATCH.gcs partitioned table ha 1,050,572 1,789,356 738,784
STAT...consistent gets - exami 752,677 64 -752,613
STAT...consistent gets 753,519 900 -752,619
STAT...consistent gets from ca 753,519 900 -752,619
STAT...session logical reads 753,560 928 -752,632
LATCH.shared pool simulator 129,635 882,814 753,179
LATCH.library cache pin 4,104 1,750,700 1,746,596
LATCH.kokc descriptor allocati 2 1,746,654 1,746,652
LATCH.shared pool 2,465 2,624,667 2,622,202
LATCH.library cache 7,512 2,630,087 2,622,575
STAT...session pga memory 393,216 3,473,408 3,080,192
LATCH.row cache objects 1,071 7,866,893 7,865,822
STAT...session uga memory max 2,485,872 924,803,712 922,317,840
STAT...session pga memory max 2,031,616 929,693,696 927,662,080
STAT...session pga memory max 2,031,616 929,693,696 927,662,080
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
7,612,145 28,693,840 21,081,695 26.53%
Run1 Run2 Diff Pct
7,612,145 28,693,840 21,081,695 26.53%
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.96
since XMLAGG produces more latches in single user environment. If you are planning to deal with High volume of data sets then ODCI is far better than XMLAGG.
No comments:
Post a Comment