The following tables show performance metrics for one run each of the following String to Row techniques described in the preceeding tutorials.
- UNION ALL Method
- Integer Series Generator Method
- Integer Series Generator Method - 10g+
- Hierarchical Methods
- Dynamic PL/SQL Method
- MODEL Method
- XML Method
Each run extracts an average of 5 values from each row of a 10,000 row table to produce a 46,000 row result set. 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.
Integer Integer
Series Series Hierarchical Hierarchical Dynamic MODEL
METRIC_NAME UNION ALL Generator Generator 10g+ DBMS_RANDOM CONNECT_BY_ROOT PL/SQL Method XML Method
------------------------- ------------ ------------ -------------- ------------ --------------- ------------ ------------ ------------
Elapsed Time (1/100 sec) 315 61 169 307 14,760 180 202 3,290
session pga memory max 3,932,160 786,432 2,097,152 4,128,768 3,670,016 393,216 3,932,160 3,342,336
session uga memory max 3,652,852 706,972 2,081,716 3,914,708 3,652,852 261,964 3,916,004 327,320
table scan rows gotten 1,191,087 10,000 10,000 10,000 10,000 1,097,620 10,000 373,076
bytes sent via SQL*Net to 804,043 881,357 887,357 881,357 881,357 792,308 881,357 880,364
session pga memory 65,536 196,608 65,536 196,608 65,536 393,216 65,536 3,342,336
session uga memory 65,464 65,464 65,464 196,392 65,464 65,464 65,464 327,320
bytes received via SQL*Ne 36,556 35,109 35,109 35,109 35,109 35,109 35,109 35,109
session logical reads 3,397 107 76 74 74 3,315 74 3,280
consistent gets from cach 3,358 68 37 35 35 3,276 35 3,239
consistent gets 3,358 68 37 35 35 3,276 35 3,239
no work - consistent read 3,322 26 27 26 26 3,086 26 3,101
table scan blocks gotten 3,322 26 26 26 26 3,082 26 3,101
redo size 2,688 2,688 2,688 2,684 2,644 2,688 2,688 2,748
sorts (rows) 2,072 58,082 48,072 58,072 58,072 2,076 48,072 2,072
DB time 290 43 145 289 14,739 158 178 3,220
CPU used when call starte 285 34 140 279 14,666 155 172 3,185
CPU used by this session 285 34 140 279 14,666 155 172 3,185
execute count 5 5 5 6 5 10,014 5 6
recursive calls 4 4 4 6 4 10,043 4 18
consistent gets - examina 1 34 1 1 1 172 1 127
buffer is not pinned coun 0 22 1 0 0 117 0 84
buffer is pinned count 0 0 19,972 0 0 6,923 0 6,900
index scans kdiixs1 0 0 10,000 0 0 8 0 0
See Statistics Descriptions for a description of each metric.
Latch Gets
The following table shows total latch gets for each method.
Integer Integer
Series Series Hierarchical Hierarchical Dynamic MODEL
METRIC_NAME UNION ALL Generator Generator 10g+ DBMS_RANDOM CONNECT_BY_ROOT PL/SQL Method XML Method
------------------------- ------------ ------------ -------------- ------------ --------------- ------------ ------------ ------------
Latch Gets Total 14,662 6,916 22,048 7,300 61,605 440,978 6,819 1,761,761
Techniques that use a small number of latches scale better than techniques that use a large number of latches.