PL/SQL Wrapper Costs - Lessons Learned

First I would like to thank the folks that offered suggestions on this problem, and I'd like to give back something here, in terms of what I learned. Part of this was from some excruciating trace file analysis, and some from actual benchmarking of different data access techniques
(using 10.2.0.3 under 32 bit Linux).

1. It is useful to try a variety of profiling/report tools (i.e. tkprof, Trace Analyzer, OraSRP) , as they each show slightly different views of the data. Its also useful to dive in and look at the raw trace data, something I've avoided in the past.
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.

3) Using "singleton" SQL (or procedures) to return data associated with individual records is another *really bad idea*, especially when you expect to be doing this for a lot of records. Using a client side cursor loop (ResultSet loop for Java folks) can result in 10X client side performance improvement over running many singleton select statements.
(and 20X performance over singleton PL/SQL wrapper procs). The gains
made here are primarily in the reduction of the quantity of database calls and SQL*Net traffic.

Peter Sylvester
MITRE Corp.

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