Re: Help with Parsing and TKPROF output

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: bjorn_at_MiracleAS.dk

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: ListGuru_at_fatcity.com (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).