PL/SQL performance in 19c
I gave a talk at Kscope21 yesterday about common mistakes that we make during the database development cycle. To kick things off, I mentioned a customer I assisted many years ago because they had misinterpreted the mechanism to do assignments in PL/SQL.
They had observed that to get the value of a SEQUENCE, they had to code “SELECT MY_SEQUENCE.NEXTVAL FROM DUAL”. (This was pre-9i days). Because of this, they mistakenly thought that all such assignments in PL/SQL that used a database function required a SELECT-FROM-DUAL. Thus their code was littered with:
select SUBSTR(…) from dual;
select INSTR(…) from dual;
select UPPER(…) from dual;
and so forth. Predictably, they were having performance issues and that’s why I had been asked to assist.
A common mistake
I started the presentation with this example, because often the audience has that (unspoken) reaction of “**We would never be so stupid”, but I then demonstrate that in fact the vast majority of us commit that very same mistake, just in a more subtle way.
A very common design pattern for database tables is to capture auditing details about who created and/or modified a row, and when that event occurred. Thus many tables have columns such as
CREATED_BY
CREATED_DATE
MODIFIED_BY
MODIFIED_DATE
and to absolve the application from the task of populating these columns, the job is done using triggers. A typical trigger contains some simple assignments:
begin
if inserting then
:new.created_by := user;
:new.created_date := sysdate;
end if;
:new.modified_by := user;
:new.modified_date := sysdate;
end;
Unbeknown to the developer, they have silently fallen into the same mistake as the initial mistake I just mentioned. When I perform a series of INSERT statements against a table defined with such a trigger, the trace file reveals an interesting side-effect
INSERT INTO T (ID,ENAME)
VALUES (:B1 ,:B2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100000 7.20 7.83 132 1048 106235 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 100001 7.20 7.83 132 1048 106235 100000
SELECT USER
FROM SYS.DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 200000 0.84 0.93 0 0 0 0
Fetch 200000 0.15 0.29 0 0 0 200000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 400001 1.00 1.22 0 0 0 200000
The reference to “USER” in the trigger code has been evaluated under the covers with a SELECT-FROM-DUAL. So perhaps we are all a little guilty of excessive SELECT-FROM-DUAL in our codebase, even if we have not misinterpreted the workings of the assignment statement in PL/SQL as the first customer did.
There is an easy fix to this performance overhead. You replace the reference to USER with a pre-supplied context variable.
begin
if inserting then
:new.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
:new.created_date := sysdate;
end if;
:new.modified_by := SYS_CONTEXT('USERENV', 'SESSION_USER');;
:new.modified_date := sysdate;
end;
This does not require a SELECT-FROM-DUAL and yields a considerable performance improvement in the trigger code. code especially in high frequency transaction environments.
What about APEX?
After the session, I popped into the “Database Q&A” virtual room and Alexander Steiner reached out with a question pertaining to the way triggers are used in APEX to capture this audit information. A typical trigger in APEX might be the along the lines of:
:new.modified_by := nvl(v('USER'),user);
in order to capture the logged on APEX user, and if not, fall back to the database account. Alexander was asking if there would be the same overheads here, given that the value of USER would only be required if the APEX session user was not present. Unfortunately, even in this instance, we have not escaped the call to USER because of the way NVL works. NVL evaluates the second parameter even if it is not used. We can easily demonstrate that with a function that takes a long time to return:
SQL> create or replace
2 function slow return number is
3 begin
4 dbms_session.sleep(10);
5 return 1;
6 end;
7 /
Function created.
SQL>
SQL> set timing on
SQL> select nvl(1,slow) from dual;
NVL(1,SLOW)
-----------
1
Elapsed: 00:00:10.02
So after some back and forth, I recommended to Alexander that a good alternative would be:
use COALESCE instead, and
use the context variables
thus ending up in new trigger code as below:
:new.modified_by := coalesce(SYS_CONTEXT('APEX$SESSION', 'APP_USER'),SYS_CONTEXT('USERENV', 'SESSION_USER'));
A closer look at 19c
If we know that USER does a SELECT-FROM-DUAL, and Oracle knows that USER does a SELECT-FROM-DUAL, and everyone knows that we can replace it with a call to SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’) and no longer get a SELECT-FROM-DUAL, then there is an obvious question here that I imagine you’ve got brewing in your own mind:
“Why the hell doesn’t Oracle just replace USER with the damn context variable? ”
That’s a valid question, and you’ll be pleased to know, we have a valid answer!
If you peek into the package body for the STANDARD package (this the package that “defines” PL/SQL), you will see the following changes in 19c.
-- Bug 27091470: Calling SYS_CONTEXT results in a considerable
-- speed-up as opposed to directly calling SQL like before.
function USER return varchar2 is
begin
return SYS_CONTEXT('USERENV', 'SESSION_USER');
end;
So there you have it. Once you get to 19c, we have finally removed the performance overhead from calling USER in a trigger. Until you get to 19c, you’ll need to make the code changes yourself, but this is yet another justification to upgrade to 19c and be on the latest long term support release. You get a better database – simple as that.
It also means I need to update my slide deck
TL;DR
19c makes calls to the USER function more efficient