Performance of PL/SQL Functions using SQL vs Pure Code
I received a comment the other day from a user of my PL/SQL packages about how he had extracted the GEOM.isCompound() function from the GEOM package, and re-written it in pure PL/SQL getting rid of the SQL pivot that that I use.
That’s the sort of behaviour I encourage.
The user’s comment was this:
This [i.e., his rewrite without the SQL] runs about 20-30 times faster and answers the same question.
And this is about what I would have expected!
But this comment reminded me about that article I have been meaning to write (for the past few years) on how to examine the performance differences between two implementations of a function in PL/SQL. I used the technique in my article on a fast method for creating multi-point geometries but I never got around to the sort of article that follows. At the end of the article I will discuss the SQL vs pure programmatic approach: there is no right answer, just some observations from someone who has written the odd PL/SQL function over the years.
The original GEOM.isCompound() function was extracted for the purpose of this article. With a slight rename, here it is:
create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
return boolean
Is
v_compound_element_count number := 0;
Begin
SELECT count(*) as c_element_count
INTO v_compound_element_count
FROM (SELECT e.id,
e.etype,
e.offset,
e.interpretation
FROM (SELECT trunc((rownum - 1) / 3,0) as id,
sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
FROM TABLE(p_elem_info) sei
GROUP BY trunc((rownum - 1) / 3,0)
) e
) i
WHERE ( i.etype = 2 AND i.interpretation = 2 )
OR ( i.etype in (1003,2003) AND i.interpretation IN (2,4) );
Return v_compound_element_count > 0;
End is_Compound;
The commentator’s function is as follows:
create or replace
function has_compound_curves ( geom_in in sdo_geometry)
return integer
as
etype number;
interpret number;
begin
for i in geom_in.sdo_elem_info.first .. geom_in.sdo_elem_info.last loop
case
when mod(i,3) = 1 then null; -- continue;
when mod(i,3) = 2 then etype := geom_in.sdo_elem_info(i);
when mod(i,3) = 0
then
interpret := geom_in.sdo_elem_info(i);
if((etype = 2 and interpret = 2) or (etype in (1003,2003) and interpret in (2,4))) then
return 1;
end if;
end case;
end loop;
return 0;
end has_compound_curves;
Now, let’s compare the two using Tom Kyte’s runstats_pkg package.
set serveroutput on size unlimited declare v_OK integer; v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,17,4,2,17,2,1,23,2,2),MDSYS.SDO_ORDINATE_ARRAY(50,15,55,15,60,15,65,15,10,25,20,30,25,25,30,30,110,178,110,175,120,175,120,178,115,180,110,178)); begin runstats_pkg.rs_start; v_OK := has_compound_curves(v_geom); runstats_pkg.rs_middle; v_OK := case when is_compound(v_geom.sdo_elem_info) = TRUE then 1 else 0 end; runstats_pkg.rs_stop; end; / anonymous block completed Run1 ran in 0 hsecs Run2 ran in 1 hsecs Run 1 ran in 0 % of the time ...
There appears to be little difference. So, I’ve short-circuited any more testing as a single execution of each function occurs in less than 1/100th of a second and so cannot be reasonably compared. The only way to compare the algorithms is to write a procedure that executes each 1000 times as follows:
create or replace
procedure test_compound(p_geom in mdsys.sdo_geometry,
p_is in integer)
As
v_OK integer;
v_is_compound boolean := case when p_is > 0 then true else false end;
Begin
For i IN 1..1000 LOOP
If ( v_is_compound ) Then
v_OK := case when is_compound(p_geom.sdo_elem_info) then 1 else 0 end;
Else
v_OK := has_compound_curves(p_geom);
End If;
End Loop;
end test_compound;
Which we would execute using the runstats_pkg as follows:
set serveroutput on size unlimited;
declare
v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,17,4,2,17,2,1,23,2,2),MDSYS.SDO_ORDINATE_ARRAY(50,15,55,15,60,15,65,15,10,25,20,30,25,25,30,30,110,178,110,175,120,175,120,178,115,180,110,178));
begin
runstats_pkg.rs_start;
test_compound(v_geom,0);
runstats_pkg.rs_middle;
test_compound(v_geom,1);
runstats_pkg.rs_stop(10);
end;
anonymous block completed
Run1 ran in 1 hsecs
Run2 ran in 91 hsecs
Run 1 ran in 1.1 % of the time
Name Run1 Run2 Diff
LATCH.cache buffers chains 121 105 -16
STAT...undo change vector size 2,072 2,140 68
STAT...recursive cpu usage 0 91 91
STAT...CPU used by this sessio 1 93 92
STAT...redo size 2,720 2,860 140
STAT...execute count 1 1,001 1,000
STAT...calls to get snapshot s 1 1,001 1,000
STAT...recursive calls 1 1,003 1,002
LATCH.shared pool 0 1,028 1,028
LATCH.SQL memory manager worka 6 2,006 2,000
LATCH.library cache lock 0 2,003 2,003
LATCH.library cache pin 4 2,007 2,003
LATCH.library cache 5 3,017 3,012
LATCH.row cache objects 12 15,024 15,012
STAT...session pga memory 65,536 0 -65,536
Run1 latches total versus runs -- difference AND pct
Run1 Run2 Diff Pct
158 25,210 25,052 1%
This tells me that the pure PL/SQL implementation runs in 1.1% of the time of the original implementation based on SQL. That is a huge difference between the two which is expected and appears to be in agreement with my commentator.
Can we modify is_Compound and close the performance gap?
Let’s modify is_Compound by:
- making it return an integer and
- removing one of the SELECT in-line views that is not needed.
create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
return integer
Is
v_compound_element_count number := 0;
Begin
SELECT count(*) as c_element_count
INTO v_compound_element_count
FROM (SELECT trunc((rownum - 1) / 3,0) as id,
sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
FROM TABLE(p_elem_info) sei
GROUP BY trunc((rownum - 1) / 3,0)
) e
WHERE ( e.etype = 2 AND e.interpretation = 2 )
OR ( e.etype in (1003,2003) AND e.interpretation IN (2,4) );
Return v_compound_element_count;
End is_Compound;
/
And then run it again against has_compound_curves() function. Note that first we need to modify the test_compound procedure to handle the changed return type by removing the “case when is_compound….end” construct and replacing it by a simple call to is_compound.
The new results are:
anonymous block completed
Run1 ran in 3 hsecs
Run2 ran in 93 hsecs
Run 1 ran in 3.23 % of the time
Name Run1 Run2 Diff
LATCH.channel operations paren 0 14 14
LATCH.checkpoint queue latch 0 16 16
LATCH.cache buffers chains 121 96 -25
STAT...undo change vector size 2,072 2,140 68
STAT...CPU used by this sessio 5 94 89
STAT...recursive cpu usage 2 91 89
STAT...redo size 2,720 2,860 140
STAT...calls to get snapshot s 1 1,001 1,000
STAT...execute count 1 1,001 1,000
STAT...recursive calls 1 1,003 1,002
LATCH.shared pool 0 1,028 1,028
LATCH.library cache pin 4 2,007 2,003
LATCH.library cache lock 0 2,003 2,003
LATCH.SQL memory manager worka 6 2,073 2,067
LATCH.library cache 5 3,018 3,013
LATCH.row cache objects 12 15,027 15,015
Run1 latches total versus runs -- difference AND pct
Run1 Run2 Diff Pct
160 25,310 25,150 1%
A very, very slight improvement.
Let’s try modifying the function by adding in an “and rownum < 2” predicate to “short circuit” the query once we have found a compound element. Also, let’s add in a test to short-circuit when we find a compound element (4,1005,2005) and not just a potential sub-element of one.
create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
return integer
Is
v_compound_element_count number := 0;
Begin
SELECT count(*) as c_element_count
INTO v_compound_element_count
FROM (SELECT trunc((rownum - 1) / 3,0) as id,
sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
FROM TABLE(p_elem_info) sei
GROUP BY trunc((rownum - 1) / 3,0)
) e
WHERE ( e.etype = 2 AND e.interpretation = 2 )
OR ( e.etype in (1003,2003) AND e.interpretation IN (2,4) )
AND rownum < 2;
Return v_compound_element_count;
End is_Compound;
Now, one last final run against the new has_compound_curves PL/SQL function….
anonymous block completed
Run1 ran in 3 hsecs
Run2 ran in 90 hsecs
Run 1 ran in 3.33 % of the time
Name Run1 Run2 Diff
LATCH.cache buffers chains 128 112 -16
STAT...undo change vector size 2,140 2,212 72
STAT...CPU used by this sessio 5 90 85
STAT...recursive cpu usage 2 90 88
STAT...redo size 2,860 3,004 144
STAT...calls to get snapshot s 1 1,001 1,000
STAT...execute count 1 1,001 1,000
STAT...recursive calls 1 1,003 1,002
LATCH.shared pool 0 1,029 1,029
LATCH.SQL memory manager worka 6 2,006 2,000
LATCH.library cache lock 1 2,003 2,002
LATCH.library cache pin 4 2,007 2,003
LATCH.library cache 5 3,017 3,012
LATCH.row cache objects 12 15,024 15,012
Run1 latches total versus runs -- difference AND pct
Run1 Run2 Diff Pct
180 25,246 25,066 1%
Again, not much change.
Let’s try reordering the geometry so the compound element is first…
select sdo_aggr_union(sdoaggrtype(geom,0.05)) as geo m from ( select rin,geom from ( select rownum as rin,e.geometry as geom from table(geom.extractelementspiped(MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1,5,2,1,9,2,1,17,4,2,17,2,1,23,2,2),MDSYS.SDO_ORDINATE_ARRAY(50,15,55,15,60,15,65,15,10,25,20,30,25,25,30,30,110,178,110,175,120,175,120,178,115,180,110,178)),0) ) e ) order by rin desc ); GEOM ----------------------------------------- MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,2,5,2,1,13,2,1,17,2,1,21,2,1),MDSYS.SDO_ORDINATE_ARRAY(120,178,115,180,110,178,110,175,120,175,120,178,60,15,65,15,50,15,55,15,10,25,20,30,25,25,30,30)) 1 row selected
And run the new geometry against the new has_compound_curves PL/SQL function….
set serveroutput on size unlimited;
declare
v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,2,5,2,1,13,2,1,17,2,1,21,2,1),MDSYS.SDO_ORDINATE_ARRAY(120,178,115,180,110,178,110,175,120,175,120,178,60,15,65,15,50,15,55,15,10,25,20,30,25,25,30,30));
begin
runstats_pkg.rs_start;
test_compound(v_geom,0);
runstats_pkg.rs_middle;
test_compound(v_geom,1);
runstats_pkg.rs_stop(10);
end;
/
anonymous block completed
Run1 ran in 2 hsecs
Run2 ran in 92 hsecs
Run 1 ran in 2.17 % of the time
Name Run1 Run2 Diff
STAT...session logical reads 26 38 12
LATCH.cache buffers chains 137 105 -32
STAT...recursive cpu usage 0 91 91
STAT...CPU used by this sessio 2 93 91
STAT...undo change vector size 2,076 2,204 128
STAT...redo size 2,724 2,996 272
STAT...recursive calls 1 1,001 1,000
LATCH.shared pool 0 1,000 1,000
STAT...calls to get snapshot s 1 1,001 1,000
STAT...execute count 1 1,001 1,000
LATCH.library cache pin 4 2,003 1,999
LATCH.library cache lock 0 2,000 2,000
LATCH.SQL memory manager worka 6 2,006 2,000
LATCH.library cache 4 3,003 2,999
LATCH.row cache objects 12 15,012 15,000
Run1 latches total versus runs -- difference AND pct
Run1 Run2 Diff Pct
190 25,152 24,962 1%
Reorganising the SDO_GEOMETRY had little effect on the SQL algorithm.
Even though I always suspected the issue was and is the overhead of setting up multiple SQL calls, I still want to complete the last of the modifications to the SQL function.
create or replace
Function is_Compound(p_elem_info in mdsys.sdo_elem_info_array)
return integer
Is
v_compound_element_count integer := 0;
Begin
SELECT 1 as c_element_count
INTO v_compound_element_count
FROM (SELECT trunc((rownum - 1) / 3,0) as id,
sum(case when mod(rownum,3) = 1 then sei.column_value else null end) as offset,
sum(case when mod(rownum,3) = 2 then sei.column_value else null end) as etype,
sum(case when mod(rownum,3) = 0 then sei.column_value else null end) as interpretation
FROM TABLE(p_elem_info) sei
GROUP BY trunc((rownum - 1) / 3,0)
) e
WHERE ( ( e.etype = 2 AND e.interpretation = 2 /* circular arc*/ )
OR ( e.etype in (4,1005,2005) )
OR ( e.etype in (1003,2003) AND e.interpretation IN (2/*circular arc*/,4/*circle*/) )
)
AND rownum < 2;
Return 1;
Exception
When NO_DATA_FOUND Then
Return 0;
End is_Compound;
Since I have decided to handle etype 4,1005 and 2005 elements, I should change the has_compound_curves() function. Since I am changing it I will change the function to have it accept and mdsys.sdo_elem_info_array as does the is_compound
() function.
create or replace
function has_compound_curves ( elem_in in mdsys.sdo_elem_info_array)
return integer
as
etype number;
interpret number;
begin
for i in elem_in.first .. elem_in.last loop
case
when mod(i,3) = 1 then null; -- continue;
when mod(i,3) = 2 then etype := elem_in(i);
when mod(i,3) = 0
then
interpret := elem_in(i);
if( (etype = 2 and interpret = 2)
or
( etype in (4,1005,2005) )
or
(etype in (1003,2003) and interpret in (2,4))
) then
return 1;
end if;
end case;
end loop;
return 0;
end has_compound_curves;
create or replace
procedure test_compound(p_elem in mdsys.sdo_elem_info_array,
p_is in integer)
As
v_OK integer;
v_is_compound boolean := case when p_is > 0 then true else false end;
Begin
For i IN 1..1000 LOOP
If ( v_is_compound ) Then
v_OK := is_compound(p_elem);
Else
v_OK := has_compound_curves(p_elem);
End If;
End Loop;
end test_compound;
Testing reveals:
set serveroutput on size unlimited;
declare
v_geom mdsys.sdo_geometry := MDSYS.SDO_GEOMETRY(2006,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,4,2,1,2,2,5,2,1,13,2,1,17,2,1,21,2,1),MDSYS.SDO_ORDINATE_ARRAY(120,178,115,180,110,178,110,175,120,175,120,178,60,15,65,15,50,15,55,15,10,25,20,30,25,25,30,30));
begin
runstats_pkg.rs_start;
test_compound(v_geom.sdo_elem_info,0);
runstats_pkg.rs_middle;
test_compound(v_geom.sdo_elem_info,1);
runstats_pkg.rs_stop(10);
end;
/
anonymous block completed
Run1 ran in 0 hsecs
Run2 ran in 90 hsecs
Run 1 ran in 0 % of the time
Name Run1 Run2 Diff
LATCH.cache buffers chains 112 126 14
LATCH.channel operations paren 0 14 14
LATCH.checkpoint queue latch 0 16 16
STAT...undo change vector size 2,076 2,144 68
STAT...recursive cpu usage 2 92 90
STAT...CPU used by this sessio 2 92 90
STAT...redo size 2,724 2,924 200
STAT...calls to get snapshot s 1 1,001 1,000
STAT...execute count 1 1,001 1,000
STAT...recursive calls 1 1,003 1,002
LATCH.shared pool 0 1,029 1,029
LATCH.library cache lock 8 2,003 1,995
LATCH.library cache pin 11 2,007 1,996
LATCH.SQL memory manager worka 6 2,073 2,067
LATCH.library cache 20 3,018 2,998
LATCH.row cache objects 12 15,024 15,012
Run1 latches total versus runs -- difference AND pct
Run1 Run2 Diff Pct
178 25,343 25,165 1%
Conclusion
It does not appear to matter how much effort we put in to making the SQL version of is_compound() more efficient, the non-SQL version will beat us every time. The performance difference is enormous with the gap, after having been close a small amount, immediately widened by removing of the need to “dereference” the sdo_elem_info_array (this reminds me of the performance difference I extracted from my VB6 library when I removed as much “dereferencing” of OraObjects in runtime).
Discussion
First off, even when you use SQL in a function, it doesn’t hurt to do some
profiling and improvement of its performance. In this case what I thought
was a pretty simple, fast piece of SQL, turned out to be capable of being
improved.
Secondly, why do I use SQL when I could use pure programmatic PL/SQL? I started out using programmatic SQL (as Pro Oracle Spatial uses) but I generally found:
- The code harder to write when one tries to handle 2, 3 and 4D data in the algorithm;
- The time to complete a standard compile-debug-test cycle longer and more complex than using pure SQL;
- The code required more documentation.
The thing I liked about using SQL is that it is:
- Easier and faster executing a single loop of the standard compile-debug-test cycle;
- Declarative and capable of being given to others for executing using a variety of tools (SQLPlus, SQL Developer, TOAD etc);
- Self-documenting because of its declarative nature;
- Finally, I have always assumed that SQL execution is the Sine qua non (without which there is nothing) of being an Oracle database!
However easier an algorithm is to write in SQL (and not all are), when one has it running correctly (the goal of all programming), if the algorithm is, as is said in project management, “on the critical path”, then it has to be converted to straight PL/SQL (as my commentator has done).
Of course I always meant to test that my SQL approach is NOT orders of magnitude slower than non-SQL approaches. Sadly, my simple testing above shows that that is not the case (well, at least in this case). Obviously the efficiency of one approach versus another depends on how it is used and the sensitivity of the process to the processing time of the function. But since a user has discovered this then, perhaps, now is the time to swap out the SQL algorithm for a much faster PL/SQL based on i.e., his!
Thanks very much to him for bringing my attention to the speed issues of the original isCompound().
I hope others find this article useful.