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
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.