Re: Tuning unknown applications

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: tim@xxxxxxxxx
  • Date: Wed, 23 Mar 2011 22:47:31 +0000

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@xxxxxxxxx> 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 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.
> --

Niall Litchfield
Oracle DBA

Other related posts: