19c scheduler and jobs

PL/SQL performance in 19c

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! Smile

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 Smile

TL;DR

19c makes calls to the USER function more efficient

7 comments

  1. 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! 😊

  2. ….
    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.

  3. — 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

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.