Author: Eddie Dobson

Oracle FAST DUAL:

Almost all of us are aware that many Oracle databases experience high consistent gets and high CPU consumption due to excessive use of SELECT 1 FROM DUAL; calls.  However this issue is fixed in Oracle10g by introducing a new FAST DUAL execution plan. Oracle 10g has considerably reduced the consistent gets and the optimization plan is different as seen from the event 10046 trace. In Oracle 10g we have row execution plan as follows

     Rows           Execution Plan
    ———     ———————————————————
        0           SELECT STATEMENT MODE: ALL_ROWS
        0           FAST DUAL

The FAST DUAL optimization plan improves performance by significantly reducing the consistent reads and thus benefits such applications that frequently use the DUAL table.

Error enabling Statistics report:

With autotrace set, we can see the cost of executing a query against DUAL. While using FAST DUAL and setting autotrace you might have got SP2-0611 error as follows.

SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
SQL>

If you ignore the error and continue then you will see an amazing execution plan with no statistics .

SQL> select sysdate from DUAL;
SYSDATE
—————
10-MAR-08

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 |
—————————————————————————————————————–

The reason behind this error is that the user is not being assigned the PLUSTRACE role. Now if we try to assign this role to user, we get another error as follows

SQL> grant plustrace to scott;
grant plustrace to scott
*
ERROR at line 1:

ORA-01919: role ‘PLUSTRACE' does not exist

Now we need to run plustrce.sql in $ORACLE_HOME/sqlplus/admin so that the PLUSTRACE role could be created.

SQL> @'D:\oracle\product\11.1.0\db_1\sqlplus\admin\plustrce.sql';

SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE' does not exist

SQL> create role plustrace;
Role created.

SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.

SQL> grant select on v_$statname to plustrace;
Grant succeeded.

SQL> grant select on v_$mystat to plustrace;
Grant succeeded.

SQL> grant plustrace to dba with admin option;
Grant succeeded.

SQL> set echo off

Now we use below command in order to grant the PLUSTRACE role .

SQL> grant plustrace to scott;
Grant succeeded.

Now we open a new window and run the SQL then we will find execution plan with statistics.

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

SYSDATE
—————
10-MAR-08

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
     ———————————————————-
               0 recursive calls
               0 db block gets
               0 consistent gets
               0 physical reads
               0 redo size
               422 bytes sent via SQL*Net to client
               416 bytes received via SQL*Net from client
               2 SQL*Net roundtrips to/from client
               0 sorts (memory)
               0 sorts (disk)
               1 rows processed

Saving Time with FAST DUAL:

In general DUAL table is amongst the most widely used tables in Oracle. Whenever we call the USER or SYSDATE functions, we use DUAL tables to fetch sequence values. Oracle internally uses DUAL tables to support SYS_CONTEXT under certain scenarios. Although selecting from DUAL is fast but it can be slow if you do it millions of times.

Now we run a simple loop of 1 million SYSDATE SELECT commands and time it by the wall-clock in Oracle 10g standard table access in Oracle 10g and in Oracle 9i. The FAST DUAL optimization shows significant savings as compared to DUAL table access.

Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Tips for Oracle 11g SQL Execution Plan Management!!
   Inside Oracle Temporary Tablespace!!
   A Quick Guide to determine Oracle RAM Size!!
   Great Tips on Oracle Real-Time SQL Monitoring!!