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      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'
Statistics
----------------------------------------------------------
         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
SQL>

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;

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

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

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 */
        a.name,100.*b.sleeps/b.gets 
FROM
        v$latchname a, v$latch b 
WHERE
        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.