Buffer size, cursors, paging and Pro*C performance?

RDBMS V 6.033, Pro*C 1.5

I hope I can describe this in a way that makes sense: I have a Pro*C program that builds a select statement as a VARCHAR string, then prepares it, opens it as a cursor, then fetches from it. It all works fine. However, whenever the select string reaches a certain length (or complexity, I'm not sure which), the execution time bogs way down--from 1.5 seconds to 13 seconds! Whenever the program runs slow, I can hear disk activity (as if something is being paged) whereas when it runs fast, I do not hear any disk activity.

Here is the some of the relevant code:

• SQL Text -----------------------

VARCHAR sql_text[800];
VARCHAR interval[128];
char order_by[2];

/* The following is all one line */

      strcpy(sql_text.arr, "SELECT INITCAP(name_last), sum(cold_starts_saved) / count(unique(log_date)), sum(distance_saved) * :distconv / count(unique(log_date)), sum(:SN * cold_starts_saved + :SD * distance_saved + :SW * weather_bonuses) / count(unique(log_date)), sum((:GN * cold_starts_saved + :GD * distance_saved) / 1000) * :massconv / count(unique(log_date)), :F * sum(distance_saved) * :voluconv / count(unique(log_date)), :F * sum(distance_saved) * 2.5 / 42 / count(unique(log_date)), count(unique(log_date)), INITCAP(name_first), cac_profile.id, INITCAP(cac_profile.city) FROM cac_profile, cac_log WHERE cac_profile.id = cac_log.id AND log_date BETWEEN ");

strcat(sql_text.arr, interval.arr);

/* The following is all one line */

strcat(sql_text.arr, " GROUP BY name_last, name_first, cac_profile.id, cac_profile.city ORDER BY ");

strcat(sql_text.arr, order_by);

Where

interval.arr = "TO_DATE('OCT-1995','MON-YYYY') AND \

                TO_DATE('DEC-1995','MON-YYYY')"

and order_by = "1"

As listed above, the compiled program runs slow (13 seconds). If I replace a few of the count(unique(log_date))'s with simple numbers (e.g. 10) in the denominators of the select statement, execution speeds up to 1.5 seconds.

There seems to be a threshold where the complexity of the select statement causes paging which in turn slows execution. Are there init.ora parameters I can tweak that can prevent this slowing down? I have plenty of RAM (96 Mb) and I am only retrieving about 300 records with a handful of columns per record.

Hope this is clear. Thanks for any suggestions!

Cheers,
------------------------------------------------------------------->

 Patrick Goebel           | voice: (415) 321-2052     ------  __o
 CASBS/GCN/VeloNet        |   fax: (415) 321-1192  -------  _`\<,_
 202 Junipero Serra Blvd. |                           ---- (*)/ (*)
 Stanford, CA 94305       | High Speed Digital Commuter
 http://cycling.org       | The road to the future is CLAIRE.


------------------------------------------------------------------->