dual optimisation in 10g

The DUAL table is used heavily by developers, DBAs and Oracle itself. In fact, we are probably unaware of most of our usage due to its internal use, but there are many articles and "tips of the month" available on the web regarding DUAL optimisation. Techniques have involved re-pointing a synonym for DUAL to an index-organized table, using X$DUAL and several others. Such techniques are now largely redundant, however, as 10g has optimised DUAL access for most usage requirements. There is now an internal FAST DUAL optimisation and we shall examine this in this short article.

a note on the examples

Throughout this article, we'll compare the peformance of FAST DUAL access between 10g and 9i instances. We'll look at simple timings and autotrace output. Note that these instances are on the same dual CPU x86 Intel Linux server which are configured as closely as is possible for two different Oracle releases.

fast dual

We'll start by looking at the FAST DUAL optimisation in 10g. With autotrace set, we can see the cost of executing a query against DUAL.

10g> set autotrace traceonly

10g> SELECT SYSDATE
  2  FROM   dual;

1 row selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)
   1    0   FAST DUAL (Cost=2 Card=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        203  bytes sent via SQL*Net to client
        342  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

We can see little to no resource usage whatsoever. There are no consistent gets and the single recursive call is only incurred the first time the statement is executed. In the execution plan, we can see that FAST DUAL is being used.

If we now compare this to the equivalent autotrace in 9i, we can see the savings that 10g makes.

 9i> SELECT SYSDATE
  2  FROM   dual;

1 row selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=8168)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (Cost=5 Card=8168)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        194  bytes sent via SQL*Net to client
        341  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This execution plan is more familiar to us (full table access of DUAL) as are the resource statistics. They show the three consistent gets that many developers and DBAs have been trying to reduce in recent years. Incidentally, if we return to 10g and select the DUMMY column (or *), we can see that Oracle reverts to using the DUAL table itself.

SQL> select dummy from dual;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=2)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=2 Card=1 Bytes=2)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        501  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

time savings with fast dual

The DUAL table is used heavily in Oracle, even when we might not realise it (which is one of the reasons that some DBAs wanted to tune access to it). It is used every time we call the USER or SYSDATE functions, we use it to fetch sequence values, Oracle uses it internally to support SYS_CONTEXT under certain circumstances and of course we select from it all the time! Over-use of the DUAL table has caused some systems to grind to a crawl (selecting from DUAL is fast but do it millions of times and it starts to show).

Given the above, we can now see if FAST DUAL is quicker in 10g. We'll run a simple loop of 1 million SYSDATE selects and time it by the wall-clock, starting with 10g.

10g> set timing on

10g> DECLARE
  2     v DATE;
  3  BEGIN
  4     FOR i IN 1 .. 1000000 LOOP
  5        SELECT SYSDATE INTO v FROM dual;
  6     END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:33.19

Now we can run the same example in 9i.

 9i> set timing on

 9i> DECLARE
  2     v DATE;
  3  BEGIN
  4     FOR i IN 1 .. 1000000 LOOP
  5        SELECT SYSDATE INTO v FROM dual;
  6     END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:45.65

Although by no means a scientific test, we can see from the above that the 10g version is almost one-third quicker. To test within the same database, we can also force a "standard" DUAL table access in 10g for further comparison as follows.

SQL> DECLARE
  2     v VARCHAR2(1);
  3  BEGIN
  4     FOR i IN 1 .. 1000000 LOOP
  5        SELECT dummy INTO v FROM dual;
  6     END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:39.68

We can see that the FAST DUAL optimisation still shows significant savings when compared with DUAL table access.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, November 2004

oracle-developer.net