Skip to content

SELECT FROM DUAL : Oracle Performance And Tuning

The DUAL table is automatically created by Oracle and contains one column (DUMMY) and one row (x value).

This table is often used by SQL developer in PL/SQL code (Package, Functions, Trigger) to initialize variables storing technical information such as for example SYSDATE, USER or HOSTNAME.

Querying DUAL table is generally faster  as we can see below:

SQL> select sysdate from dual;

SYSDATE
---------
05-OCT-21

Elapsed: 00:00:00.01

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
        554  bytes sent via SQL*Net to client
        386  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

But what happens when this “SELECT FROM DUAL” is executed several times into the application :

Let’s execute it for 100, 1000, 10000, 100000 and 1000000 executions

SQL> declare
        v_date date;
begin
 for rec in 1..100 loop
        select sysdate into v_date from dual;
  end loop;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
        v_date date;
begin
 for rec in 1..1000 loop
        select sysdate into v_date from dual;
  end loop;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
        v_date date;
begin
 for rec in 1..10000 loop
        select sysdate into v_date from dual;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.08
SQL> declare
        v_date date;
begin
 for rec in 1..100000 loop
        select sysdate into v_date from dual;
  end loop;
end;  2    3    4    5    6    7
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
SQL> declare
        v_date date;
begin
 for rec in 1..1000000 loop
        select sysdate into v_date from dual;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.34
SQL>

 

Let’s execute now a PL/SQL block to assign directly the variable v_date with SYSDATE instead of using “SELECT FROM DUAL”:

SQL> declare
        v_date date;
begin
 for rec in 1..100 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> declare
        v_date date;
begin
 for rec in 1..1000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> declare
        v_date date;
begin
 for rec in 1..10000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
        v_date date;
begin
 for rec in 1..100000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
SQL> declare
        v_date date;
begin
 for rec in 1..1000000 loop
        v_date := sysdate;
  end loop;
end;
  2    3    4    5    6    7    8
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.28
SQL>

 

Conclusion:

Nb of executions SELECT FROM DUAL Assigning variable
100
1 ms
0 ms
1000 1 ms
0 ms
10000 8 ms
1 ms
100000 85 ms
14 ms
1000000 8.34 sec
1.28 sec

 

“SELECT FROM DUAL” is always slower than “Assigning variable”:

  • 8 times more slower for 10000 executions
  • 6 times more slower for 100000 executions
  • More than 8 times more slower for 1000000 executions

From a performance point of vew, “SELECT FROM DUAL” must be avoided to initialize variable because when you query the DUAL table in a PL/SQL block, oracle optimizer does a roundtrip between the PL/SQL engine and the SQL engine. For few rows, it’s fast, but for several rows (Ex.: 1000000) the “SELECT FROM DUAL” is inefficient because oracle will do 1000000 roundtrips between the PL/SQL engine and the SQL engine.

I have seen plenty of applications where the “SELECT FROM DUAL” is used everywhere (Ex. : “SELECT FROM DUAL” in a Logon trigger !!!) while we can use a simple “Assigning variable” and changing the code has increased the performance of the application significantly.

 

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *