Hi Bjorn, Thanks for your reply. Based on what you have said I think I might have come across what could be the problem. We have common modules (the server side is microfocus cobol - oracle 7.3.4 on NT) that are used by the online servers and also batch programs. Normally the online programs are compiled with mode=ansi and the batch programs are compiled with mode=oracle so that cursors can be kept open across commits
So I am wondering if the calls to common modules by the online system which are compiled with mode=ansi causes the cursor to be closed and the cursor cache entry lost so the next time the common module is called it has to reparse it, even though it may still be in the same online transaction. Trouble is I don't really know what to do to fix this problem as I'm sure we had to have the mode=ansi for the online programs for some reason (it was all designed about 3 years ago so I'm having trouble remembering the reasons for some things) Do you think it would be a problem having mode=oracle for the online system ?? Thanks again Ian -----Original Message----- Sent: Wednesday, 6 March 2002 18:28 To: Multiple recipients of list ORACLE-L You indicate, that you are specifying max_opencursors (I assume you mean maxopencursors) and that your program program is written in Pro*C (or another precompiler) Do remember, that maxopencursors only influences implicit cursors in the precompiler and you must code explicit cursors correctly, so that they do not re-parse contineously. For explicit cursors, you should avoid doing CLOSE, simply using OPEN again (on an already open cursor) causes it to avoid the parse step. I would also recommend inspecting the raw trace file to see which of the parse calls is really taking that long. Thanks, Bj�rn. On Wednesday 06 March 2002 04:53, you wrote: > 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. ---------------------------------------- Content-Type: text/html; charset="us-ascii"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: ---------------------------------------- -- Bj�rn Engsig, Miracle A/S http://MiracleAS.dk -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Bj=F8rn=20Engsig?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Biddell, Ian INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
