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

Are you sure that the Execution Plan is the same in both the cases ? You might be adding more clauses which might be changing the execution plan. Try enabling trace in both the cases and comparing the rows fetched at each step and the execution plans.

Virag

Virag Saksena                                         vsaksena_at_us.oracle.com
Senior Consultant                                     415.506.5087

In article <4ci0dg$ogj_at_nntp.Stanford.EDU> patrick_at_casbs.Stanford.EDU (Patrick Goebel) writes:
>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.
>------------------------------------------------------------------->
Received on Mon Jan 15 1996 - 00:00:00 CET