Re: Tuning unknown applications
Date: Wed, 23 Mar 2011 22:47:31 +0000
Message-ID: <AANLkTin0NaLxayA2xVVT9GiJiVVxLi_Exuwd+kJJ=YVX_at_mail.gmail.com>
I mostly agree with Tim. I will say however that knowing what the application is, and isn't doing, is often extremely useful. We have a client that had a sql statement that regularly used 45 minutes of CPU every 15 minutes. Their db was, unsurprisingly CPU bound. We had several times highlighted the statement (they have outsourced developers) as a root cause. For various reasons this statement hadn't been changed. Last time I was onsite I asked how the module concerned was used. It turns out 40 sessions were active all day long, but the sessions only actually did useful work in 2 separate 2 hour periods (think polling a queue). We reduced load by 50% simply by changing the polling interval. We still started with what is consuming the time, but sometimes it is worth asking, can we reduce the load by working differently is more productive.
On Wed, Mar 23, 2011 at 10:19 PM, Tim Gorman <tim_at_evdbt.com> wrote:
> 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
>
>
>
-- Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 23 2011 - 17:47:31 CDT