Is NVL really slower?

I recently found this tip.

Small "abstract": the use of a verbose to compare values including null values is faster than the use of nvl, or even coalesce. An NVL is must slower than the other two options!

I wanted to try it in 11g (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production)
I show here the code of that article but cut-paste friendly :-) (So you can try it by yourself)



create or replace
procedure P1 ( a number, b number, c date, d date, e varchar2, f varchar2) is
x number;
begin
if a != b or ( a is null and b is not null ) or
( a is not null and b is null ) then x := 1; end if;
if c != d or ( c is null and d is not null ) or
( c is not null and d is null ) then x := 1; end if;
if e != f or ( e is null and f is not null ) or
( e is not null and f is null ) then x := 1; end if;
end;
/

create or replace
procedure P2 ( a number, b number, c date, d date, e varchar2, f varchar2) is
x number;
begin
if nvl(a,-1) != nvl(b,-1) then x := 1; end if;
if nvl(c,to_date('0001','yyyy')) != nvl(d,to_date('0001','yyyy')) then x := 1; end if;
if nvl(e,chr(0)) != nvl(f,chr(0)) then x := 1; end if;
end;
/

create or replace
procedure P3 ( a number, b number, c date, d date, e varchar2, f varchar2) is
x number;
begin
if (coalesce(a,-1) != coalesce(b,-2)) then x:=1; end if;
if (coalesce(c,sysdate+1) != coalesce (d,sysdate+2)) then x:=1; end if;
if (coalesce(e,'a') != coalesce(f,'b')) then x:=1; end if;
end;
/



we will use 1 million iterations .It will run the code and show in the dbms_output the lapsed time


declare
l_time number := DBMS_UTILITY.get_time;
l_iterations number:=1000000;
begin
declare
d date := SYSDATE;
v varchar2 (10) := '123123';
begin
for i in 1 .. l_iterations
loop
p1 (i
, i
, d
, d
, v
, v);
end loop;
end;

DBMS_OUTPUT.put_line ('full_verbose: '||to_char((DBMS_UTILITY.get_time - l_time)/100)||
' seconds');
l_time := DBMS_UTILITY.get_time;

declare
d date := SYSDATE;
v varchar2 (10) := '123123';
begin
for i in 1 .. l_iterations
loop
p2 (i
, i
, d
, d
, v
, v);
end loop;
end;

DBMS_OUTPUT.put_line ('nvl: '||to_char((DBMS_UTILITY.get_time - l_time)/100)||' seconds');
l_time := DBMS_UTILITY.get_time;

declare
d date := SYSDATE;
v varchar2 (10) := '123123';
begin
for i in 1 .. l_iterations
loop
p3 (i
, i
, d
, d
, v
, v);
end loop;
end;

DBMS_OUTPUT.put_line ('coalesce: '||to_char((DBMS_UTILITY.get_time - l_time)/100)||
' seconds');
end;



I run it several times (30 runs) and the results were similar to the 3 classes below:
Class 1: full_verbose wins!

full_verbose: ,26 seconds
nvl: 1,22 seconds
coalesce: ,34 seconds

Class 2: coalesce wins!

full_verbose: .42 seconds
nvl: 1.21 seconds
coalesce: .35 seconds

and sometimes Class 3: there's a tie!

full_verbose: .36 seconds
nvl: 1.22 seconds
coalesce: .36 seconds

The ranges of values
full_verbose: .29 - .45
nvl: 1.21 - 1.23
coalesce: .34 - .36

ok its in 1 million iterations...maybe it's not very significant in most of applications, but...
I think it's very odd that nvl gets soo bad :4 times slower!!
I alse think that coalesce is a not very wide used or even known function but, is as readable as nvl and less code (and easy readable) than the full verbose approach.

What do you think?

PS: the answer to the post title question is "Yes!"

Update:
range results with 100 million iterations and 10 runs:
full_verbose: 21.11 - 27.5 seconds
nvl: 123.39 - 124.23 seconds
coalesce: 36.87 - 37.54 seconds

In the long run the verbose version can be seen as really the fastest...

Update: the explanation for the results is that NVL doesn't make short-circuit but the verbose one and coalesce do. Note: Coalesce only does that since 10g (in 9i it behaves like NVL)
So please do not use NVL function, specially if you use functions inside the NVL.