Beware of Oracle Dual Table Costs!!

 Author: Jeff McCormick

Page: 1 2

If you have ever used Oracle then you must be aware of the DUAL tables. The DUAL table is one of the first things we learn as Oracle professionals. Basically a DUAL table is a table with a single column and single row owned by the SYS user and accessible to all users. It is used to evaluate constant expression in the SELECT statements, select the name of the current user, ping the database or to generate the next sequence number for a surrogate key. In this article we will help you reveal the cost of querying the DUAL table and offer some less expensive options to optimize application processing.

Cost of DUAL:

You can measure the cost of DUAL by looking at CPU consumption in terms of logical I/O. The logical I/O can be measured as consistent gets. You can use the AUTOTRACE command in SQL*Plus to display the value of consistent gets of an executed statement.

The advent of Oracle 10g eliminated the overhead of performing logical I/O for the query has been eliminated and a new FAST DUAL operation is shown in the access path from the execution plan. The consistent gets are shown as zero in the SQL statement execution statistics. However the behavior of SELECT * DUAL is the same as it had been in prior releases of Oracle and you need to have 3 consistent gets so as to satisfy your query. You can eliminate the consistent gets by changing your code to SELECT 1 FROM DUAL.

SET AUTOTRACE TRACEONLY
SELECT USER FROM DUAL;
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
331 bytes sent via SQL*Net to client
377 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

DUAL Tables in Action:

Consider an application where primary keys are generated with the help of sequences. Such can approach can cause poor batch performance due to addition of millions of rows to the database. Our task is to measure the cost of key generation and introduce a more efficient way to create unique keys for our batch processing.

Using DUAL for generating Sequences:

Now we will generate next number in a sequence by using NEXTVAL function. Using sequence object requires only one consistent get and db block gets and recursive calls will be eliminated by caching the sequence numbers.

Create Sequence:

CREATE SEQUENCE MYSEQ
    START WITH 1000000000 INCREMENT BY 1
    MINVALUE 1000000000 MAXVALUE 5000000000
    NOCYCLE NOCACHE ORDER;

Set AUTOTRACE:

SET AUTOTRACE TRACEONLY

NEXTVAL:

SELECT MYSEQ.NEXTVAL FROM dual;

Execution Plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 3108674772
---------------------------------------------------------

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

SQL Statement Execution Statistics:

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

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Best Setting for Oracle Multiblock Read Count!!
   Great Tips for Oracle Wrap Utility!!
   The Power of Oracle External Tables
   DBA Tips for Statistics gathering on Oracle Data Dictionary Objects!!