Re: slow data delivery rate via Pro*C ?

In <2018_at_keele.keele.ac.uk>, csa09_at_seq1.keele.ac.uk (P. Singleton):
> I use Oracle V6 on a Sun 4 network to serve a prototype CASE system via
> the Pro*C interface.
 ...
> The root of the problem is the low bandwidth from Oracle to our applications.
> Once the SELECT query has been evaluated (no complaints about the speed of
> this), the resulting tuples are delivered at a disappointingly leisurely
> rate.
....

The key to PRO*C speed seems to be use of array fetches instead of single row fetches (SQL*Plus selects in batches of 20 by default). Last Spring I did comparisons of single row fetches vs. array fetches (summarized below).

Also, as I understand it, PRO*C suffers from excessive interrupt activity and this is being addressed in the next major release of the product. So I also did a comparison between single row fetch vs. single row PL/SQL fetch (PL/SQL doesn't do arrays yet). PL/SQL is supposed to reduce network interrupt activity by buffering up multiple messages and sending them in a single packet across the network.

Finally, for good measure, I did a comparison between dynamic vs. non-dynamic queries (dynamic queries have to be reparsed).

I traced tests with "trace -c" and found that all trace parameters were identical except for write, read, and brk (summarized below). I'm not a systems guru so my apologies in advance if this methodology is brain dead.

                   Single Row Fetch                    Array Fetch
                    (176 fetches)            (11 fetches totaling 176 rows)
=====     ================================   ==============================
Trace     
Param     Dynamic    Non-Dynamic   PL/SQL       Dynamic     Non-Dynamic
-----     --------------------------------   ------------------------------
write        1422       898          545          278            259
read         1247       723          370           94             75
brk           123       123            5           10             10

Array fetches win hands down.

I should mention that I did this test before our SUN's were switched to yellow pages. So I should really re-do the complete test. I noticed some immediate differences in brk activity.

Does anybody have better strategies for tracing performance at the system level?

+-----------------------------------------------------------------------+
| Jean Anderson, DBA & SA                  email:  jean_at_seismo.css.gov  |
| SAIC Geophysics Division, MS 12             or:  jean_at_esosun.css.gov  |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
 +-----------------------------------------------------------------------+
|   all disclaimers apply....                                           |
+-----------------------------------------------------------------------+