Oracle-L: RE: Help with Parsing and TKPROF output

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Tue, 05 Mar 2002 20:43:20 -0800
Message-ID: <F001.0042041A.20020305204320@fatcity.com>

Ian,
I'll start with some easy questions:
What version of Oracle and what OS?
Can you show the SQL being run or some of it? What does v$session_event and v$session_wait show? Can you reproduce it when you run the SQL called by the program from SQLPlus?

I had a problem ion 815 / NT with really really long SQL statements (produced by Forms) that would parse forever and never complete - is your statement (or any of them) really long?

Regards,
Bruce Reardon

-----Original Message-----

Sent: Wednesday, 6 March 2002 14:53

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: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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 - 22:43:20 CST