Re: PL/SQL Wrapper Costs - Lessons Learned

  • From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
  • To: peters@xxxxxxxxx
  • Date: Wed, 27 Jun 2007 06:53:00 +0100

Nice summary, and thanks for doing it

On 6/21/07, Peter Sylvester <peters@xxxxxxxxx> 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

Other related posts: