Integer Series Generators

Performance Comparison - Large Numbers

The following tables show performance metrics for one run each of six integer series generation techniques described in the preceeding tutorials.

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.

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-12053.html]SQL Snippets: Integer Series Generators - Performance Comparison - Large Numbers[/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-12053.html">SQL Snippets: Integer Series Generators - Performance Comparison - Large Numbers</a>

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

  • Link Text : SQL Snippets: Integer Series Generators - Performance Comparison - Large Numbers
  • URL (href): http://www.sqlsnippets.com/en/topic-12053.html