The following tables show performance metrics for one run each of the eight integer series generation techniques described in the preceeding tutorials.
- Integer Table Method
- MODEL Method
- ROWNUM + a Big Table Method
- CONNECT BY LEVEL Method
- CUBE Method
- Type Constructor Expression Method
- Type Constructor + Cartesian Product Method
- Pipelined Function Method
Each run generated a series of integers from 1 to 100. 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 Type Constructor Pipelined
METRIC_NAME Table MODEL + Big Table LEVEL CUBE Constructor + Cartesian Product Function
---------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------------- ------------
Elapsed Time (1/100 sec) 3 3 3 2 5 262 3 4
session pga memory max 262,144 262,144 262,144 262,144 262,144 262,144 262,144 327,680
session pga memory 196,608 65,536 131,072 131,072 196,608 65,536 131,072 327,680
redo size 2,744 2,640 2,684 2,684 2,684 2,684 2,684 2,684
sorts (rows) 2,071 2,071 2,071 2,072 2,199 2,071 2,081 2,076
session uga memory 0 0 0 0 0 65,464 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 Type Constructor Pipelined
METRIC_NAME Table MODEL + Big Table LEVEL CUBE Constructor + Cartesian Product Function
---------------------------------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------------- ------------
cache buffers chains 206 163 221 163 163 1,208 181 249
row cache objects 136 96 129 87 84 135 114 171
library cache 92 77 86 80 77 113 89 179
shared pool 70 25 26 24 25 85 29 45
session idle bit 56 55 56 55 55 56 55 57
library cache pin 50 43 48 43 43 57 45 89
library cache lock 26 20 28 20 20 40 25 72
enqueues 23 16 17 16 16 26 16 20
enqueue hash chains 22 16 16 16 16 26 16 18
shared pool simulator 12 9 7 9 9 16 10 17
object queue header operation 8 12 12 12 12 305 12 15
redo allocation 8 8 8 8 8 18 8 8
cache buffers lru chain 7 6 6 6 6 396 6 7
SQL memory manager workarea list latch 6 10 6 6 6 73 6 6
session allocation 6 2 4 3 2 2 2 6
sort extent pool 4 4 4 4 4 4 4 4
session switching 4 4 4 4 4 4 4 4
kks stats 4 2 2 2 2 4 2 2
simulator hash latch 4 0 10 0 0 134 0 1
simulator lru latch 4 0 10 0 0 130 0 1
PL/SQL warning settings 3 3 3 3 3 3 3 3
compile environment latch 2 1 2 1 1 1 1 3
object stats modification 1 1 2 1 1 1 1 2
library cache lock allocation 1 1 1 1 1 1 1 2
dml lock allocation 1 1 1 1 1 1 1 1
FOB s.o list latch 1 0 0 1 0 0 0 1
OS process 0 0 0 3 0 0 0 0
messages 0 0 0 1 0 40 0 0
channel operations parent latch 0 0 0 1 0 18 0 0
channel handle pool latch 0 0 0 1 0 0 0 0
OS process allocation 0 0 0 1 0 0 0 0
process allocation 0 0 0 1 0 0 0 0
process group creation 0 0 0 1 0 0 0 0
checkpoint queue latch 0 0 0 0 0 269 0 0
redo writing 0 0 0 0 0 13 0 0
active checkpoint queue latch 0 0 0 0 0 13 0 0
loader state object freelist 0 0 0 0 0 12 0 0
virtual circuit buffers 0 0 0 0 0 9 0 0
virtual circuit queues 0 0 0 0 0 7 0 0
parallel query alloc buffer 0 0 0 0 0 4 0 0
user lock 0 0 0 0 0 4 0 0
session timer 0 0 0 0 0 3 0 0
library cache load lock 0 0 0 0 0 2 0 2
virtual circuits 0 0 0 0 0 2 0 0
active service list 0 0 0 0 0 2 0 0
library cache pin allocation 0 0 0 0 0 1 1 1
resmgr:actses active list 0 0 0 0 0 1 0 0
XDB unused session pool 0 0 0 0 0 1 0 0
KMG MMAN ready and startup request latch 0 0 0 0 0 1 0 0
resmgr:free threads list 0 0 0 0 0 1 0 0
------------ ------------ ------------ ------------ ------------ ------------ ------------------- ------------
sum 757 575 709 575 559 3,242 632 986
Techniques that use a small number of latches scale better than techniques that use a large number of latches.