Re: Tuning unknown applications

  • From: Tim Gorman <tim@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 25 Mar 2011 13:16:08 -0600

OK, clarification...

I've seen that my comment "don't bother learning the application" looks to mean "don't learn the application".  I wish I had worded it better, sorry about that.  I should have been more accurate and instead say "don't wait to learn about  the application, just dive right into Method-R and learn it from the inside out" or something to that effect.

To paraphrase the Beach Boys, "I've been all around this great big world and I've tuned all kinds of apps", and all that's needed to get started is DBA permissions in your hands and Method-R singing in your head.  Oh yeah, a keyboard helps too...

Sorry for the confusion...

-Tim


On 3/25/2011 1:01 PM, TJ Kiernan wrote:
While I agree with the approach, I think there's something missing... I'd suggest making friends with an app admin or BA.  There's some technically proficient (if not Oracle proficient) business user out there somewhere.  They can tell you whether long-running SQL is a problem or not (background batch jobs may run for hours, while a user search takes 5 minutes - which is a problem?)  Before I became a DBA (and subsequently learned how Oracle works), I was an admin for a COTS package where performance mysteriously plummeted when we went into production (I mean seriously - we tested with a miniscule fraction of the data volume on completely different hardware, so why WOULDN'T the performance have remained the same or even improved in production?)  The vendor kept asking for AWR reports, and the DBAs dutifully produced them and closed the request ticket, and I'm not convinced that anybody knew what to do from there.  It seemed that regardless of how many AWR 
reports or manual sn
 apshots we took, the database just wouldn't figure out that we wanted it to go faster.  I wound up working with our dev team on analyzing explain plans for some poorly performing SQL that I had managed to identify, and we ended up blindly creating some indexes - one or some or all of which made some positive impact.

Cool story, T. J.   All of that to say, had there been some communication outside of opening & closing service tickets between me (the Business) and the DBAs, I might have been convinced to Millsap (known prior to Hotsos 2011 as 10046 trace) the known troublesome SQL and we would have been able to make intelligent decisions about how to go about fixing our performance problems while wasting less time.  Somebody who knows how the database works working with somebody who knows what the business and/or application is supposed to do is a rare, yet powerful combination.

Thanks,
T. J.
 


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
Sent: Wednesday, March 23, 2011 5:20 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Tuning unknown applications

Don't bother learning the application, focus on what the users say is 
hurting them. Step #1) look for the SQL statements taking tons of 
elapsed-time or response-time, #2) focus on the worst two or three SQL 
statements, #3) fix them, #4) implement the fix in production, and #5) 
repeat all over again starting from step #1.

Best to use SQL tracing on specific programs identified by users as 
performing poorly.  Check out white papers on www.method-r.com on tuning 
methodology and consider buying the book "Optimizing Oracle Performance" 
by Millsap and Holt (O'Reilly, 2003).




On 3/23/2011 3:44 PM, Ram Raman wrote:
List,
When DBAs are put in charge of unknown applications not developed in 
house or put in charge of third party COTS applications, how do we go 
about learning the systems and tune such systems.  This is an open 
ended question, but when I am asked to tune things, I am not sure how 
I would start without knowing the processes and data structure.
Thanks.
--
//www.freelists.org/webpage/oracle-l


--
//www.freelists.org/webpage/oracle-l




-- //www.freelists.org/webpage/oracle-l

Other related posts: