RE: Database performance monitoring tool for developers.

Hmmm...I'm just not real sure on this one. My instinct (and the way we currently do things) is to encourage developers to work work with the DBA
(erm, "DBAs"! Plural now! Yay!) for performance tuning when they are
designing, developing, and testing. One of the reasons for this is that it can be very difficult to determine exactly why "that yellow bar on the I/O graph is so high", even for those who I feel are database-aware.

What we've done instead is created a set of procedures that the dev can use in our Dev and QA DBs (of course all development takes place in the QA DB <sigh>). The procedures are START_TRACE, STOP_TRACE, and STAT_TRACE. The START_TRACE starts a 10046 trace on their current session, ususally through TOAD in development. The STOP_TRACE stops the trace, runs TKPROF against the resulting tracefile, and copies the output from the udump directory
(which devs can't access) to their home directory on the development app
server. STAT_TRACE just gives a little blurb as to the status of the trace.

I like this approach instead of giving the devs a instance-wide monitoring tool because of a few reasons. First, it concentrates on the performance of the dev's SQL instead of instance/system performance. Second, while it's not a flashy graph, it is static and portable. All I really get from Spotlight is point-in-time. I believe Stealth Collect would have similar disadvantages to STATSPACK (wether level 5 or 10). Third, Spotlight and other tools generally need to run as DBA. As such, the dev now has power to do more than I am comfortable with, even in a dev DB. The killing of a process from within the DB -- I prefer killing at the OS level to workaround the bugs -- comes to mind. Finally, I don't want to manage yet another tool for the devs. I don't want to know what 8-10 concurrent sessions (if we were so licensed) of Spotlight would do to our little dev DBs. Wait until they discover "View...Options...Spotlight Console...Data Collection...Foreground Information" and change it to 5 or 3 seconds.

Am I just being an overly paranoid DBA or what? Is there such a thing as an overly paranoid DB? Any thoughts as to the 10046 trace approach vs an instance-wide tool like Spotlight?

BTW, I happen to *love* Spotlight on Oracle by Quest and am well aware of it's abilities to drill down to a process. It is my favorite monitoring/troubleshooting tool for Oracle, unless Steve the Spotlight product manager is listening -- then I have a lot of "it's GREAT, but if you could just change..." ;)

My $.02,
Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Wednesday, April 16, 2003 9:54 AM
To: Multiple recipients of list ORACLE-L

No development should ever take place without the developers having access to performance monitoring tools. The developers really should be the first ones to see the effects on performance on their code.

/Bjørn.

Shamita & Chiran Ghosh wrote:

Hello, How does the list feel about providing developers access to ad-hoc/real-time, problem solving diagnostics tool such as Spotlight, Mamba etc. to developers to provide first-line support. I'm not so crazy about the it; but was wondering on what the list's opinion would be. Comments please. Thanks.

Chiran Ghosh

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.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
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).