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.