Wednesday, 12 April 2017

The Problem with SQL Calling PL/SQL Calling SQL

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.

Oracle Database PL/SQL, SQL, Oracle Database Certifications

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));