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