More 12.2 PL/Scope Magic: Find SQL statements that call user-defined functions

When a SQL statement executes a user-defined function, your users pay the price of a context switch, which can be expensive, especially if the function is called in the WHERE clause. Even worse, if that function itself contains a SQL statement, you can run into data consistency issues.

Fortunately, you can use PL/Scope in Oracle Database 12c Release 2 to find all the SQL statements in your PL/SQL code that call a user-defined function, and then analyze from there.

I go through the steps below. You can run and download all the code on LiveSQL.

First, I turn on the gathering of PL/Scope data in my session:

ALTER SESSION SET plscope_settings='identifiers:all, statements:all'
/

Then I create a table, two functions and a procedure, so I can demonstrate this great application of PL/Scope:

CREATE TABLE my_data (n NUMBER)
/

CREATE OR REPLACE FUNCTION my_function1
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE FUNCTION my_function2
   RETURN NUMBER
   AUTHID DEFINER
IS
BEGIN
   RETURN 1;
END;
/

CREATE OR REPLACE PROCEDURE my_procedure (n_in IN NUMBER)
   AUTHID DEFINER
IS
   l_my_data   my_data%ROWTYPE;
BEGIN
   SELECT my_function1 ()
     INTO l_my_data
     FROM my_data
    WHERE     n = n_in
          AND my_function2 () = 0
          AND n = (SELECT my_function1 () FROM DUAL);

   SELECT COUNT (*)
     INTO l_my_data
     FROM my_data
    WHERE n = n_in;

   UPDATE my_data
      SET n = my_function2 ()
    WHERE n = n_in;
END;
/

Note that only two of the three DML statements in MY_PROCEDURE contain a function call (the first query and the update).

Now I UNION ALL rows from ALL_STATEMENTS and ALL_IDENTIFIERS to get a full picture:

WITH one_obj_name AS (SELECT 'MY_PROCEDURE' object_name FROM DUAL)
    SELECT plscope_type,
           usage_id,
           usage_context_id,
           LPAD (' ', 2 * (LEVEL - 1)) || usage || ' ' || name usages
      FROM (SELECT 'ID' plscope_type,
                   ai.object_name,
                   ai.usage usage,
                   ai.usage_id,
                   ai.usage_context_id,
                   ai.TYPE || ' ' || ai.name name
              FROM all_identifiers ai, one_obj_name
             WHERE ai.object_name = one_obj_name.object_name
            UNION ALL
            SELECT 'ST',
                   st.object_name,
                   st.TYPE,
                   st.usage_id,
                   st.usage_context_id,
                   'STATEMENT'
              FROM all_statements st, one_obj_name
             WHERE st.object_name = one_obj_name.object_name)
START WITH usage_context_id = 0
CONNECT BY PRIOR usage_id = usage_context_id
/

And I see these results:

PLSCOPE_TYPE    USAGE_ID    USAGE_CONTEXT_ID    USAGES
ID    1    0    DECLARATION PROCEDURE MY_PROCEDURE
ID    2    1      DEFINITION PROCEDURE MY_PROCEDURE
ID    3    2        DECLARATION FORMAL IN N_IN
ID    4    3          REFERENCE NUMBER DATATYPE NUMBER
ID    5    2        DECLARATION VARIABLE L_MY_DATA
ID    6    5          REFERENCE TABLE MY_DATA
ST    7    2        SELECT STATEMENT
ID    8    7          REFERENCE TABLE MY_DATA
ID    9    7          REFERENCE COLUMN N
ID    10    7          REFERENCE FORMAL IN N_IN
ID    11    7          REFERENCE COLUMN N
ID    13    7          CALL FUNCTION MY_FUNCTION1
ID    14    7          CALL FUNCTION MY_FUNCTION2
ID    15    7          ASSIGNMENT VARIABLE L_MY_DATA
ID    16    15            CALL FUNCTION MY_FUNCTION1
ST    17    2        SELECT STATEMENT
ID    18    17          REFERENCE TABLE MY_DATA
ID    19    17          REFERENCE FORMAL IN N_IN
ID    20    17          REFERENCE COLUMN N
ID    21    17          ASSIGNMENT VARIABLE L_MY_DATA
ST    22    2        UPDATE STATEMENT
ID    23    22          REFERENCE TABLE MY_DATA
ID    24    22          REFERENCE FORMAL IN N_IN
ID    25    22          REFERENCE COLUMN N
ID    26    22          REFERENCE COLUMN N
ID    27    22          CALL FUNCTION MY_FUNCTION2

OK. Now let's get to the substance of this blog post. I use subquery refactoring (WITH clause) to create and then use some data sets: my_prog_unit - specify the program unit of interest just once; full_set - the full set of statements and identifiers; dml_statements - the SQL DML statements in the program unit. Then I find all the DML statements whose full_set tree below it contain a call to a function.

WITH my_prog_unit AS (SELECT USER owner, 'MY_PROCEDURE' object_name FROM DUAL),
     full_set
     AS (SELECT ai.usage,
                ai.usage_id,
                ai.usage_context_id,
                ai.TYPE,
                ai.name
           FROM all_identifiers ai, my_prog_unit
          WHERE ai.object_name = my_prog_unit.object_name
            AND ai.owner = my_prog_unit.owner
         UNION ALL
         SELECT st.TYPE,
                st.usage_id,
                st.usage_context_id,
                'type',
                'name'
           FROM all_statements st, my_prog_unit
          WHERE st.object_name = my_prog_unit.object_name
            AND st.owner = my_prog_unit.owner),
     dml_statements
     AS (SELECT st.owner, st.object_name, st.line, st.usage_id, st.type
           FROM all_statements st, my_prog_unit
          WHERE     st.object_name = my_prog_unit.object_name
                AND st.owner = my_prog_unit.owner
                AND st.TYPE IN ('SELECT', 'UPDATE', 'DELETE'))
SELECT st.owner,
       st.object_name,
       st.line,
       st.TYPE,
       s.text
  FROM dml_statements st, all_source s
 WHERE     ('CALL', 'FUNCTION') IN (    SELECT fs.usage, fs.TYPE
                                          FROM full_set fs
                                    CONNECT BY PRIOR fs.usage_id =
                                                  fs.usage_context_id
                                    START WITH fs.usage_id = st.usage_id)
       AND st.line = s.line
       AND st.object_name = s.name
       AND st.owner = s.owner
/

And I see these results:

STEVEN    MY_PROCEDURE    6    SELECT       SELECT my_function1 ()
STEVEN    MY_PROCEDURE    18    UPDATE"   UPDATE my_data

Is that cool or what?