Re: PL/SQL Wrapper Costs - Lessons Learned

Nice summary, and thanks for doing it

On 6/21/07, Peter Sylvester <peters_at_mitre.org> wrote:
>
>
> 2) Using PL/SQL to wrap simple SQL statements that return (1) row of
> data to a 3GL program is a *bad idea*. It takes roughly double the
> elapsed time of having the 3GL program do the SQL directly. I ran a
> number of tests, used different parameter passing strategies, even
> eliminated all parameters and logic, but could not remove the PL/SQL
> overhead. The SQL*Net messaging overhead is about the same, however for
> SQL versus wrapped SQL (and its fairly high).
>
> If you have (2) or more SQL statements to run, the PL/SQL overhead might
> start to look more tolerable.

This approach is also likely to be helpful where you have, or are likely to have, multiple applications accessing the data, for obvious code re-use reasons and for consistency. I've genuinely seen complaints from people that there was something wrong with the database for CRM because returning a customer account record took about 15s ( a long time when some is stood in front of you) but that when you used the casework program to view the same data it took about 5s. (we don't allow front line staff casework access). The reason of course was nothing to do with the database and everything to do with the different sql. Of course my reason would be why write it twice:)

Niall

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l