Whats the performance of querying in dual on Oracle 11g

Question

I was trying to access the time and timezone of a db. The query i had were :

select sysdate from dual

and

select systimestamp from dual

What would be the performance over-head of querying these ? I do understand that until 10g these were expensive and starting 11 they were to be in-memory. Would these run in 00:00:01 ?

"Would these run in 00:00:01" - that's an unusual question. Why do you ask? — Bob Jarvis - Слава Україні, Jan 27 '16 at 12:37

Florin Ghita · Accepted Answer · 2016-01-27T07:33:01.363

I won't bother of that. Surely is fast.

An idea would be to measure it. Because is fast, let's do it 10000 times and measure the time:

declare
t number;
d date;
begin

  t:=dbms_utility.get_time();
  for k in 1..10000 loop
    select sysdate into d from dual;
  end loop;
  dbms_output.put_line(dbms_utility.get_time() - t);
end;
/

On my machine(with oracle 11.2.0.4) this outputs 16, so 16 milliseconds for 10000 times, that means 0.000016 seconds per operation.

EDIT: Select 1 from dual is faster, the above returning 13 milliseconds. Systimestamp is a little slower than sysdate, with 17.

score 2 · Answer 2 · answered Jan 27 '16 at 12:47

I stole @FlorinGhita's code and changed it to:

declare
  t number;
  d date;
begin
  t := dbms_utility.get_time();

  for k in 1..10000 loop
    select sysdate into d from dual;
  end loop;

  dbms_output.put_line(dbms_utility.get_time() - t);

  t := dbms_utility.get_time();

  for k in 1..10000 loop
    d := SYSDATE;
  end loop;

  dbms_output.put_line(dbms_utility.get_time() - t);
end;

because I wanted to contrast the amount of time required to do the SELECT with the amount of time required to perform the direct assignment.

The results of five runs of the above were

SELECT    Assignment
73        6
84        6
74        6
74        6
74        5

Next, my thought was that perhaps there was overhead associated with having the SELECT statement first - so I changed it around so that the assignment loop was done first followed by the SELECT loop:

declare
  t number;
  d date;
begin
  t := dbms_utility.get_time();

  for k in 1..10000 loop
    d := SYSDATE;
  end loop;

  dbms_output.put_line(dbms_utility.get_time() - t);

  t := dbms_utility.get_time();

  for k in 1..10000 loop
    select sysdate into d from dual;
  end loop;

  dbms_output.put_line(dbms_utility.get_time() - t);
end;

Five runs of the above gave:

 Assignment    SELECT
 5             78
 6             75
 7             72
 6             75
 6             86

If I'm reading the above correctly it shows that performing a SELECT...FROM DUAL takes more than 10 times LONGER than making a direct assignment.

Takeaway: don't use SELECT xxxxx INTO zzz FROM DUAL. Use zzz := xxxxx instead.

Timings above were generated by Oracle 11.1

Hope this helps.

Nice test. Just one comment here. The worse timing is not caused by access to dual table, but rather by context which between PL and SQL engine. The same applies to `a := select seq.nextval from dual;` vs. pure `a := seq.nextval;` . The later is newer, simplified syntax. — ibre5041, Jan 27 '16 at 16:31
Worth reading Bob's thread : http://stackoverflow.com/questions/34418779/using-select-to-call-a-function — Florin Ghita, Jan 27 '16 at 19:29
@FlorinGhita - I thought this current question was interesting, especially in regards to the question you cited. Thanks for remembering it. :-) I hadn't thought to compare the `SELECT function INTO variable FROM DUAL` to `variable := function` before, so this was a good opportunity. — Bob Jarvis - Слава Україні, Jan 27 '16 at 23:01

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

Yes these was some perfomance improvement implemented in 11g See this anwser. But even in 10g there is no need to worry about performance. Most likely the access to this table will NOT do any disk IO (just one logical read).

edited May 23 '17 at 12:04

Community

1
1

answered Jan 27 '16 at 07:08

ibre5041

4,903
1
20
35

score 0 · Answer 4 · answered Jan 27 '16 at 07:08

I am using 11g.

Adding to @Florin answer, I repeated the test for 10000 rows, to get_time for both the commands, sysdate and systimestamp.

The output for sysdate was same as Florin, 16 but the output for systimestamp was 36.

But when I repeated it for 100000 rows, the output was 185 and 197 respectively.

So to answer your question, using sysdate is definately faster than systimestamp.

Whats the performance of querying in dual on Oracle 11g

4 Answers4