Test Data

The UKOUG conference is over for another year – but it has left me with plenty to do and lots of things to investigate. Here’s just one little point that I picked up during one of the 10 minute “Oak Talks” that members of the Oak Table Network were doing in the lunch breaks.

There is a fairly well-known strategy for generating a list of numbers by using a “select from dual … connect by …” query, but I hadn’t realised that there were two ways of using it. The code I’ve usually used is this:

select
        rownum id
from
        dual
connect by
        rownum <= 4000
;

But it looks as if most people use it like this:

select
        rownum id
from
        dual
connect by
        level <= 4000
;

The latter is clearly more “appropriate” than the former in that the use of level pseudo-column appears to be more consistent with the use of the connect by clause, so I suspect I just happened to do it the “wrong” way the first time I used it, and never noticed that it looked a little odd because it was returning the expected result.

The “Oak Talk” was the one I gave, by the way, and one of the comments I made was that this code was fine if you wanted a tiny amount of data, but if you wanted a larger amount of data you ought to do something like this:

with generator as (
	select
		rownum id
	from dual
	connect by
		rownum <= 4000
)
select
	/*+ gather_plan_statistics */
	count(*)
from	(
	select
		/*+ no_merge */
		rownum id
	from
		generator,
		generator
	where
		rownum <= 10000000
	)
;

The key feature is that I’ve used the original query twice, joining the result set to itself, rather than simply increasing the target value. In this example I’ve put the select statement that generates 10,000,000 rows of data into an inline non-mergeable view and counted the result set. This allowed me to run the query without having to generate a massive output before showing you the execution stats. Here’s the plan with stats:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TEMP TABLE TRANSFORMATION      |                            |      1 |        |      1 |00:04:57.76 |      31 |      7 |      7 |       |       |          |
|   2 |   LOAD AS SELECT                |                            |      1 |        |      1 |00:00:00.03 |      10 |      0 |      7 |   261K|   261K|  261K (0)|
|   3 |    COUNT                        |                            |      1 |        |   4000 |00:00:00.02 |       0 |      0 |      0 |       |       |          |
|   4 |     CONNECT BY WITHOUT FILTERING|                            |      1 |        |   4000 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   5 |      FAST DUAL                  |                            |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |
|   6 |   SORT AGGREGATE                |                            |      1 |      1 |      1 |00:04:57.72 |      18 |      7 |      0 |       |       |          |
|   7 |    VIEW                         |                            |      1 |      1 |     10M|00:01:30.04 |      18 |      7 |      0 |       |       |          |
|*  8 |     COUNT STOPKEY               |                            |      1 |        |     10M|00:01:00.04 |      18 |      7 |      0 |       |       |          |
|   9 |      MERGE JOIN CARTESIAN       |                            |      1 |      1 |     10M|00:00:40.04 |      18 |      7 |      0 |       |       |          |
|  10 |       VIEW                      |                            |      1 |      1 |   2500 |00:00:00.04 |       9 |      7 |      0 |       |       |          |
|  11 |        TABLE ACCESS FULL        | SYS_TEMP_0FD9D6602_4E35F82 |      1 |      1 |   2500 |00:00:00.04 |       9 |      7 |      0 |       |       |          |
|  12 |       BUFFER SORT               |                            |   2500 |      1 |     10M|00:00:10.01 |       9 |      0 |      0 | 64512 | 64512 |57344  (0)|
|  13 |        VIEW                     |                            |      1 |      1 |   4000 |00:00:00.02 |       9 |      0 |      0 |       |       |          |
|  14 |         TABLE ACCESS FULL       | SYS_TEMP_0FD9D6602_4E35F82 |      1 |      1 |   4000 |00:00:00.01 |       9 |      0 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - filter(ROWNUM<=10000000)

As you can see, there was a little I/O (direct path writes to materialize the factored subquery, then db file scattered reads to read it back), and a little bit of memory used as the query ran. The run time of the query was about 4 seconds, compared to using the simple form of the query:

PLAN_TABLE_OUTPUT
-----------------
SQL_ID  0gftkt677gyq1, child number 0
-------------------------------------
select  /*+ gather_plan_statistics */  count(*) from (  select   /*+ no_merge
*/   rownum id  from dual  connect by   rownum <= 10000000 )

Plan hash value: 2085675642
----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Starts | E-Rows | A-Rows |   A-Time   |
----------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                 |      |      1 |      1 |      1 |00:04:56.47 |
|   2 |   VIEW                          |      |      1 |      1 |     10M|00:06:16.47 |
|   3 |    COUNT                        |      |      1 |        |     10M|00:05:46.47 |
|   4 |     CONNECT BY WITHOUT FILTERING|      |      1 |        |     10M|00:05:16.47 |
|   5 |      FAST DUAL                  |      |      1 |      1 |      1 |00:00:00.01 |
----------------------------------------------------------------------------------------

This query took about 8 seconds, so there’s not really a lot of difference in terms of speed between the two – especially when you consider that you’re probably going to do something rather more complex than select a simple rownum to create some data. But there’s an important difference which shows up when you look at the session stats, in particular the memory usage. The following figures show the changes in UGA and PGA memory from running the two queries.

Session delta (complex query)
Name                                                                     Value
----                                                                     -----
session uga memory                                                     647,164
session uga memory max                                                 647,164
session pga memory                                                   3,604,480
session pga memory max                                               3,604,480

Session delta (simple query)
Name                                                                     Value
----                                                                     -----
session uga memory                                                      65,464
session uga memory max                                             299,754,000
session pga memory max                                             300,023,808

The simple query is only 4 seconds (100%) slower – but the amount of PGA memory it requires is huge (80 times more) compared to the complex query. What’s more, if you look back at the execution plan with gather_plan_statistics, you’ll see that it’s memory that doesn’t show in the “workarea operations” part of the plan. You’ve probably seen several articles on my blog using this factored subquery approach to generate data – now you know why it’s the default code I have in my testing template – if you want to generate 100M rows, it makes an enormous difference.

[Further reading on “subquery factoring”]