Oracle-DB: Check user-defined PL/SQL functions for missing DETERMINISTIC flag

If using user.defined PL/SQL functions in SQL, it is crucial for the optimizer to know if a function is deterministic or not. Deterministic means, that the result of a function call is stable for subsequent calls with the same call paramater values. You need to tell the optimizer that the result can be considered as stable by adding the keyword DETERMINISTIC to the function declaration.

CREATE OR REPLACE FUNCTION MyFunc() RETURN NUMBER DETERMINISTIC ... 

If knowing that a function is deterministic, the SQL engine can cache the function results for repeated calls of this function. This may make a huge difference for the SQL execution effort.

Consider an example:
A table MyTab has 1 mio. records. A package function does some calculations returning a value that is used as filter criteria at access on MyTab.

CREATE TABLE MyTab(ID NUMBER);

INSERT INTO MyTab SELECT Level FROM DUAL CONNECT BY LEVEL <= 1000000;
COMMIT;

CREATE OR REPLACE PACKAGE MyPack AS
  FUNCTION MyFunc RETURN NUMBER;
  PROCEDURE Reset;
END MyPack;
/

CREATE OR REPLACE PACKAGE BODY MyPack AS
  Counter NUMBER := 0;

  FUNCTION MyFunc RETURN NUMBER IS
  BEGIN
    Counter := Counter + 1;
    RETURN 5;
  END MyFunc;
  
  PROCEDURE Reset IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Counter = '||Counter);
    Counter := 0;
  END Reset;
END MyPack;  
/

Now let's check what happens.

SET SERVEROUTPUT ON;
SELECT * FROM MyTab WHERE ID = MyPack.MyFunc;
EXEC MyPack.Reset;

5
Counter = 1000000

The table MyTab was read by full table scan, only one of the 1 million rows equals the function return value 5.
The function call is not related to any row value of the table, nethertheless the fuction has been called 1 mio times, because the DB could not know if it will return the same value for each call.

Now add the keyword DETERMINISTIC to the function declaration and try again:

CREATE OR REPLACE PACKAGE MyPack AS
  FUNCTION MyFunc RETURN NUMBER DETERMINISTIC;
  PROCEDURE Reset;
END MyPack;  
/

Counter = 1

The function needs to be called only once.
So there might be a huge difference in execution time for your SQLs if you have such pittfalls in your function declarations where really deterministic functions are not declared as such.


But how to recognize which functions could be worth to declare as DETERMINISTIC?

The following appproach scans your DB for long running SQLs that uses non-deterministic user-defined PL/SQL functions.
It's up to you to evaluate and decide if this functions could be tagged as DETERMINISTIC or not. But this selection gives you an initial insight into where pitfalls such as those in the above example may be hidden.

The reuse of function results is limited to a single SQL execution. It might therefore also make sense to label functions as DETERMINISTIC that are not actually deterministic.
For example, if a PL/SQL function selects values from master data tables, then the function is not determinisitic because the content of this tables may change. But in reality, you are not interested in detecting such a change during a singe SQL execution. So you can tag this function DETERMINISTIC nethertheless and ensure that is is called much less frequently than otherwise.

SET SERVEROUTPUT ON;
DECLARE
  TYPE proc_RT IS RECORD(
    Owner         VARCHAR2(128),
    Package_Name  VARCHAR2(128),
    Function_Name VARCHAR2(128)
  );
  TYPE Proc_TT IS TABLE OF Proc_RT INDEX BY VARCHAR2(2000);
  proc_table Proc_TT;

  TYPE Char_Table_Type IS TABLE OF CHAR(1);
  Char_Table  Char_Table_Type := Char_Table_Type(' ', '(', '!', '=', '<', '>', '+', '-', '*', '/'); -- delimiters chars to search for
  char_table_count INTEGER  := Char_Table.COUNT;

  Start_Pos   INTEGER;
  End_pos     INTEGER;
  Test_Pos    INTEGER;
  l_text      CLOB;
  l_match     VARCHAR2(4000);
  full_match  VARCHAR2(4000);
  l_json      VARCHAR2(4000);
  l_count     INTEGER;
  l_stmts     INTEGER;

  FUNCTION JSON_Esc(p_In VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN REPLACE(p_In, '\"', '\\\\\"');
  END JSON_Esc;

BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  FOR p_Rec IN (SELECT Owner, Package_Name, Function_Name, Compare_Name
                FROM   (
                        SELECT Owner, NULL Package_Name, Object_Name Function_Name, Deterministic, Object_Name Compare_Name
                        FROM   DBA_Procedures
                        WHERE  Object_Type = 'FUNCTION'
                        UNION ALL
                        SELECT p.Owner, p.Object_Name Package_Name, p.Procedure_Name Function_Name, p.Deterministic, p.Object_Name||'.'||p.Procedure_Name Compare_Name
                        FROM   DBA_Procedures p
                        JOIN   DBA_Arguments a ON a.Owner = p.Owner AND a.Package_Name = p.Object_Name AND a.Object_Name = p.Procedure_Name
                        WHERE  p.Object_Type = 'PACKAGE'
                        AND    a.Argument_Name IS NULL /* Program is a function with a return value */
                       )
                WHERE  Owner NOT IN (SELECT UserName FROM All_Users WHERE Oracle_Maintained = 'Y')
                AND    Deterministic = 'NO'
               ) LOOP
    -- Lookup with function name only
    proc_table(p_Rec.Compare_Name) := Proc_RT(p_Rec.Owner, p_Rec.Package_Name, p_Rec.Function_Name);
    -- Lookup with function name qualified with owner
    proc_table(p_Rec.Owner||'.'||p_Rec.Compare_Name) := Proc_RT(p_Rec.Owner, p_Rec.Package_Name, p_Rec.Function_Name);
  END LOOP;

  l_stmts := 0;
  FOR t_Rec IN (SELECT Instance_Number, SQL_ID, Parsing_Schema_Name, SQL_Text, Elapsed_Secs
                FROM   (
                        SELECT Instance_Number, SQL_ID, Parsing_Schema_Name,
                               LTRIM(SQL_Text, CHR(10) || CHR(13) || ' ' || CHR(9)) SQL_Text,  /* remove leading whitespaces from SQL */
                               SUM(Elapsed_Secs) OVER (PARTITION BY Instance_Number, SQL_ID, Parsing_Schema_Name) Elapsed_Secs,
                               ROW_NUMBER() OVER (PARTITION BY Instance_Number, SQL_ID, Parsing_Schema_Name ORDER BY 1) AS rn  /* Because of no aggregate functions on CLOB */
                        FROM   (
                                SELECT Inst_ID Instance_Number, SQL_ID, UPPER(SQL_FullText) SQL_Text, Parsing_Schema_Name, Elapsed_Time/1000000 Elapsed_Secs
                                FROM   gv$SQLArea
                                WHERE   Command_Type != 47 /* No PL/SQL */
                                UNION ALL
                                SELECT h.Instance_Number, h.SQL_ID, UPPER(t.SQL_Text) SQL_Text, h.Parsing_Schema_Name, h.Elapsed_Secs
                                FROM   (SELECT st.Instance_Number, st.SQL_ID, st.Parsing_Schema_Name, SUM(st.Elapsed_Time_Delta)/1000000 Elapsed_Secs
                                        FROM   DBA_Hist_SQLStat st
                                        JOIN   DBA_Hist_Snapshot ss ON ss.DBID = st.DBID AND ss.Snap_ID = st.Snap_ID AND ss.Instance_Number = st.Instance_Number
                                        WHERE  ss.Begin_Interval_Time > SYSDATE - 2 /* Number of days back considered */
                                        GROUP BY st.Instance_Number, st.SQL_ID, st.Parsing_Schema_Name
                                       ) h
                                JOIN   DBA_Hist_SQLText t ON t.SQL_ID = h.SQL_ID
                               )
                        WHERE Elapsed_Secs > 100 /* Min elapsed time to be considered */
                       )
                WHERE  RN = 1 /* Select one occurrence of SQL text per SQL-ID only */
                AND    SQL_Text NOT LIKE 'BEGIN%' /* No command_type in DBA_Hist_SQLStat to filter PL/SQL */
                AND    SQL_Text NOT LIKE 'DECLARE%'
               ) LOOP
    l_stmts := l_stmts + 1;
    Start_Pos := 1;
    l_text := t_Rec.SQL_Text;
    l_Count := 0;
    LOOP
      -- Look for the next delimiter
      End_pos := 0; -- start value
      FOR i IN 1..char_table_count LOOP
        Test_Pos := INSTR(l_text, Char_Table(i), Start_Pos);
        IF Test_Pos > 0 AND ( End_Pos = 0 OR Test_Pos < End_Pos) THEN
          End_Pos := Test_Pos;
        END IF;
      END LOOP;

      BEGIN
        IF End_Pos > 0 THEN
          l_match := SUBSTR(l_text, Start_Pos, End_pos-Start_Pos);
        ELSE
          l_match := SUBSTR(l_text, Start_Pos); -- The rest of the string
        END IF;
        l_Count := l_Count + 1;
        -- DBMS_OUTPUT.PUT_LINE(l_match);

        IF proc_Table.EXISTS(l_match) -- Either owner and function name or only function name match
        THEN
          -- Get the following parameter for matching function call
          -- Check if parameters follow immediately (after end_pos are only spaces up to an opening parenthesis)
          full_match := l_match; -- Default if no parameter list follows
          IF End_Pos > 0 AND REGEXP_LIKE(SUBSTR(l_text, End_Pos), '^\\s*\\(') THEN -- opening parenthesis directly after or after spaces
            End_Pos := INSTR(l_text, ')', Start_Pos);
            IF End_Pos > 0 THEN
              full_match := SUBSTR(l_text, Start_Pos, End_pos-Start_Pos + 1);
            ELSE
              full_match := SUBSTR(l_text, Start_Pos); -- The rest of the string
            END IF;
          END IF;


          l_json := '{' ||
                    '\"Instance\": '               || t_Rec.Instance_Number              || ', '   ||
                    '\"SQL-ID\": \"'               || JSON_Esc(t_Rec.SQL_ID)             || '\", ' ||
                    '\"Parsing Schema Name\": \"'  || t_Rec.Parsing_Schema_Name          || '\", ' ||
                    '\"Elapsed Secs\": '           || ROUND(t_Rec.Elapsed_Secs)          || ', '   ||
                    '\"Owner\": \"'                || proc_Table(l_match).Owner          || '\", ' ||
                    '\"Package Name\": \"'         || proc_Table(l_match).Package_Name   || '\", ' ||
                    '\"Function Name\": \"'        || proc_Table(l_match).Function_Name  || '\", ' ||
                    '\"Match in SQL\": \"'         || JSON_Esc(full_match)               || '\" '  ||
                    '}';
          DBMS_OUTPUT.PUT_LINE(l_json);
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          IF SQLCODE != -6502 THEN -- End_Pos > 4000 raises ORA-06502: PL/SQL: numeric or value error: character string buffer too small
            RAISE;
          END IF;
      END;
      EXIT WHEN End_Pos = 0;
      Start_Pos := End_Pos + 1;
    END LOOP;
  END LOOP;
END;

Much more convenient you can execute this check using my free analysis tool Panorama, there this check is among lots of others in the section "Dragnet selection".