Skip to Main Content
  • Questions
  • Difference in performance SQL vs PL/SQL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Patrick.

Asked: January 19, 2016 - 11:45 am UTC

Last updated: January 19, 2016 - 3:59 pm UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

I kinda always followed this mantra:
"If you can do it in SQL, use SQL. Otherwise try PL/SQL and if that doesn't work ask for help..."
I have created a query that does exactly what I want but it takes a lot of time. No problem, I thought, since it is something that will only be run occasionally. I always thought (was taught?) that pure SQL was faster than PL/SQL. But, curious as I am, I decided to run the same update in a block of PL/SQL code. Using BULK COLLECT and FORALL to minimize the context switches and LIMIT to manage my memory usage.
And in my environment this PL/SQL code ran more than twice as fast as the SQL solution.
I checked the results and the end result is exactly the same, so that is no explanation for the performance gain.
I have created a simple script to show what's going on. This is a very simplified version of my production environment, but it shows the difference. The difference is not as great as in my environment, but you'll get the idea:
CLEAR SCREEN
SET SERVEROUTPUT ON SIZE UNLIMITED

set timing on
set echo off
prompt -= drop the tables if they exist
declare
  table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist, -00942);
  object_does_not_exist exception;
  pragma exception_init(object_does_not_exist, -04043);
begin
  execute immediate 'drop table foo purge';
exception
  when table_or_view_does_not_exist
    or object_does_not_exist then null; -- on purpose, hide any error
end;
/
declare
  table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist, -00942);
  object_does_not_exist exception;
  pragma exception_init(object_does_not_exist, -04043);
begin
  execute immediate 'drop table bar purge';
exception
  when table_or_view_does_not_exist
    or object_does_not_exist then null; -- on purpose, hide any error
end;
/
prompt -= create a table with 50000 rows
create table foo as
select level code
     , dbms_random.string('U', trunc(dbms_random.value(1, 6))) value
  from dual
connect by level < 50001
/
prompt -= create a table with the same values, just uppercased
create table bar as
select code code
     , upper(value) value
  from foo
/
commit
/
prompt -= Perform an update with plain SQL.
update foo m
   set m.value = (select mu.value
                    from bar mu
                   where m.code = mu.code)
/
rollback
/
prompt -= Perform the same update using PL/SQL     
declare
  c_bulklimit pls_integer := 2500;
  cursor thenewvalues is
    select mu.code
         , mu.value value
      from bar mu;
  type thenewvalues_tt is table of thenewvalues%rowtype index by pls_integer;
  l_thenewvalues thenewvalues_tt;
begin
  open thenewvalues;
  loop
    fetch thenewvalues bulk collect
      into l_thenewvalues limit c_bulklimit;
    if l_thenewvalues.count > 0 then
      forall indx in l_thenewvalues.first .. l_thenewvalues.last
        update foo m
           set m.value = l_thenewvalues(indx).value
         where m.code = l_thenewvalues(indx).code;
    end if;
    exit when l_thenewvalues.count < c_bulklimit;
  end loop;
end;
/
rollback
/
prompt -= cleanup
declare
  table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist, -00942);
  object_does_not_exist exception;
  pragma exception_init(object_does_not_exist, -04043);
begin
  execute immediate 'drop table foo purge';
exception
  when table_or_view_does_not_exist
    or object_does_not_exist then null; -- on purpose, hide any error
end;
/
declare
  table_or_view_does_not_exist exception;
  pragma exception_init(table_or_view_does_not_exist, -00942);
  object_does_not_exist exception;
  pragma exception_init(object_does_not_exist, -04043);
begin
  execute immediate 'drop table bar purge';
exception
  when table_or_view_does_not_exist
    or object_does_not_exist then null; -- on purpose, hide any error
end;
/

set timing off


The output is like this (on my environment):
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as...
-= drop the tables if they exist
PL/SQL procedure successfully completed
Executed in 0.01 seconds
PL/SQL procedure successfully completed
Executed in 0.009 seconds
-= create a table with 50000 rows
Table created
Executed in 1.813 seconds
-= create a table with the same values, just uppercased
Table created
Executed in 0.092 seconds
Commit complete
Executed in 0.007 seconds
-= Perform an update with plain SQL.
50000 rows updated
Executed in 76.034 seconds
Rollback complete
Executed in 0.082 seconds
-= Perform the same update using PL/SQL
PL/SQL procedure successfully completed
Executed in 47.091 seconds
Rollback complete
Executed in 0.466 seconds
-= cleanup
PL/SQL procedure successfully completed
Executed in 0.06 seconds
PL/SQL procedure successfully completed
Executed in 0.021 seconds


What would be an explanation for the difference in performance?

Kindest regards,
Patrick Barel

and Chris said...

There's no index on the code columns!

That means both updates have to do a full table scan of bar to fetch its values. But the PL/SQL approach only does this once (the select).

The SQL is effectively:

For every row in foo:
Execute "select mu.value from bar mu where mu.code = :v"

So the SQL approach full scans bar once for every row in foo! No wonder it takes longer...

You can see this in the Execs column of SQL monitor report (trailing columns removed for readability):

==================================================================================
| Id |      Operation       | Name |  Rows   | Cost |   Time    | Start  | Execs |
|    |                      |      | (Estim) |      | Active(s) | Active |       |
==================================================================================
|  0 | UPDATE STATEMENT     |      |         |      |        85 |     +6 |     3 |
|  1 |   UPDATE             | FOO  |         |      |        85 |     +6 |     3 |
|  2 |    TABLE ACCESS FULL | FOO  |   50000 |   30 |        85 |     +6 |     3 |
|  3 |    TABLE ACCESS FULL | BAR  |       1 |   30 |        90 |     +1 | 59775 |
==================================================================================


If you create an index on:

create index bari on bar (code);


The select from bar becomes a nice quick index range scan. So you should see the performance flip back in favor of SQL (taking just the relevant parts of the output):

-= Perform the same update using PL/SQL
PL/SQL procedure successfully completed.

Elapsed: 00:00:44.550
Rollback complete.

Elapsed: 00:00:00.599
-= Perform an update with plain SQL.
50,000 rows updated.

Elapsed: 00:00:00.660


A slam dunk for SQL (44s vs <1s)

If you also create the index on foo, the PL/SQL version also benefits:

create index fooi on foo (code);

Output:
-= Perform the same update using PL/SQL
PL/SQL procedure successfully completed.

Elapsed: 00:00:02.977
Rollback complete.

Elapsed: 00:00:00.710
-= Perform an update with plain SQL.
50,000 rows updated.

Elapsed: 00:00:01.977


The SQL version still comes out marginally ahead for me.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Makes perfect sense

Patrick Barel, January 19, 2016 - 3:27 pm UTC

Thanks for your quick answer, Chris. SQL has indeed a sub-optimal execution path and and index could help but in on my real data adding this is not an option. It now makes perfect sense to me and if my queries run too slow I will help Oracle by building a PL/SQL solution.

MERGE

Kim Berg Hansen, January 19, 2016 - 3:36 pm UTC

A SQL way of doing it "in bulk" would be to use MERGE:

merge into foo m
using bar mu
   on (m.code = mu.code)
when matched
   then update set m.value = mu.value
/


That full table scans both tables once and hash joins.

(This is quick and dirty answer. Haven't had time to test the performance, sorry, will do later ;-)
Chris Saxon
January 19, 2016 - 3:59 pm UTC

Nice solution, thanks Kim. Comes out at just over 1s for me :)

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library