String to Rows

Performance Comparison

The following tables show performance metrics for one run each of the following String to Row techniques described in the preceeding tutorials.

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.

Warning: Results on your own systems with your own data will differ from these results. Results will even differ from one set of test runs to the next on the same machine. Run your own tests and average the results from multiple runs before making performance decisions.




Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12051.html]SQL Snippets: String to Rows - Performance Comparison[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12051.html">SQL Snippets: String to Rows - Performance Comparison</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: String to Rows - Performance Comparison
  • URL (href): http://www.sqlsnippets.com/en/topic-12051.html

Revision Notes

Date Category Note
2007-07-29 Revision Added tests for Hierarchical methods.
2007-07-02 Revision Added Integer Series Generator test.