Thursday, July 28, 2011

XMLAGG Vs ODCI

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  /

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     /

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      /

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;

Table created.

Elapsed: 00:00:02.75
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>

exec runstats_pkg.rs_start;

select x,stragg_fnc(val)
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;

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
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...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

Run1 latches total versus runs -- difference and pct
        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