Oracle-L: RE: tuning question when you have a 3GL application

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Mon, 18 Aug 2003 19:28:41 -0800
Message-ID: <F001.005CB655.20030818192841@fatcity.com>

Writing an application in what is, essentially, vaporware, was a smart business decision
if I ever saw one. If you have said "COBOL", I'd understand, but you have said ".NET" and
that is the difference. Does that MS vaporware have a profiler? It would be very nice to use
it, because your problem might not be in the database. I distinctly recollect the situation
when I tuned the database to death and the application would freeze from time to time because
the connection pooling was buggy in iPlanet 6.0 and the whole application server would simply
freeze from time to time, much to the delight of the end users. Of course, the damagement kept
repeating that "the database froze" untill the DBA team has proven the contrary. I'm not
saying that your engineering team wrote decent queries, that would be too much to expect
from any gang of developers, what I'm saying is that you have miles to go before you sleep
and do the database part. Your developers should profile the application (prof, gprof,
pixie, cynthia) and find out in which routine the time is spent. It might not even be in
the routine that has anything to do with the database. Profiling the database part is easy.
That is what event 10046 is for, but you have first to establish that your problem really
is in the database. Statspack, on the other hand, is a kludgy tool for tuning applications
and a wonderful tool for tuning instances. Statspack will tell you what resources are lacking,
what types of requests were made to the database and will tell you what is the most expensive
SQL (not on the default run level). It will not tell you why is it the most most expensive SQL
and what can you do to fix it. Statspack is a a batch oriented tool which produces an ugly
report much later but if you want to see results right away, you should use drilldown option
in OEM (chap option) or use SQL*Lab/SQL Navigator from Quest SW, which is really an excellent tool.
Tuning an application is a tricky business and you must use it from beginning to the end, without
jumping to the parts you consider most likely culprits for bad performance. I you don't follow
the method from beginning to the end, you might be succesful when the database freezes over.

--

Mladen Gogala
Oracle DBA

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

rgaffuri_at_cox.net
Sent: Monday, August 18, 2003 3:24 PM
To: Multiple recipients of list ORACLE-L

We have two teams. Database team and a Software engineering team. The software engineering team wrote an application in .net.

We periodically get 'the application is slow' from them. I have not gotten the ok to run statspack in production.

Are there any canned scripts I can run to monitor the v$views or latch contention etc... It may not be the database, but I need some metrics.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <rgaffuri_at_cox.net
INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

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

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mladen Gogala
INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message