SQL embedded into PL/SQL

A few days ago I spoke with another developer about SQL embedded into PL/SQL code and how function calls are handled in the WHERE clause of SQL statements. There is sometimes confusion who (PL/SQL engine or SQL engine) is executing it, I thought it's a good idea to write a posting about it.

Last year I have already blogged about it in Caution when using PL/SQL functions in a SQL statement, but that was in the context of writing a stand alone SQL statement for a report, ... but what actually happens if you have a SQL embedded into PL/SQL like the following example procedure code in a package

PROCEDURE processEmps
  ( pDepartmentId IN NUMBER
  )
IS
BEGIN
    FOR rEMP IN
      ( SELECT EMPLOYEE_ID
          FROM EMPLOYEES
         WHERE SALARY > getAvgDeptSalary(pDepartmentId)
      )
    LOOP
        NULL;
    END LOOP;
END processEmps;

(Note: getAvgDeptSalary isn't a very good example, because you could also do that with SQL only, but it's just an example)

Very simple. A function in the WHERE clause which uses the procedure parameter pDepartmentId. What would you expect how the code/SQL is executed?

Do you expect the code is executed in the following order?

1. Procedure processEmps is called
2. Function getAvgDeptSalary gets executed by the PL/SQL engine, because the PL/SQL engine has already all input parameters for the function call. No values from the query are needed. The result gets stored into an internal temporary bind variable.
3. Context switch to the SQL engine which executes the query on EMPLOYEES, restricted to SALARIES greater than the value in internal temporary bind variable.

Sorry you are wrong!

The hole SQL statement is handed over to the SQL engine, which then does a call to the getAvgDeptSalary function. That could be one time if you are lucky and the optimizer is clever enough, but it could also be for each row which is processed by the query!!! So it's the same processing as already described in my Caution when using PL/SQL functions in a SQL statement! You can also easily identify that behavior by yourself, because there are some restrictions in the usage of function calls in the WHERE clause:

• You can only use public functions.
• You can't use the non-positional syntax (at least in < 11g) for the function parameters.

That's because the SQL engine can only use public PL/SQL functions and can't handle the non-positional syntax for parameters.

So if you have somewhere in your code such function calls (like the V or NV functions in Oracle APEX), be aware that this could be a performance bottleneck! Do the function call before the SQL statement and use a variable to restrict the query.

« ... Read full posting ... »

Oracle Magazine: Sizing Up Performance

There is a new article about Tips and techniques for optimal Oracle Application Express performance in the new Oracle Magazine Jan/Feb issue.

« ... Read full posting ... »

Drop in replacement for V and NV function

If you have read my previous posting about Caution when using PL/SQL functions in SQL statements and a related thread on the OTN forum, you know that using the V or NV function in your SQL-where clause can have a performance impact on your query.

I have created wrapper functions for V and NV which use the DETERMINISTIC optimizer hint, so that the optimizer/query engine just calls them once for the query and not for every row.

I have also added a DV function which is simular to NV. It will return the value as date.

Installation instructions can be found in the script header.

NOTE: This drop in replacement may improve performance, but it would be best if you avoid the V function in your SQL statement, always use the bind variable syntax (:PX_VARIABLE) instead! That's is the best for the optimizer and it works with all Oracle DB versions.

DATABASE VERSION: I was just informed by Tom Kyte that

10gr2 supports deterministic as an optimization in SQL for the first
time. Prior to 10gr2 - deterministic was all about function based indexes only - the SQL engine ignored it.

So only if you have a 10.2.x database, this drop in replacement will give you a speed up of your query.

UPDATE: Seems that my link for the file doesn't work. Added the content of the script to the posting.

--******************************************************************************
--
--  PROJECT:  Apex Library
--
--  FILE:     ApexLib_V_functions_replacement.sql
--
--  DESCRIPTION:
--
--    This script contains a drop in replacement for the APEX functions
--    V and NV which are used to read the value of an APEX item.
--
--    Why do I need a replacement for them? Checkout my blog posting at
--    http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
--    and the OTN forum discussion at
--    http://forums.oracle.com/forums/thread.jspa?threadID=445423&messageID=1576799
--
--    This script also contains a DV function (for date) to complete the existing
--    V (for string) and NV functions (for number).
--
--  NOTE:
--
--    The package has to be loaded into the schema where your application tables
--    are located. That should be the same schema as it is specified by
--    "First Schema Provisioned" at "Manage Workspaces\Workspace Details".
--    => search for "First Schema Provisioned" in the online help.
--
--    If you use a other version than APEX 2.2.x then you have to replace the
--    schema FLOWS_020200 with the schema your version is using (eg FLOWS_020000)
--
--  SUPPORTED APEX VERSIONS:
--
--    2.x
--
--  AUTHORS:
--
--    PW: Patrick Wolf (http://inside-apex.blogspot.com/)
--
--  SVN HEADER:
--
--    $Id: ApexLib_V_functions_patch.sql 140 2007-02-08 19:26:56Z patrick_wolf $
--
--******************************************************************************
--
CREATE OR REPLACE FUNCTION V
  ( p_item   IN VARCHAR2
  , p_flow   IN NUMBER   := NULL
  , p_scope  IN VARCHAR2 := 'SESSION_AND_USER'
  , p_escape IN VARCHAR2 := 'N'
  )
  RETURN VARCHAR2 DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX V function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
    RETURN FLOWS_020200.V
             ( p_item   => p_item
             , p_flow   => p_flow
             , p_scope  => p_scope
             , p_escape => p_escape
             );
END V;
/
--
CREATE OR REPLACE FUNCTION NV
  ( p_item   IN VARCHAR2
  )
  RETURN NUMBER DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX NV function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
    RETURN TO_NUMBER(FLOWS_020200.V(p_item));
END NV;
/
--
CREATE OR REPLACE FUNCTION DV
  ( p_item        IN VARCHAR2
  , p_format_mask IN VARCHAR2 := NULL
  )
  RETURN DATE DETERMINISTIC
--==============================================================================
-- Wraps the existing APEX V function and adds the DETERMINISTIC optimizer hint
-- so that the function isn't called for each row the query engine is verifying.
-- See http://inside-apex.blogspot.com/2006/11/caution-when-using-plsql-functions-in.html
-- for details.
--==============================================================================
IS
BEGIN
    IF p_format_mask IS NOT NULL
    THEN
        RETURN TO_DATE(FLOWS_020200.V(p_item), p_format_mask);
    ELSE
        RETURN TO_DATE(FLOWS_020200.V(p_item));
    END IF;
END DV;
/

« ... Read full posting ... »

Caution when using PL/SQL functions in a SQL statement

Be cautious if you use PL/SQL functions/packages in your SQL statements, especially in the Where-Clause!

Why?

Because the function is probably called more often than you think it is! If you don't want to read the hole article, scroll down and check the APEX impact.


An example

Let's create the following simple function.

CREATE OR REPLACE FUNCTION getValue
( pValue IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  DBMS_Output.put_line
    ( 'called getValue with '||pValue||' at '||
      TO_CHAR(SYSDATE, 'HH24:MI:SS')
    );
  RETURN pValue;
END getValue;

And now look at the following SQL Statement where we call our new function with a constant value.

SELECT COUNT(*)
 FROM EMPLOYEES
WHERE SALARY = getValue(1)
;

How often do you think is the function called?

• One time because it always uses the same value as parameter?
• For each row which is checked by the query engine?
• Some number between one time and for all rows?

Check the DBMS_Output.

If you have bet your money on For each row which is checked by the query engine, then you are the winner! It's called 107 times! As often as we have rows in the table.

If we change the query to

SELECT COUNT(*)
 FROM EMPLOYEES
WHERE EMPLOYEE_ID > getValue(1)
;

How often to you think it is called? You have the same choice as before.

Check the DBMS_Output.

It's just called one time! What is the difference? In our second example the optimizer is using the EMP_EMP_ID_PK index and in that case as Tom Kyte explained me

The optimizer gets the start/stop range (the function might be called twice actually) and then it range scans with the constants.

So what's the deal all about it?

Keep in mind the performance issue when you have a function which is selecting against other tables and you use such a function in a where-clause, where 1.000's of records are processed!

Possible solutions?

You can change the query in the following way. The optimizer then knows that is ok when he executes the query just one time. So called scalar subquery caching, which works with all DB versions. Checkout AskTom about that term.

SELECT COUNT(*)
 FROM EMPLOYEES
WHERE SALARY = (SELECT getValue(1) FROM DUAL)
;

Or you take a look at the DETERMINISTIC clause which can specified when creating a function or a package functions. What does it do? It's a hint for the optimizer to indicate that the function returns the same result value whenever it is called with the same values for its arguments. But, as noted by Tom Kyte

10gr2 supports deterministic as an optimization in SQL for the first time. Prior to 10gr2 - deterministic was all about function based indexes only - the SQL engine ignored it.

So only if you have a 10.2.x database, using DETERMINISTIC will give you a speed up of your query. Use the above solution instead.

But let's change our function and see what we get now.

CREATE OR REPLACE FUNCTION getValue
( pValue IN NUMBER
)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
 DBMS_Output.put_line
   ( 'called getValue with '||pValue||' at '||
     TO_CHAR(SYSDATE, 'HH24:MI:SS')
   );
 RETURN pValue;
END getValue;

Run the first query again and you will see that the function is called just once!
Try the following SQL statement.

SELECT COUNT(*)
FROM EMPLOYEES
WHERE SALARY = getValue(SALARY)
;

How often do you think it is called? It's just called as often as there are different salaries in the table. With the old version of the function is called for each row again.

I know that the Oracle documentation about DETERMINISTIC says

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function.

Which I think is basically to see in relation to the function based index for which this "hint" has been introduced. From my observation, if you have such a function call in several SQL statements which are called after each other (eg in a anonymous pl/sql block), the function is executed again for each SQL statement. It's just a hint for the optimizer during execution of the current statement.

DON'T USE DETERMINISTIC if your function is changing some global variables (eg increase a counter) or doing DML and you expect that the function is called for each row!

And how is that all related to APEX?

Are you using the V or NV function in your SQL statements? In APEX 2.0 the V function isn't wrapped (in 2.2 it is) and I don't see that the DETERMINISTIC is used in the code... It's just memory processing they do in there, but if you call it for 1.000's of records...

Always use bind variables instead! Only use the V function in your packages, but never use them in your report SQL statements.

I have done a posting on OTN about speeding up the V function. Stay tuned.

UPDATE: See my posting Drop in replacement for V and NV function which contains a wrapper for the existing functions.

« ... Read full posting ... »