The following tables show performance metrics for one run each of six integer series generation techniques described in the preceeding tutorials.
- Integer Table Method
- MODEL Method
- ROWNUM + a Big Table Method
- CONNECT BY LEVEL Method
- Type Constructor + Cartesian Product Method
- Pipelined Function Method
Each run generated a series of integers from 1 to 100,000.
Note that the Type Constructor Expression Method technique was excluded from this comparison because it can only be used to generate up to 999 different values. The CUBE Method technique was excluded from this test because it failed to complete in under 10 minutes.
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 ROWNUM CONNECT BY Type Constructor Pipelined
METRIC_NAME Table MODEL + Big Table LEVEL + Cartesian Product Function
---------------------------------------- ------------ ------------ ------------ ------------ ------------------- ------------
Elapsed Time (1/100 sec) 59 349 68 67 57 544
session pga memory max 262,144 4,784,128 262,144 2,031,616 262,144 327,680
session uga memory max 261,964 4,533,984 261,964 2,016,252 261,964 261,964
session pga memory 196,608 65,536 131,072 0 262,144 327,680
session logical reads 6,879 45 6,927 45 78 111
consistent gets 6,840 6 6,888 6 39 72
consistent gets from cache 6,840 6 6,888 6 39 72
no work - consistent read gets 6,822 0 6,860 0 0 5
buffer is not pinned count 6,658 0 0 0 22 37
DB time 29 311 27 37 22 34
CPU used when call started 29 309 27 35 22 34
CPU used by this session 29 309 27 35 21 34
session uga memory 0 0 0 0 65,464 65,464
See Statistics Descriptions for a description of each metric.
Latch Gets
The following table shows total latch gets for each method.
Integer ROWNUM CONNECT BY Type Constructor Pipelined
METRIC_NAME Table MODEL + Big Table LEVEL + Cartesian Product Function
---------------------------------------- ------------ ------------ ------------ ------------ ------------------- ------------
cache buffers chains 13,946 836 13,927 163 196 258
session idle bit 13,376 13,375 13,376 13,375 13,375 13,377
simulator lru latch 424 172 461 0 0 1
simulator hash latch 424 172 461 0 0 1
row cache objects 139 96 129 87 132 171
cache buffers lru chain 106 522 6 6 6 7
library cache 92 83 85 79 119 20,172
object queue header operation 79 365 12 12 15 18
checkpoint queue latch 55 237 0 0 11 43
library cache pin 50 49 48 43 54 13,417
shared pool 47 20 18 17 45 49
library cache lock 30 24 30 22 34 79
enqueues 21 96 17 16 16 100
enqueue hash chains 20 97 16 16 16 99
messages 10 28 4 0 8 48
shared pool simulator 10 9 6 9 16 17
redo allocation 9 12 8 8 8 12
SQL memory manager workarea list latch 6 144 6 6 6 140
channel operations parent latch 6 22 0 0 6 29
session allocation 6 2 4 2 2 6
session switching 4 4 4 4 4 4
sort extent pool 4 4 4 4 4 4
kks stats 4 2 2 2 2 2
PL/SQL warning settings 3 3 3 3 3 3
redo writing 2 7 0 0 1 8
active checkpoint queue latch 2 3 0 0 1 1
compile environment latch 2 1 2 1 1 3
object stats modification 1 1 1 2 1 1
library cache lock allocation 1 1 1 1 1 2
dml lock allocation 1 1 1 1 1 1
session timer 1 1 0 0 1 2
KMG MMAN ready and startup request latch 1 1 0 0 1 1
object queue header heap 1 0 0 0 0 0
JS queue state obj latch 0 36 0 0 0 36
active service list 0 10 0 0 0 10
qmn task queue latch 0 4 0 0 0 0
In memory undo latch 0 2 0 0 0 2
OS process allocation 0 1 1 0 0 2
resmgr:actses active list 0 1 0 0 0 1
resmgr:schema config 0 1 0 0 0 1
kwqbsn:qsga 0 1 0 0 0 0
Shared B-Tree 0 1 0 0 0 0
library cache load lock 0 0 0 0 2 2
library cache pin allocation 0 0 0 0 1 1
mostly latch-free SCN 0 0 0 0 0 1
undo global data 0 0 0 0 0 1
lgwr LWN SCN 0 0 0 0 0 1
Consistent RBA 0 0 0 0 0 1
FOB s.o list latch 0 0 0 0 0 1
------------ ------------ ------------ ------------ ------------------- ------------
sum 28,883 16,447 28,633 13,879 14,089 48,136
Techniques that use a small number of latches scale better than techniques that use a large number of latches.