Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle alternative dual table

Tips by Donald Burleson

An alternative DUAL
Mike Ault

There is a cost when selecting from DUAL, even if we?re only selecting a constant value. To demonstrate:


SQL> select 1 from dual;
Elapsed: 00:00:00.01
Execution plan
         0 recursive calls
         0 db block gets
         3 consistent gets
         0 physical reads
         0 redo size
       380 bytes sent via SQL*Net to client
       499 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
         0 sorts (memory)
         0 sorts (disk)
         1 rows processed

We see that each select from DUAL costs 3 consistent gets. Now, if you do a lot of selecting from DUAL your code might benefit from using an alternative DUAL, one that only requires 1 consistent get. Here's how it's done:


create table xdual (
   dummy varchar2(1) primary key
organization index;
insert into xdual varchar2(9);
analyze table xdual compute statistics for table for all indexes for all
indexed columns;

The trick is to create the XDUAL as an index-organized table and analyze it properly. This allows the cost-based optimizer to generate the most efficient plan possible, which requires only 1 consistent get:


SQL> select 1 from xdual;


Elapsed: 00:00:00.01
Execution plan
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_33973' (UNIQUE) (Cost=1


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

To see how these two compare, consider the following code example:


    x number;
    for i in 1 .. 10000 loop
        select 1 into x from dual;
    end loop;

This required about 0.32 seconds on my machine using dual, whereas if I exchanged XDUAL with DUAL I had to wait only 0.25 seconds. Not a whole lot, but it's still about 20% reduction. Also keep in mind that consistent gets = CPU resources so if you reduce the required number of consistent gets you reduce the need for CPU.

Note that in Oracle 10g this situation disappears since the DUAL table now has become a special internal table that requires no consistent gets!

Using Reusable SQL

Except for single run SQL (such as weekly reports or infrequently used procedures) you should attempt to use bind variables instead of literals in your PL/SQL code. Use of bind variables allows the code to be reused multiple times. The entire purpose of the shared pool is to allow reuse of SQL statements that have already been parsed.

You can have the DBA set the CURSOR_SHARING parameter to FORCED in Oracle8i or to FORCED or SIMILAR in Oracle9i. In Oracle9i the hint CURSOR_SHARING_EXACT can be used for specific code that shouldn't be shared. In Oracle8i there is no CURSOR_SHARING_EXACT parameter and all literals in SELECT statements will be changed to bind variables. In Oracle9i Oracle uses bind variable peaking for the first time a SQL is parsed to allow more optimal code paths to be selected.

Identify SQL using Comments

It can be very difficult to pull your PL/SQL procedure code out of the background code in an instance shared pool. You can place a comment in each SQL statement that identifies the SQL within the shared pool. An example of this is:


CURSOR get_latch IS
SELECT /* DBA_UTIL.get_latch */,100.*b.sleeps/b.gets 
        v$latchname a, v$latch b 
        a.latch# = b.latch# and b.sleeps > 0;

Now to find all SQL code in the shared pool from the DBA_UTILITIES package you can simply query the V$SQLAREA or V$SQLTEXT to find code entries with ?%DBA_UTIL%? in the SQL_TEXT column.

Don't Over-specify Variable Length

Because a VARCHAR2 can be up to 32k in PL/SQL it is easy to fall into the trap of thinking that you can always specify the length to be many times what you feel you need. An example is setting VARCHAR2 to 2000 when you only need 80 characters.

The problem with over-specifying variable size is that the PL/SQL engine believes you and reserves the memory size you ask for in each variable declaration. Now this may not be a problem with one or two over-specified variables but if this is a PL/SQL table containing a thousand records it can place considerable memory overhead on your system.

Proper Use of Data Types

Improper use of datatypes can result in implicit type conversions. Implicit type conversions can result in the statement not being able to use appropritate indexes thus forcing a full table scan. Using %TYPE and %ROWTYPE to capture the types from either the specific table or cursor alleviates this.



If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.