Oracle fast dual Oracle Database Tips by Donald Burleson

Arup Nanda has an excellent description of the 10g "Fast dual" feature, whereby select from the "dual" fake table are now performed faster, since fast dual does not perform logical I/O (consistent gets):

How many developers (and DBAs, too) do you think use this command often?

select USER into <some variable> from DUAL

Far too many, probably. Each call to the DUAL creates logical I/Os, which the database can do without. In some cases the call to DUAL is inevitable as in the line <somevariable> := USER. Because Oracle code treats DUAL as a special table, some ideas for tuning tables may not apply or be relevant.

Oracle Database 10g makes all that worry simply disappear: Because DUAL is a special table, the consistent gets are considerably reduced and the optimization plan is different as seen from the event 10046 trace.

In Oracle9i
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 TABLE ACCESS (FULL) OF 'DUAL'


In 10g
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FAST DUAL

Notice the use of the new FAST DUAL optimization plan, as opposed to the FULL TABLE SCAN of DUAL in Oracle9i. This improvement reduces the consistent reads significantly, benefiting applications that use the DUAL table frequently.

Note: Technically these fast DUAL improvements are implemented in the SQL Optimizer, but of course for many users SQL*Plus is the primary tool for manipulating SQL.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.