RE: Tuning unknown applications
Date: Fri, 25 Mar 2011 14:01:08 -0500
Message-ID: <0EC85B1F3B2DF8409BF66DC182F495D9858D8F_at_DEEDS.pti-nps.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Wednesday, March 23, 2011 5:20 PM
To: oracle-l_at_freelists.org
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.
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 25 2011 - 14:01:08 CDT