Generating lots of rows using connect by – safely!

Tanel Poder

2008-06-08

Every now and then I need to generate a large number of rows into some table.

Not that I have any personal urge to do so, but you know, this is needed for some test cases and so on ;-)

It’s quite well known that it’s possible to generate lots of rows using CONNECT BY LEVEL <= #rows syntax.

However as the CONNECT BY LEVEL syntax is recursive by nature, it will consume more memory the more rows you query in one shot ( I think it was Mark Bobak who once posted this observation to Oracle-L, but I didn’t manage to find that thread anymore ).

So, here’s a test case:

SQL> select count(r)
  2  from (
  3     select rownum r from dual connect by rownum <= 100000000
  4  )
  5  /
        select rownum r from dual connect by rownum <= 100000000
                             *
ERROR at line 3:
ORA-04030: out of process memory when trying to allocate 44 bytes (kxs-heap-w,cursor work heap)

After running for a while the server process run out of private memory, used for the CONNECT BY cursor work heap.

Let’s investigate:

Execution plan is following, we have a single recursive CONNECT BY operator:

SQL> @x

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  5n2f53tsa3bq3, child number 0
-------------------------------------
select count(r) from (  select rownum r from dual connect by rownum <= 100000000 )

Plan hash value: 2085675642

---------------------------------------------------------
| Id  | Operation                       | Name | E-Rows |
---------------------------------------------------------
|   1 |  SORT AGGREGATE                 |      |      1 |
|   2 |   VIEW                          |      |      1 |
|   3 |    COUNT                        |      |        |
|   4 |     CONNECT BY WITHOUT FILTERING|      |        |
|   5 |      FAST DUAL                  |      |      1 |
---------------------------------------------------------

I actually ran Snapper on the session performing connect by and we see the large memory allocations from its output as well:

SQL> @snapper out,gather=s,sinclude=memory 3 10 109

-- Session Snapper v1.07 by Tanel Poder (  )

---------------------------------------------------------------------------------------------------------------------------------------------
HEAD,     SID, SNAPSHOT START   ,  SECONDS, TYPE, STATISTIC                               ,         DELTA,  DELTA/SEC,     HDELTA, HDELTA/SEC
---------------------------------------------------------------------------------------------------------------------------------------------
DATA,     109, 20080608 04:48:47,        3, STAT, session uga memory                      ,      72534112,   24178037,     72.53M,     24.18M
DATA,     109, 20080608 04:48:47,        3, STAT, session pga memory                      ,      72810496,   24270165,     72.81M,     24.27M
DATA,     109, 20080608 04:48:47,        3, STAT, sorts (memory)                          ,             1,          0,          1,        .33
--  End of snap 1
DATA,     109, 20080608 04:48:50,        4, STAT, session uga memory                      ,      95642904,   23910726,     95.64M,     23.91M
DATA,     109, 20080608 04:48:50,        4, STAT, session pga memory                      ,      95748096,   23937024,     95.75M,     23.94M
--  End of snap 2
DATA,     109, 20080608 04:48:54,        3, STAT, session uga memory                      ,      96559400,   32186467,     96.56M,     32.19M
DATA,     109, 20080608 04:48:54,        3, STAT, session pga memory                      ,      96665600,   32221867,     96.67M,     32.22M
--  End of snap 3
[...some lines snipped...]
--  End of snap 8
DATA,     109, 20080608 04:49:14,        4, STAT, session uga memory                      ,     100945488,   25236372,    100.95M,     25.24M
DATA,     109, 20080608 04:49:14,        4, STAT, session uga memory max                  ,     100880024,   25220006,    100.88M,     25.22M
DATA,     109, 20080608 04:49:14,        4, STAT, session pga memory                      ,     101056512,   25264128,    101.06M,     25.26M
DATA,     109, 20080608 04:49:14,        4, STAT, session pga memory max                  ,     100990976,   25247744,    100.99M,     25.25M
--  End of snap 9
DATA,     109, 20080608 04:49:18,        3, STAT, session uga memory                      ,     100159920,   33386640,    100.16M,     33.39M
DATA,     109, 20080608 04:49:18,        3, STAT, session uga memory max                  ,     100159920,   33386640,    100.16M,     33.39M
DATA,     109, 20080608 04:49:18,        3, STAT, session pga memory                      ,     100270080,   33423360,    100.27M,     33.42M
DATA,     109, 20080608 04:49:18,        3, STAT, session pga memory max                  ,     100270080,   33423360,    100.27M,     33.42M
--  End of snap 10

PL/SQL procedure successfully completed.

As Snapper calculates session statistic counter deltas then these snapshots show that my process allocated around 30MB of UGA memory per second during the CONNECT BY statement execution. In fact by the crash my process UGA/PGA size had grown to almost 1.8GB in size:

SQL> @ses 109 memory

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session uga memory                                               1825306316
session uga memory max                                           1825306316
session pga memory                                               1827832112
session pga memory max                                           1827832112
redo blocks read (memory)                                                 0
redo blocks read (memory) by LNS                                          0
workarea memory allocated                                                 0
sorts (memory)                                                          148

8 rows selected.

Note that this memory used for recursive connect by is not reported as workarea memory in it’s traditional sense (like sort, hash and bitmap merge areas), therefore the normal PGA size restriction constraints like PGA_AGGREGATE_TARGET don’t apply, this work area lives outside that domain. In fact if you query V$SQL_WORKAREA_ACTIVE during the SQL execution, nothing is returned for that SQL.

So, how to generate lots of rows fast and safely?

Well, we can still use CONNECT BY with lower recursion and let a cartesian merge join do the multiplication for us!

SQL> select count(r)
  2  from (
  3             select rownum r
  4             from
  5                     (select rownum r from dual connect by rownum <= 1000) a,
  6                     (select rownum r from dual connect by rownum <= 1000) b,
  7                     (select rownum r from dual connect by rownum <= 1000) c
  8             where rownum <= 100000000
  9     )
 10  /

  COUNT(R)
----------
 100000000

100 million rows were generated this time, without running out of memory.

The execution plan is following:

SQL> @x

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  76prmvktb3r9a, child number 0
-------------------------------------
select count(r) from (   select rownum r   from    (select rownum r
from dual connect by rownum <= 1000) a,    (select rownum r from dual
connect by rownum <= 1000) b,    (select rownum r from dual connect by
rownum <= 1000) c   where rownum <= 100000000  )

Plan hash value: 2999494577

-----------------------------------------------------------------------------------------
| Id  | Operation                            | Name | E-Rows |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |      |      1 |       |       |          |
|   2 |   VIEW                               |      |      1 |       |       |          |
|*  3 |    COUNT STOPKEY                     |      |        |       |       |          |
|   4 |     MERGE JOIN CARTESIAN             |      |      1 |       |       |          |
|   5 |      MERGE JOIN CARTESIAN            |      |      1 |       |       |          |
|   6 |       VIEW                           |      |      1 |       |       |          |
|   7 |        COUNT                         |      |        |       |       |          |
|   8 |         CONNECT BY WITHOUT FILTERING |      |        |       |       |          |
|   9 |          FAST DUAL                   |      |      1 |       |       |          |
|  10 |       BUFFER SORT                    |      |      1 | 15360 | 15360 |14336  (0)|
|  11 |        VIEW                          |      |      1 |       |       |          |
|  12 |         COUNT                        |      |        |       |       |          |
|  13 |          CONNECT BY WITHOUT FILTERING|      |        |       |       |          |
|  14 |           FAST DUAL                  |      |      1 |       |       |          |
|  15 |      BUFFER SORT                     |      |      1 | 15360 | 15360 |14336  (0)|
|  16 |       VIEW                           |      |      1 |       |       |          |
|  17 |        COUNT                         |      |        |       |       |          |
|  18 |         CONNECT BY WITHOUT FILTERING |      |        |       |       |          |
|  19 |          FAST DUAL                   |      |      1 |       |       |          |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM<=100000000)

During the query execution the PGA size never grew larger than about a megabyte:

SQL> @ses 110 memory

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session uga memory                                                   497316
session uga memory max                                               751696
session pga memory                                                   885040
session pga memory max                                              1147184
redo blocks read (memory)                                                 0
redo blocks read (memory) by LNS                                          0
workarea memory allocated                                                 0
sorts (memory)                                                           13

8 rows selected.

Btw, I’ve just updated my WaitProf script to a merge join row-generator to save memory when profiling over longer periods of time (which you probably don’t want to do as WaitProf never sleeps – is constantly on CPU, sampling as fast as it can).

If you want to read about performance of different row generators, then Adrian Billington has written a comprehensive article about the topic: http://www.oracle-developer.net/display.php?id=408


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS