Search

Top 60 Oracle Blogs

Recent comments

Oracle 12c – PL/SQL in SELECT (WITH & PRAGMA UDF)

Oracle 12c – PL/SQL in SELECT
(using WITH & PRAGMA UDF)

It is often useful to execute PL/SQL functions as part of an SQL SELECT statement (or other DML). When SQL calls PL/SQL a “context switch” occurs as execution moves between SQL execution and PL/SQL execution. In Oracle Database 12c Oracle has given us two tools to help reduce the impact of this context switching. The first is the ability to add a local function definition to the SELECT via the WITH clause, the second is to flag a function using PRAGMA UDF so that the compiler and optimizer will know it is to be used from SQL. Both options can measurably improve performance if used correctly.

To achieve maximum performance gains the PL/SQL function involved should not call other PL/SQL procedures and/or PL/SQL functions nor should PRAGMA UDF functions be called from “normal” PL/SQL (not inside SQL).
 

Normal Function Use in SQL (for comparison)

The following function definition and subsequent tests provide a baseline for comparison of WITH and PRAGMA UDF performance.

create or replace function times_42(inval number)
  return number 
as
begin
  return inval * 42;
end;
/
select channel_id,count(*) nbr_rows,sum(quantity_sold) qtysold, sum(times_42(cust_id)) cust42
  from sh.sales
  group by channel_id
  order by channel_id;

Elapsed (Wall-Clock) Execution Times

Running the above code three times (using SQL*Plus SET TIMING ON) resulted in elapsed times as follows:

1 – 2.018

2 – 1.945

3 – 1.928

 

WITH

Oracle 12c allows definition of PL/SQL Functions and Procedures using SQL’s Common Table Expression (WITH).

Defining PL/SQL locally reduces SQL-PL/SQL context-switching costs perhaps significantly improving performance.

Some points of caution:

  • Local PL/SQL overrides stored PL/SQL with the same name
  • Local PL/SQL is not stored in the database
  • Local PL/SQL is part of the same source code as the SQL that uses it
    (and would have to be copied if reused, yow!)
  • PL/SQL Result Cache does not cache Local PL/SQL

To use the feature in a sub-query SELECT, the main query must use the “/*+ WITH_PLSQL */” hint to avoid a syntax error.
 

Example Using WITH

with function times_42(inval number)
      return number
     as
     begin
        return inval * 42;
     end;
select channel_id,count(*) nbr_rows,
             sum(quantity_sold) qtysold,
             sum(times_42(cust_id)) cust42
  from sh.sales
  group by channel_id
  order by channel_id
/

 

Elapsed (Wall-Clock) Execution Times

Running the above code three times (using SQL*Plus SET TIMING ON) resulted in elapsed times as follows:

1 – .854

2 – .825

3 – .929

 

PRAGMA UDF

A related tool to reduce context switching in Oracle 12c allows functions to be defined using “PRAGMA UDF” to specify that a function will be used in SELECTS (behaving similar to function in WITH). This optimizes code for use within a SELECT or other SQL. Using PRAGMA UDF is probably not a good option for functions also used directly from PL/SQL (not nice to lie to the database).
 

Example Using PRAGMA UDF

create or replace function times_42(inval number)
  return number
as
  pragma udf;
begin
  return inval * 42;
end;
/
select channel_id,count(*) nbr_rows,
           sum(quantity_sold) qtysold, 
           sum(times_42(cust_id)) cust42
  from sh.sales
  group by channel_id
  order by channel_id;

 

Elapsed (Wall-Clock) Execution Times

Running the above code three times (using SQL*Plus SET TIMING ON) resulted in elapsed times as follows:

1 – .667

2 – .602

3 – .664

 

Performance Implications

All examples in this article use the Oracle-supplied SH.SALES table (>900k rows) running on a single-CPU laptop with Oracle 12c Standard Edition to allow you to recreate them. I was unable to find a tool to accurately measure context switching (if you know of one please send me a note); so, elapsed time is used in the comparisons.

Both WITH and PRAGMA UDF options provide a dramatic improvement in runtimes and in my testing (with other data sources and functions too) the PRAGMA UDF seemed to consistently out-perform a local function using WITH. Here is a table showing the comparative elapsed times shown in this unit:

1st Run 2nd Run 3rd Run
Compiled Function in Database 2.018 1.945 1.928
Function in WITH 0.854 0.825 0.929
Compiled UDF Function in Database 0.667 0.602 0.664

 

Conclusion

In this case my first choice is to use PRAGMA UDF because having a single copy of the function code seems so much more maintainable than having code repeated; the consistently better performance provides an added incentive.

Clearly, there are savings to be had when PL/SQL is called from SQL with a couple of caveats:

  • If the PL/SQL in question calls other PL/SQL, then, WITH and UDF might not be the best choice.
  • If a PL/SQL function will be called from PL/SQL, UDF may cause performance to be off since the optimization will be incorrect.

As always when performance is involved; don’t take my word for it (or anyone else’s). Test, test, and test again in your own environment with your own data!