Posted by 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
Learning is not a spectator sport 
Thanks for the article. I have quite a few of those triggers. We have recently upgraded to 19.11.0 and I was wondering you happen to have a list of all your blog posts strictly applicable to version 19c? I’m interested in knowing “What’s new in PL/SQL in 19c” and anything else you can suggest so I can quickly start taking advantage of the new programmatic features or enhancements. Thanks! 😊
https://connor-mcdonald.com/?s=19c&tag=19c should get you close
….
I gave a talk at Kscope21 yesterday about common mistakes
….
Thanks, where can we get the contents for this presentation? dont see under “asktom” resource tab.
“slow” should have been a RESERVED WORD !
— Start of DDL Script for Package XP.XA_TRG_UTL
— Generated 24.06.2021 19:00:04 from XP@OBTP
CREATE OR REPLACE
PACKAGE xa_trg_utl
AUTHID CURRENT_USER
IS
————————————————————————- TYPES, TABLES
SUBTYPE st_dml_user IS VARCHAR2 (100);
————————————————————————- SUBTYPES
————————————————————————- CONSTANTS
————————————————————————- VARIABLES
————————————————————————- CURSORS
————————————————————————- UNITS
—
FUNCTION get_dml_user
/**
* Gibt den Usernamen zurück unter dem DML ausgeführt wird
* Wenn unter einem Apex-Context gearbeitet wird, ist das der APEX-User
* ansonsten der Oracle-User unter dem die Session läuft
* @AUTHOR: AMei 20111229
* @PARAM: void
* @RETURN: VARCHAR2
* @EXCEPTION:
* @SEE:
*/
RETURN VARCHAR2;
FUNCTION inserting_flag
RETURN VARCHAR2;
FUNCTION updating_flag
RETURN VARCHAR2;
FUNCTION deleting_flag
RETURN VARCHAR2;
END xa_trg_utl;
/
— Grants for Package
GRANT EXECUTE ON xa_trg_utl TO public
/
CREATE OR REPLACE
PACKAGE BODY xa_trg_utl
IS
/**
* Dieses Package stellt Hilfs-Units und globale Werte für die
* Benutzng in Triggern (insbesondere Historien und Audith) bereit.
* HINWEIS:
* CREATE PUBLIC SYNONYM xa_trg_utl FOR xp.xa_trg_utl;
* GRANT EXECUTE ON xa_trg_utl TO PUBLIC;
*/
—
/* ÄNDERUNGSHISTORIE
Nr Datum Bearb. Beschreibung
#001 20171016 AMei Anpassung Implementierung GET_DML_USER
#000 20111229 AMei initial erstellt
*/
—
————————————————————————- SUBTYPES
————————————————————————- TYPES, TABLES
————————————————————————- CONSTANTS
gc_this_package_name xp_gc.st_ora_object_name := ‘XA_TRG_UTL’;
gc_inserting_flag CONSTANT VARCHAR2 (1) := ‘I’;
gc_updating_flag CONSTANT VARCHAR2 (1) := ‘U’;
gc_deleting_flag CONSTANT VARCHAR2 (1) := ‘D’;
————————————————————————- VARIABLES
—
gv_curr_dml_user wwv_flow_users.user_name%TYPE;
————————————————————————- CURSORS
————————————————————————- FOREWARD-DECLARATIONS
————————————————————————- Standard-UNITS
—
PROCEDURE init
IS
/**
* Diese Prozedur führt Initalisierungen aus, die für die weitere Bearbeitung
* des aktuellen Packages benötigt werden.
*/
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END init;
PROCEDURE free
IS
/**
* Diese Prozedur wird nach der kompletten Abarbeiung eines Packages aufgerufen.
* Sie enthält Code zum Aufräumen des Systems. (Beisp.: Speicher freigeben)
*/
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END free;
—
PROCEDURE init_gv
/**
* Initialisiert globale Variablen die feste Text repräsentieren.
* @AUTHOR: AMei 20110219
* @PARAM: void
* @RETURN: void
* @EXCEPTION:
* @SEE:
*/
IS
BEGIN
gv_curr_dml_user :=
NVL (UPPER (v (‘APP_USER’)),
NVL (SYS_CONTEXT (‘USERENV’, ‘OS_USER’), SYS_CONTEXT (‘USERENV’, ‘SESSION_USER’)));
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END init_gv;
—
FUNCTION get_dml_user
/**
* Gibt den Usernamen zurück unter dem DML ausgeführt wird
* Wenn unter einem Apex-Context gearbeitet wird, ist das der APEX-User
* ansonsten der Oracle-User unter dem die Session läuft
* @AUTHOR: AMei 20111229, AMei 20171016 Umstellugn von GV-Zuweisung auf LV-Ermittlung
* @PARAM: void
* @RETURN: VARCHAR2
* @EXCEPTION:
* @SEE:
*/
RETURN VARCHAR2
IS
lv_retval_dml_user wwv_flow_users.user_name%TYPE;
BEGIN
— RETURN gv_curr_dml_user;
lv_retval_dml_user :=
UPPER (COALESCE (SYS_CONTEXT (‘APEX$SESSION’, ‘APP_USER’), — set by APEX application
SYS_CONTEXT (‘JSP$SESSION’, ‘USER_ID’), — set by future JSP application
— place future application environment SYS_CONTEXT here
SYS_CONTEXT (‘userenv’, ‘client_identifier’), — set by Oracle
USER));
RETURN lv_retval_dml_user;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END get_dml_user;
—
FUNCTION inserting_flag
RETURN VARCHAR2
IS
BEGIN
RETURN gc_inserting_flag;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END inserting_flag;
—
FUNCTION updating_flag
RETURN VARCHAR2
IS
BEGIN
RETURN gc_updating_flag;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END updating_flag;
—
FUNCTION deleting_flag
RETURN VARCHAR2
IS
BEGIN
RETURN gc_deleting_flag;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END deleting_flag;
—
BEGIN
init_gv;
EXCEPTION
WHEN OTHERS
THEN
RAISE;
END xa_trg_utl;
/
— End of DDL Script for Package XP.XA_TRG_UTL
not entirely sure what this is for?
Slightly Off Topic: MODIFIED_BY as used above is usually a flaw in itself. You capture only the last update.