Oracle-L: Help with Parsing and TKPROF output

From: Biddell, Ian <Ian.Biddell_at_COMPAQ.com>
Date: Tue, 05 Mar 2002 19:53:18 -0800
Message-ID: <F001.004203BF.20020305195318@fatcity.com>

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.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Biddell, Ian
  INET: Ian.Biddell_at_COMPAQ.com

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

Received on Tue Mar 05 2002 - 21:53:18 CST