X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

The Problem with SQL Calling PL/SQL Calling SQL

Chris Saxon
Developer Advocate

In the previous post we refactored a SQL query on the PL/SQL Challenge so it no longer called PL/SQL functions. This improved performance by removing context switches.

The reduction in query execution time was ten seconds. The observant among you will have noticed that the statement processed over 60,000 rows. That's a saving of less than 200 microseconds per row.

That's a tiny overhead for individual rows. At this point you may be saying:

"Big deal. I'm calling the function in a top-N query. Worst case it returns 10 rows, increasing runtime by two milliseconds. The overhead is trivial. I prefer to keep my SQL queries in PL/SQL functions because it aids code reuse".
 
Let's put performance to one side for the minute. If you have common queries, why not put them in PL/SQL and call these functions in your select statements? This makes future maintenance easier if you need to change them.
 
Because there's an important caveat with SQL that calls PL/SQL that calls SQL. You may end up with results you didn't expect.
 
Let's look at an example. Your application has a typical users table with a user in it:
create table app_users (
  user_id     integer not null primary key,
  given_name  varchar2(100) not null,
  family_name varchar2(100) not null
);
insert into app_users values (1, 'Chris', 'Saxon');

commit;

You've stored components of the name separately. To ensure you display full names consistently across the application you've built a function, get_full_name. This returns the formatted name based on user_id. The query to do this is:
  select given_name || ' ' || family_name
  from   app_users usr
  where  usr.user_id = :user_id;
So far so good. If we call this from SQL there's a problem however.
 
The following query should return the same value for name_fn and name_sql:
select get_full_name ( user_id ) name_fn, 
       given_name || ' ' || family_name name_sql
from   app_users;
When I execute it however, I see the following:
NAME_FN       NAME_SQL
------------- -----------
Harvey Saxon  Chris Saxon 
They've returned different values!! How did this happen?!
 
I haven't done anything to modify the results in the function. This is an expected consequence of calling PL/SQL functions that execute queries within a SQL statement. You can (and will) see this effect happen in your environments.
 
Why?
 
The reason is due to Oracle's read consistency model. Each SQL statement gets a read consistent view of the data at the time it starts executing. The query within the function begins at some point later than the main statement. This means that the statement inside the function can have a different view of the data than the SQL query that called it. It can see changes committed between the time the parent query started and its own inception.
 
In this case I issued the following in a separate session while the query above was executing:
update app_users
set    given_name = 'Harvey'
where  user_id = 1;

commit; 
To ensure the main query and the function returned different results I had to cheat slightly. I'm not quick enough to run the query in one session and the update in another. So I also placed a ten second sleep in get_full_name before the query. This ensured I had enough time to commit the change before the query inside the function began.
 
This is a key point. If the query that calls PL/SQL executes quickly it's difficult to reproduce the anomaly above. As a result you're unlikely to spot this kind of error in testing. This leads to bug reports that occur "randomly" in production you're not able to reproduce. The impact of these bugs can be somewhere between a mild annoyance and total catastrophe. If you're unfamiliar with the read consistency model tracking down and resolving them can be difficult.
 
Fortunately the solution is simple:
 
Copy all SQL out of PL/SQL and paste it directly into the calling statement(s).
 
Does this mean that there's no way to have reusable queries that are both correct and performant?
 
Of course not!
 
There is a better way. With views.
 
For example, you can create the following view:
create or replace view app_users_formatted as
  select user_id, given_name || ' ' || family_name full_name
  from   app_users;
Substituting this for the PL/SQL in our original query we get:
select (select full_name 
        from   app_users_formatted apuf
        where  apus.user_id = apuf.user_id
       ) name_view, 
       given_name || ' ' || family_name name_sql
from   app_users apus;

Name_sql and Name_view are now part of the same statement. This means they are read consistent to the same point in time. No matter how long the query takes to execute both will return the same value.

 
Of course, for simple formatting issues such as this, you can create a virtual column with the result you want:
alter table app_users add (full_name as (given_name || ' ' || family_name));

Conclusion

There are two things to watch for when calling PL/SQL from SQL:
  • The performance overhead of context switches
  • Potentially incorrect results if the PL/SQL itself calls SQL
You can avoid both of these by extracting the SQL query of PL/SQL and placing it directly in the calling statement. If the query you take out is one you want to reuse, save it in a view. You then reference the view instead of the PL/SQL function in your SQL.

Using views instead of PL/SQL also enables the optimizer to include the SQL in query transformations. This can lead to more efficient execution plans. The performance gains from the new plan can be even greater than the time saved on context switches.

Code Listing

-- note: requires execute privileges on dbms_lock.
create table app_users (
  user_id     integer not null primary key,
  given_name  varchar2(100) not null,
  family_name varchar2(100) not null
);
insert into app_users values (1, 'Chris', 'Saxon');
commit;  

create or replace function get_full_name ( user_id app_users.user_id%type )
  return varchar2 as
  full_name varchar2(200);
begin

  dbms_lock.sleep(10);

  select given_name || ' ' || family_name
  into   full_name
  from   app_users usr
  where  usr.user_id = get_full_name.user_id;
  
  return full_name;
  
end get_full_name; 
/ 

-- in session 1:
select get_full_name ( user_id ) name_fn, 
       given_name || ' ' || family_name name_sql
from   app_users;

-- in session 2, while query is still executing in session 1:
update app_users
set    given_name = 'Harvey'
where  user_id = 1;

commit;  

Join the discussion

Comments ( 4 )
  • Martin Rose Wednesday, September 30, 2015

    Chris, this solution is fine until your function gets more complex.

    CREATE VIEW ABC AS
    SELECT *
    FROM XYZ
    WHERE EXISTS (SELECT NULL
    FROM QRS
    WHERE QRS.XYZ_FK = XYZ.XYZ_PK
    AND QRS.STATUS = :p_status)
    /

    How are you going to implement this? You can't, because Oracle doesn't have the ability to parameterise views.

    Instead, let me offer an alternative solution...

    There's 2 immediate issues.
    i). lack of read consistency between the main SQL statement, and the 'side' one contained in the function,
    ii). context switching.

    i). We can get over the 1st point by using flashback to query at a specific point in time.

    FUNCTION get_full_name (p_user_id VARCHAR2, p_timestamp TIMESTAMP) RETURN VARCHAR2 IS
    l_full_name LONG;
    --
    BEGIN
    select given_name || ' ' || family_name
    INTO l_full_name
    from app_users usr
    where usr.user_id = (p_user_id)
    AS OF TIMESTAMP p_timestamp;
    --
    RETURN (l_full_name);
    END get_full_name;
    /

    BEGIN
    Read_Consistent_Timestamp := SYSTIMESTAMP;
    --
    select get_full_name (user_id, Read_Consistent_Timestamp) name_fn,
    given_name || ' ' || family_name name_sql
    from app_users
    AS OF TIMESTAMP Read_Consistent_Timestamp;
    END;

    Now there's no more read-inconsistency between the main & side SQL.

    ii). The context switching can be reduced/eliminated by using pipelining,

    FUNCTION Much_Less_Context_Switching (Input_cursor Input_cursor_rows, p_timestamp TIMESTAMP) RETURN Output_cursor_rows PIPELINED IS
    TYPE cursor_row_table_type IS TABLE OF Input_Cursor%ROWTYPE INDEX BY PLS_INTEGER;
    cursor_row_table cursor_row_table_type;
    --
    Output_Row Output_cursor_row;
    --
    BEGIN
    FETCH Input_cursor BULK COLLECT INTO cursor_row_table;
    --
    FOR i IN 1..Input_cursor.COUNT LOOP
    Output_Row := cursor_row_table(i);
    Output_Row.full_name := get_full_name (i.user_id, p_timestamp); <-- FUNCTION is here
    --
    PIPE ROW (Output_Row);
    END LOOP;
    --
    --
    CLOSE Input_Cursor;
    --
    RETURN;
    END Much_Less_Context_Switching;
    /

    BEGIN
    Read_Consistent_Timestamp := SYSTIMESTAMP;
    --
    SELECT *
    FROM TABLE(Much_Less_Context_Switching ((Input_cursor => CURSOR(SELECT * FROM APP_USERS AS OF TIMESTAMP Read_Consistent_Timestamp),
    p_timestamp => Read_Consistent_Timestamp));
    END;
    /

    This way, we get to keep our FUNCTION, yet have no (or vastly reduced) context switching AND everything is read-consistent.

    (Please note none of this code is meant to be runnable; it's just an example & kept simple).

  • Chris Saxon Monday, October 5, 2015

    Yes, you can implement flashback query to enable read consistent functions. If you're taking this approach then you need to use SCNs instead of timestamps.

    Internally Oracle converts the timestamps to SCNs and there can be a difference of up to three seconds in this process:

    http://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01012

  • Learner Sunday, July 24, 2016

    I've a function calling another PL/SQL function with the following content:

    Begin
    qty:=OBJ_one.quantity;
    qty:=NVL(qty,0);
    OBJ_two:=OBJ_one.another_Obj.again_another_OBJ.OBJ_two;
    OBJ_two.quantityOld:=OBJ_two.quantityOld-qty;
    OBJ_two.quantityNew:=OBJ_two.quantityNew+qty;

    update_OBJ_two(OBJ_two); ----this is another function.
    End

    Most of the time it works fine. For example, if OBJ_one.quantity is 20, then I expect it to subtract 20 from quantityOld and add 20 onto quantityNew. But sometimes, quantityOld remains the same but quantityNew increased by 20.

    Is this similar to what you have explained?
    Thanks.

  • Chris Saxon Sunday, July 24, 2016

    No. Even if there's SQL in update_OBJ_two, you're calling it from PL/SQL. So the read consistency problem isn't there.

    I'm not sure what's going on in your case!

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services