Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Help with 10046 level 12 trace

Re: Help with 10046 level 12 trace

From: Philip Douglass <philipd_at_sirs.com>
Date: Thu, 22 Aug 2002 16:38:24 -0800
Message-ID: <F001.004BDC28.20020822163824@fatcity.com>


Yes, I noticed the noticed the extra parses too. I've noticed other Delphi developers (including some of our own) also have a strong tendency to produce very similar inefficient code. I haven't asked, but I wonder if there is something in that development environment that encourages this kind of coding.

It seems to me that since it is so obvious where the primary problem is, as well as what the solution is, there isn't much point in regretting how much data tkprof ignores in the tracefile. Whether or not the excessive parsing is causing latch contention seems irrelevant, unless one really wants to beat the poor Delphi developer over the head with it. On second thought...

--
Philip Douglass
Internet Networking Group
Database Administrator
SIRS Mandarin, Inc.

----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, August 22, 2002 6:14 PM


I'll second that motion. Oddly, though, it's even worse than that: This
code is actually parsing MORE times than it's executing! ("Parse.
Thanks, but nevermind. Okay, now parse the same thing again. Okay, now
execute...")

I pulled the trigger pretty quickly a minute ago when I suggested that
Mandar should upload the file to our site. There's a lot you can learn
from tkprof output alone. However, amplifying Mladen's point a little
bit, you're losing a lot of data by viewing raw trace files with tkprof.
For example, are so many parse calls causing latch contention to be a
significant component of response time? With tkprof output, we can only
guess.

<ad>Even tkprof 9i output ignores significant amounts of useful
data.</ad>

We'll be glad to look at the raw trace file. Just let me know if you'd
like us to.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic, Oct 1-3 San Francisco, Oct 15-17 Dallas, Dec 9-11
Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Next event: Miracle Database Forum, Sep 20-22 Middelfart Denmark
-----Original Message----- Douglass Sent: Thursday, August 22, 2002 4:20 PM To: Multiple recipients of list ORACLE-L Well, I'm sure a guru here will correct me if I am wrong, but at first blush it looks like your developer is not only parsing, executing and fetching in a loop, but is also parsing a few extra times just for fun. I'm pretty sure you will find that the bulk of your performance problem lies there. == the bad way == loop parse execute fetch close end loop == the good way == parse execute loop fetch end loop close -- Philip Douglass Internet Networking Group Database Administrator SIRS Mandarin, Inc. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 22, 2002 4:41 PM Hello All, One our lead developers has just written a Delphi application to process some printing jobs. The application would every other minute query a job table and generate a report to be printed to specific network printers. I ran a 10046 level 12 trace using dbms_system.set_ev. I would be very glad if experts can help me understand how to interpret the trace and tkprof output. Also pls let me know if ull find anything which points to or would lead to performance problems. I am thinking of setting session_cached_cursors to a non zero value. thanks Mandar Tkprofed trace file [...snipped by Philip Douglass...] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Philip Douglass INET: philipd_at_sirs.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 Thu Aug 22 2002 - 19:38:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US