RE: Performance tuning in complex environment

Reminds me of the day when a third-party developed app (main batch program) ran *very* slowly - the user department went out and bought this app and server on their own without IT's blessing or support (a different story). Dialogue below:

Third-party Developer (TPD): This same batch program which runs 1hr 30 min on your box completes under 30 min at our Office with *your* data. We suggest obtaining an IBM S80 because it is 3 times faster than your current box

(IBM On-site person: Yes! Yes!!!)

User Department Manager (UDM): Ok - we have a $100,000 budget for this - lets go out and buy this h/w (We need to go through IT for this purchase)

My Manager, when approached with this issue (MM): I know your TPD has this view, but can my Sr. DBA look at this problem?

UDM: Ok, but I doubt anything can be done since my TPD says so...

TPD: Hey, your DBA can't mess with our code!

Sr.DBA (Me!): Ok - let's take a look at V$SYSTEM_EVENT, V$SESSION_EVENT and V$SESSION_WAIT when your program runs...

Me: Hey - what's this session doing with 'SQL*Net Message from dblink'? This is the top wait (more than 99% of TIME_WAITED in V$SESSION_EVENT)

TPD: Yeah - we have a view that makes a call to your employee table sitting on your prod box to fetch the Emp name, once for every row in the loop (1000s of rows, 3300 rows a pop)

Me: Haven't you guys heard of Replicated Tables?

TPD: What's that?

Me: (after creating a local copy and replacing the view with an indexed table) Run your program now...

TPD: Hey - it finished in 5 minutes!!! We don't need to buy any other box!

UDM: I like that!!! Thanks!!

MM: Well done - I knew my DBA could do it!

(IBM On-site person: $%##@#@#&*()+__@)

Me: (Hitting myself on the head, and thinking to myself: I should have asked for just 1% of the $$ that would have otherwise been unnecessarily spent on that great big H/w box :(

Moral of the story:

(a) Never ass*u*me anything - ask for stats to prove any 'assumption' (b) Get the right tools to determine the problem area (and use it correctly)

Afterthought (c) - Follow Gary Goodman's principle: Ask for 10% of the $$ allocated for the h/w that would have otherwise been spent on *trying* to solve the problem by throwing h/w at it! (Cary - correct me if I erred here!)

John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

>-----Original Message-----
>From: Avnish.Rastogi_at_providence.org
>[mailto:Avnish.Rastogi_at_providence.org]
>Sent: Thursday, December 11, 2003 11:29 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Performance tuning in complex environment
>
>
>Not really sure what happened and why we decided to that. I
>was involved in the beginning of project and remembered that
>PM was mentioning about talking to another Logician client who
>were facing same issues.
>
>-----Original Message-----
>Jamadagni, Rajendra
>Sent: Thursday, December 11, 2003 10:55 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Ummm ... what was the problem that prompted you guys to
>replace citrix servers?
>
>Raj
>---------------------------------------------------------------
>-----------------
>Rajendra dot Jamadagni at nospamespn dot com
>All Views expressed in this email are strictly personal.
>QOTD: Any clod can have facts, having an opinion is an art !
>
>
>-----Original Message-----
>[mailto:Avnish.Rastogi_at_providence.org]
>Sent: Thursday, December 11, 2003 1:35 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hello Everyone, I am trying to get some help/suggestions reg.
>how to troubleshoot performance issues.
>
>Little back ground about our environment. Its third party
>application (Logician) from GE. There are total 11 databases,
>all on oracle 8174 H-UX 11i in cluster environment. All the
>databases are on EMC Symmetrix using 6 disks. All the clients
>are connecting to database thru Citrix terminal servers.
>In last one year we spend lots of time/money in tuning
>databases, replacing Citrix servers but end result is same. I
>was wondering if anybody out there has ran into same kind of
>situation. Our (DBAs) guess is the disk layout is not optimal
>but we also dont have any data to prove that disks are the
>bottleneck. Is there any way to collect these kinds of stats
>in Oracle. We aren't getting much help from our SAN administrator.
>
>
>
>DISCLAIMER:
>This message is intended for the sole use of the individual to
>whom it is addressed, and may contain information that is
>privileged, confidential and exempt from disclosure under
>applicable law. If you are not the addressee you are hereby
>notified that you may not use, copy, disclose, or distribute
>to anyone the message or any information contained in the
>message. If you have received this message in error, please
>immediately advise the sender by reply email and delete this message.
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: <Avnish.Rastogi_at_providence.org
> INET: Avnish.Rastogi_at_providence.org
>
>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).
>
>
>***************************************************************
>***********************
>This e-mail message is confidential, intended only for the
>named recipient(s) above and may contain information that is
>privileged, attorney work product or exempt from disclosure
>under applicable law. If you have received this message in
>error, or are not the named recipient(s), please immediately
>notify corporate MIS at (860) 766-2000 and delete this e-mail
>message from your computer, Thank you.
>***************************************************************
>***********************5
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jamadagni, Rajendra
> INET: Rajendra.Jamadagni_at_espn.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).
>
>
>DISCLAIMER:
>This message is intended for the sole use of the individual to
>whom it is addressed, and may contain information that is
>privileged, confidential and exempt from disclosure under
>applicable law. If you are not the addressee you are hereby
>notified that you may not use, copy, disclose, or distribute
>to anyone the message or any information contained in the
>message. If you have received this message in error, please
>immediately advise the sender by reply email and delete this message.
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: <Avnish.Rastogi_at_providence.org
> INET: Avnish.Rastogi_at_providence.org
>
>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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).