Wednesday 30 January 2019

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?

2 comments:

Tony said...

I've been on the coalesce bandwagon for a few years now. I think it was Jonathan Lewis, maybe Tom Kyte that first pointed out that coalesce stops the evaluation process if the first value is null, that short circuit is huge, especially if your second value requires a fair amount of processing to determine the value (i.e. a function).

This is one of the things I wish Quick SQL did by default, right now when it creates the trigger code for you it uses NVL instead of coalesce.

Anonymous said...

As of May 14th, 2021, Quick SQL uses COALESCE in the triggers it creates.