Re: pro*c performance question

>>>>> "ST" == Saeed Talebbeik <saeed_at_ov.com> writes:
  ST> NNTP-Posting-Host: wylie.ov.com

  ST> I have an application that executes many select statements and manuipulates   ST> explicit cursors by doing declare, open , fetch, close.

  ST> In order to hold all the cursors (implicit and explicit) open at all times
  ST> and thus reduce the amount of parsing I have compiled my program with
  ST> options "hold_cursor=yes" and "release_cursor=no" . According to Oracle's
  ST> documentation even if I issue a "close cursor" statement the cursor will still
  ST> stay open and I will avoid reparsing of the statements thus the performance
  ST> will be increased.

  ST> After compiling my program I see absolutely no change in the amount of time that   ST> my program and the oracle engine eats !

  ST> If anyone has done any pro*c tuning before and has any insight into what the problem   ST> might be , please post your replies.

  ST>                                                                    Regards,
  ST>                                                                   <<<saeed>>>

Put this into your source-code immediately after connecting (only for v6):

exec sql alter session set sql_trace true; exec sql alter session set events '10233 trace name context forever';

The first statement opens a trace-file in USER_DUMP_DEST (init.ora) and logs all cursor activity. The second statement makes Oracle log information about when and how much memory is allocated per cursor. If you have an X-terminal and do this on unix, you can debug your program in one window, and run "tail -f tracefile" in another. I find this setup very useful.

When your program has completed, you can run tkprof on your tracefile. Find the one or two statements that use the most resources. Concentrate on tuning these: make shure you are using the correct index. If your sql contains bind-variables, tkprof can produce incorrect execution-paths if you're using a numeric host-variable against an indexed char-column, so watch out.

I suggest you try removing the "exec sql close ..." statements before re-executing, I think you will find this gets rid of any re-parsing and binding. If you see any cursors that allocate memory in multiple chunks, you should force Oracle to allocate in one chunk with "exec oracle option ( areasize = n ), where n is in KB. Put this before the first reference to the cursor. The areasize option remains in effect until changed, so if you have many cursors, you might want to put this in front of every one. The default value is 16 kb.

Precompile with select_error=no (default is yes). This causes Oracle to ignore checking that "select .. into" constructs only return 1 row (ANSI standard). If you compile with select_error=yes, Oracle will fetch twice, the second time (which takes just as long) is just for checking that there are no more rows.

Using array-fetches and array-binds is of course the number one performance booster.

hope this helps,

Magnus Lonnroth
Oracle Sweden

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_oracle.com