msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

DUAL behavior change

While reading the book Expert Oracle Database Architecture, I noticed a very important bit of information about the DUAL table. In 10g, Oracle does not do a full table scan on DUAL if it does not need to. This is not the case in versions prior to 10g. To illustrate:

In 9.2:

SQL> set autotrace on
SQL> select sysdate from dual;

SYSDATE
---------
09-NOV-05


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




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

Notice the TABLE ACCESS (FULL) OF ‘DUAL’ and the 3 consistent gets

In Oracle 10g XE:

SQL> set autotrace on
SQL> select sysdate from dual;

SYSDATE
---------
09-NOV-05


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


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

Notice that in 10g, there is no consistent gets and no full table scan. select sysdate from dual is treated as if I was calling a function directly. This is a very nice enhancement. As a side note, notice the enhancement to the autotrace results in 10g.


Filed in Oracle, Tips on 09 Nov 05 | Tags: ,


Reader's Comments

  1. |

    Eddie, I am missing something. DUAL has just one row, why does it matter if Oracle does a full tablescan of a single-row table (that is very likely in the cache anyway)?

  2. |

    Hi Rob,

    I guess it does not matter much in the case of DUAL. However, “0 consistent gets” is better than “3 consistent gets” because, quoting AskTom: “Each consistent get is a latch, latches = contention”.

    Since I only need the SYSDATE, which is not stored in the DUAL table, why scan (full or not) the DUAL (or any) table?

  3. |

    Since I only need the SYSDATE, which is not stored in the DUAL table, why scan (full or not) the DUAL (or any) table? Definitely. We had this query which is executed more than 2 million times in every half hour. SELECT SYS_CONTEXT(:B2,:B1) FROM SYS.DUAL Creating and index on dual made it 1/3. Seems it will be better when we upgrade to 10G.

  4. |

    Keep in mind that we should not refer to ‘dummy’ column of Dual table. If we do, plan will revert back to 5 c.gets.

    Usually, we don’t need to refer dummy column, but I had seen some code like that!

  5. |

    hi – great news; that’s what i like in the 10g release – there are also a lot of small changes with big effect.

    Carl