PRAGMA UDF: A One Line Trick to Speed Up PL/SQL Functions in SQL

The Context Switch Tax

When you call a PL/SQL function from a SQL statement, Oracle must switch execution context from the SQL engine to the PL/SQL engine for every row processed. This context switch has a fixed overhead cost that adds up quickly on large result sets. A function that takes microseconds to execute may add seconds or minutes to a query simply because of the switching overhead on millions of rows.

PRAGMA UDF: One Line, Real Impact

Starting in Oracle 12c, the PRAGMA UDF directive tells the PL/SQL compiler to optimize a function specifically for being called from SQL. The compiler generates code that reduces the context switch overhead, typically improving performance by 30% to 60% when the function is called from SQL contexts.

CREATE OR REPLACE FUNCTION format_currency(
   p_amount   IN NUMBER,
   p_currency IN VARCHAR2 DEFAULT 'USD'
)
RETURN VARCHAR2
IS
   PRAGMA UDF;
BEGIN
   RETURN CASE p_currency
      WHEN 'USD' THEN '$' || TO_CHAR(p_amount, 'FM999,999,990.00')
      WHEN 'EUR' THEN TO_CHAR(p_amount, 'FM999,999,990.00') || ' EUR'
      WHEN 'GBP' THEN CHR(163) || TO_CHAR(p_amount, 'FM999,999,990.00')
      ELSE TO_CHAR(p_amount, 'FM999,999,990.00') || ' ' || p_currency
   END;
END format_currency;
/

-- Usage in SQL (this is where PRAGMA UDF helps)
SELECT order_id,
       format_currency(total_amount) AS formatted_total
  FROM orders
 WHERE order_date > SYSDATE - 30;

Benchmarking the Difference

Here is a simple test you can run to see the improvement:

-- Create test function WITHOUT PRAGMA UDF
CREATE OR REPLACE FUNCTION test_no_udf(p_val NUMBER) RETURN NUMBER IS
BEGIN
   RETURN p_val * 1.1;
END;
/

-- Create identical function WITH PRAGMA UDF
CREATE OR REPLACE FUNCTION test_with_udf(p_val NUMBER) RETURN NUMBER IS
   PRAGMA UDF;
BEGIN
   RETURN p_val * 1.1;
END;
/

-- Time comparison on a large table
SET TIMING ON

SELECT SUM(test_no_udf(employee_id)) FROM
   (SELECT LEVEL AS employee_id FROM dual CONNECT BY LEVEL <= 1000000);
-- Note the elapsed time

SELECT SUM(test_with_udf(employee_id)) FROM
   (SELECT LEVEL AS employee_id FROM dual CONNECT BY LEVEL <= 1000000);
-- Compare: typically 30-60% faster

When PRAGMA UDF Does Not Help

PRAGMA UDF only benefits functions called from SQL. If your function is called exclusively from other PL/SQL code, the pragma has no effect (and no negative impact either). For functions called from both SQL and PL/SQL contexts, PRAGMA UDF is still beneficial because the optimization only activates in the SQL calling path.

Also, if your function performs significant work (complex calculations, table lookups, or other operations that dwarf the context switch cost), the relative improvement from PRAGMA UDF will be small. The pragma is most impactful for lightweight functions called on many rows.

Combining With Other Optimizations

PRAGMA UDF pairs well with the DETERMINISTIC keyword. A function that is both deterministic and marked with PRAGMA UDF gets the benefit of both reduced context switching and potential result caching by the optimizer. For functions used in WHERE clauses or join conditions, this combination can be particularly powerful.

Version Availability

PRAGMA UDF is available from Oracle 12c Release 1 onward and works identically in 19c, 21c, and 23ai. There are no version specific behavioral differences. Once you adopt Oracle 12c or later as your minimum supported version, you can add PRAGMA UDF to all functions that are primarily called from SQL with no risk and consistent benefit.