Help with Parsing and TKPROF output

[Biddell, Ian]

Hi All,   I have an online program that is timing out and when I trace it I get the following at the bottom of my tkprof output. It’s easy to see why the transaction is running slow, because of all the parsing. But the program and all called modules are compiled with max_opencursors = 75 to stop the parsing problem, though it doesn’t seem to be helping here.   There are only 64 unique sql statements that all use host variables, so why does it also say there 786 sql statements in the session, what could be causing the 64 to turn into 786 and be getting reparsed all the time  :-((((((   Any help on this would be greatly appreciated as the transaction dies after awhile and it’s in production  doh!   Thanks Ian     ********************************************************************************   OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse      667      1.50      22.38          0          0          0           0 Execute   5071      0.30       0.32          0        460         31        5514 Fetch     7439      1.17       6.60          1      66144          4        7257 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total    13177      2.97      29.30          1      66604         35       12771   Misses in library cache during parse: 0     OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS   call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse      118      0.19       3.89          0          0          0           0 Execute    533      0.32       4.56          0          0          0         532 Fetch      267      0.00       0.02          0        271        532         267 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total      918      0.51       8.47          0        271        532         799   Misses in library cache during parse: 0     784  user  SQL statements in session.     2  internal SQL statements in session.   786  SQL statements in session.    64  statements EXPLAINed in this session. ******************************************************************************** Trace file: ora00503.trc Trace file compatibility: 7.03.02 Sort options: default          1  session in tracefile.      784  user  SQL statements in trace file.        2  internal SQL statements in trace file.      786  SQL statements in trace file.       68  unique SQL statements in trace file.       64  SQL statements EXPLAINed using schema:            CSISDBA.prof$plan_table              Default table was used.              Table was created.              Table was dropped.    19969  lines in trace file.