The following tables show performance metrics for one run each of the three Column to Row techniques described in the preceeding tutorials.
Each run transforms 10 columns from an 800 row table into a 8,000 row result set. See the log file from these tests for more details.
The tests were performed using SQL*Plus against an Oracle 11g Enterprise Edition Release 11.1.0.6.0 database running on Oracle Enterprise Linux 5.
Statistics
The following table shows database statistics where values for one method differ by more than 100 from another method.
METRIC_NAME UNPIVOT UNION ALL Collection MODEL ------------------------------ ------------ ------------ ------------ ------------ Elapsed Time (1/100 sec) 30 32 32 43 table scan rows gotten 8,800 157,493 142,869 1,600 session uga memory max 123,452 123,452 123,452 246,904 bytes sent via SQL*Net to clie 127,123 118,454 118,719 127,935 session logical reads 135 755 641 72 consistent gets 83 703 588 20 consistent gets from cache 83 703 588 20 consistent gets from cache (fa 82 691 551 19 no work - consistent read gets 44 635 540 8 table scan blocks gotten 44 614 540 8 recursive calls 36 295 9 9 session uga memory 65,464 0 65,464 65,464 sorts (rows) 8,000 0 0 0
See Statistics Descriptions for a description of each metric.
Latch Gets
The following table shows total latch gets for each method.
UNPIVOT UNION ALL Collection MODEL
------------ ------------ ------------ ------------
2,150 3,771 2,463 1,863
Techniques that use a small number of latches scale better than techniques that use a large number of latches.