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).
