Oracle USER vs sys_context

This post was inspired by the fact I couldn't find many good references for what I thought was 'previously discovered truth'. So what does any good science-nut do? Add their own contribution.

So here are two simple performance suggestions. The second was an added bonus I realised I could demonstrate simply.

1) Stop using USER

I'm using USER far less frequently anyway, since it has no context in Oracle APEX, but it is still a handy default value for created_by colums, and I'm sure some Forms programmers could add life with a small refactor.

Sven explores this in his excellent post regarding triggers in 12c (spoiler: there is typically no need for a trigger).
I thought Tim had a section in an article similar to this, but I couldn't find it again.
And there's a tweet. There's always a tweet.

I executed the following on a development server, and it took 47, 50, 50 seconds respectively.

declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := user;
  end loop;
end;
/

50 seconds

Replacing USER with sys_context('userenv','session_user') took an order of magnitude lower at 2.5, 2.4, and 2.4 seconds.

declare 
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := sys_context('userenv','session_user');
  end loop;
end;
/

2.5 seconds

It's no longer context switching between SQL and PL/SQL.

2) Stop using NVL

Instead, consider COALESCE, or other null-related functions.

So consider the previous test, but NVLing both expressions. At 58 seconds, the time taken seems like the sum of both, plus time to evaluate. Connor has more detail on the difference.

declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := nvl(sys_context('userenv','session_user'),user);
  end loop;
end;
/

58 seconds

By swapping the NVL with a COALESCE, you utilise a programming concept called 'short circuit evaluation'.  Results: 2.5, 3.1, 2.5 seconds.

declare
  v_result varchar2(100);
begin
  for i in 1..1000000 loop
  v_result := coalesce(sys_context('userenv','session_user'),user);
  end loop;
end;
/

2.5 seconds

I'm sure there's the odd "it depends", but if you don't take the performance freebies, what are things going to be like when you tackle to nasty queries?