The following tables show performance metrics for one run each of the string aggregation techniques for comma separated values described in the preceding tutorials
- MAX DECODE Method
- LEAD Method
- MODEL Method 1
- MODEL Method 2
- MODEL Method 3
- Hierarchical Method
- Object Method
- Object + MODEL
- Tom Kyte's STRAGG
- XML - CSV Method.
Each run was performed on a 100,000 row table. It resulted in 1000 rows of output with 100 comma separated values per row. Since some methods cannot include null values in the aggregated result or do not support ordering the aggregated values these features were not included in the tests. See the log file from these tests for more details.
Statistics
The following table shows database statistics where values for one method differ by more than 100 from another method.
MAX Object
METRIC_NAME DECODE LEAD MODEL 1 MODEL 2 MODEL 3 Hierarchical Object + MODEL STRAGG XML
------------------------- ----------- ----------- ----------- ----------- ----------- ------------ ----------- ----------- ----------- -----------
Elapsed Time (1/100 sec) 268 1,682 176 126 216 3,004 67 247 329 695
session pga memory max 1,507,328 2,097,152 27,721,728 4,521,984 3,932,160 7,274,496 2,293,760 2,949,120 2,621,440 8,323,072
session uga memory max 1,427,076 1,492,540 26,966,936 4,369,316 3,787,236 7,187,908 1,754,396 2,655,940 2,409,036 5,289,452
sorts (rows) 102,075 102,075 103,075 103,075 103,075 10,302,075 102,079 102,075 202,094 202,075
table scan rows gotten 100,000 100,000 100,000 101,000 100,000 10,100,000 100,000 100,000 103,514 100,000
redo size 2,684 2,684 2,684 2,684 2,684 2,684 26,636 2,684 2,744 2,684
bytes received via SQL*Ne 6,011 7,919 2,109 2,109 2,109 2,109 2,109 2,109 2,109 2,109
undo change vector size 2,004 2,004 2,004 2,004 2,004 2,004 8,588 2,004 2,004 2,004
session logical reads 229 229 229 233 229 18,629 548 336 486 271
consistent gets 190 190 190 194 190 18,590 384 297 445 232
consistent gets from cach 190 190 190 194 190 18,590 384 297 445 232
no work - consistent read 180 180 180 182 180 18,180 184 183 217 180
table scan blocks gotten 180 180 180 182 180 18,180 180 180 204 180
DB time 251 1,565 179 125 215 3,003 67 247 267 692
CPU used by this session 247 1,555 173 119 208 2,991 45 240 262 661
CPU used when call starte 247 1,555 173 119 208 2,991 45 240 262 661
db block changes 46 46 46 46 46 46 216 46 47 46
db block gets 39 39 39 39 39 39 164 39 41 39
db block gets from cache 39 39 39 39 39 39 164 39 41 39
workarea executions - opt 8 8 12 14 14 313 8 1,009 14 8
calls to get snapshot scn 7 7 7 9 7 407 96 41 93 21
recursive calls 5 4 4 4 4 4 316 15 58 6
sorts (memory) 2 2 3 3 3 203 6 2 13 2
consistent gets - examina 1 1 1 1 1 1 157 103 195 43
session pga memory 196,608 786,432 0 0 0 196,608 917,504 196,608 458,752 6,029,312
session uga memory 65,464 130,928 0 0 0 65,464 720,104 130,928 196,392 2,945,880
physical read total bytes 0 0 0 0 0 0 8,192 0 0 0
physical read bytes 0 0 0 0 0 0 8,192 0 0 0
buffer is not pinned coun 0 0 0 0 0 0 93 71 142 28
Note that these tests were performed on a single CPU machine. On multi-CPU machines MODEL Method 1 may perform significantly faster than the other two MODEL methods because it uses PARTITION BY logic.
See Statistics Descriptions for a description of each metric.
Latch Gets
The following table shows total latch gets for each method.
MAX Object
METRIC_NAME DECODE LEAD MODEL 1 MODEL 2 MODEL 3 Hierarchical Object + MODEL STRAGG XML
------------------------- ----------- ----------- ----------- ----------- ----------- ------------ ----------- ----------- ----------- -----------
cache buffers chains 531 564 539 544 531 37,386 1,369 643 852 590
session idle bit 175 175 175 175 175 178 177 176 176 175
row cache objects 93 93 105 120 120 708 829 12,348 9,345 1,808,430
enqueue hash chains 20 336 93 18 16 498 155 20 20 176
enqueues 20 332 92 18 16 493 98 19 21 172
library cache 91 110 82 80 74 127 851 198 2,629 603,797
library cache pin 50 71 47 43 41 85 441 76 2,376 401,878
JS queue state obj latch 0 144 36 0 0 216 36 0 0 72
shared pool 70 82 29 26 24 54 194 54 3,062 403,650
library cache lock 27 28 24 22 20 34 320 71 96 100,967
resmgr:resource group CPU 0 268 20 8 16 470 2 2 4 16
SQL memory manager workar 75 410 14 83 16 1,080 6 4,073 75 142
simulator hash latch 14 14 14 14 14 1,414 19 15 20 15
simulator lru latch 14 14 14 14 14 1,414 19 15 20 15
messages 20 106 14 8 14 199 25 14 18 52
shared pool simulator 12 18 11 7 5 21 84 20 1,019 100,924
redo allocation 8 21 10 8 8 30 119 8 8 15
object queue header opera 15 27 9 13 15 42 30 15 18 21
active service list 2 38 8 0 2 61 8 2 2 18
checkpoint queue latch 22 126 6 16 6 220 0 22 22 50
channel operations parent 18 78 6 8 6 145 0 14 14 34
cache buffers lru chain 6 6 6 6 6 6 9 6 7 6
session switching 4 4 4 4 4 5 4 4 4 4
sort extent pool 4 4 4 4 4 5 4 4 4 4
PL/SQL warning settings 3 3 3 3 3 3 5 3 3 3
session allocation 2 2 2 2 2 4 294 4 4 2
kks stats 4 2 2 2 2 2 6 2 2 2
session timer 2 5 2 0 2 12 0 2 2 4
In memory undo latch 0 8 2 0 0 12 2 0 0 4
redo writing 3 20 1 2 1 33 21 3 3 10
dml lock allocation 1 1 1 1 1 2 43 1 1 1
compile environment latch 1 1 1 1 1 1 3 2 2 1
object stats modification 1 1 1 1 1 1 1 1 1 1
active checkpoint queue l 1 5 1 0 1 10 0 1 1 3
resmgr:actses active list 0 4 1 0 0 7 1 0 0 2
resmgr:schema config 0 4 1 0 0 6 1 0 0 2
shared pool sim alloc 0 0 1 0 0 0 0 0 0 0
OS process allocation 1 5 0 1 1 11 0 0 1 3
KMG MMAN ready and startu 1 5 0 1 1 10 0 0 1 3
library cache lock alloca 0 0 0 1 1 0 8 6 2 3
mostly latch-free SCN 0 4 0 0 0 9 7 0 0 2
lgwr LWN SCN 0 4 0 0 0 9 7 0 0 2
qmn task queue latch 0 5 0 0 0 5 0 0 5 0
parallel query alloc buff 0 4 0 0 0 4 0 0 0 0
undo global data 0 1 0 0 0 3 22 0 0 1
OS process 0 0 0 0 0 3 0 0 0 0
ksuosstats global area 0 1 0 0 0 2 0 0 0 1
JS slv state obj latch 0 0 0 0 0 2 0 0 0 0
Consistent RBA 0 1 0 0 0 1 7 0 0 1
kwqbsn:qsga 0 1 0 0 0 1 0 0 1 0
Shared B-Tree 0 1 0 0 0 1 0 0 1 0
transaction branch alloca 0 0 0 0 0 1 0 0 0 0
dummy allocation 0 0 0 0 0 1 0 0 0 0
channel handle pool latch 0 0 0 0 0 1 0 0 0 0
parameter table allocatio 0 0 0 0 0 1 0 0 0 0
process group creation 0 0 0 0 0 1 0 0 0 0
process allocation 0 0 0 0 0 1 0 0 0 0
ncodef allocation latch 0 0 0 0 0 1 0 0 0 0
threshold alerts latch 0 0 0 0 0 1 0 0 0 0
OS process: request alloc 0 0 0 0 0 1 0 0 0 0
resmgr:free threads list 0 0 0 0 0 1 0 0 0 0
library cache pin allocat 0 0 0 0 0 0 11 3 1 3
library cache load lock 0 0 0 0 0 0 6 0 6 0
sequence cache 0 0 0 0 0 0 6 0 0 0
transaction allocation 0 0 0 0 0 0 4 0 0 0
session state list latch 0 0 0 0 0 0 3 3 0 3
post/wait queue 0 0 0 0 0 0 3 0 0 0
dictionary lookup 0 0 0 0 0 0 3 0 0 0
FOB s.o list latch 0 0 0 0 0 0 2 0 1 0
list of block allocation 0 0 0 0 0 0 2 0 0 0
job_queue_processes param 0 1 0 0 0 0 0 0 0 0
----------- ----------- ----------- ----------- ----------- ------------ ----------- ----------- ----------- -----------
sum 1,311 3,158 1,381 1,254 1,164 45,055 5,267 17,850 19,850 3,421,280
Techniques that use a small number of latches scale better than techniques that use a large number of latches.
Acknowledgements
The test harness used to produce these results was adapted from Tom Kyte's Runstats.sql code.