Obsessed with Oracle PL/SQL

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

Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations: FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END betwnstr; I can then call it in a SQL statement: SELECT bewtnstr (last_name, 3, 6) FROM employees Nice, right? But there's a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function. All of that takes time. And we'd much rather it didn't. Since...