Re: Inconsistent SQL tuning results
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: "D'Hooge Freek" <Freek.DHooge@xxxxxxxxx>
- Date: Mon, 7 Feb 2011 16:55:55 -0800
Yeah ...
I was thinking the same thing about page-cache and disk-buffer cache.
Apparently, the only cost effective way to test program A in environment B
at time X is to actually run program A in environment B at time X. I'm not
saying that some basic performance testing is not useful, but it seems to
me, that beyond a point, the diminishing returns cease to justify the time
and effort.
In my case, since the risk is low, I'll just make an informed guess and toss
it in production and see what happens.
Regards,
Mike
On Mon, Feb 7, 2011 at 2:53 PM, D'Hooge Freek <Freek.DHooge@xxxxxxxxx>wrote:
> First of all, yes sql tracing is what needs to be done here.
> But, it might also be a good idea to, on the same moment you are tracing,
> monitor your server.
>
> One reason that you get better performance during succeeding runs, even
> after clearing the buffer cache, is that the data is cached somewhere else
> then in the db buffer cache.
> This could be the filesystem cache or the san cache (if you are using a
> san).
> The sql trace files will, in such cases, only show a faster response time
> for your read operations (single block read, scattered read, direct .).
> When using a tool like collectl on the same time, you would see for example
> a growth in the filesystem cache during the first execution.
>
>
> Regards,
>
> Freek D'Hooge
> Uptime
> Oracle Database Administrator
> email: freek.dhooge@xxxxxxxxx
> tel +32(0)3 451 23 82
> http://www.uptime.be
> disclaimer: www.uptime.be/disclaimer
> ---
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Cary Millsap
> Sent: maandag 7 februari 2011 18:58
> To: michaeljmoore@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Inconsistent SQL tuning results
>
> Answer: Trace it in both circumstances, using
> http://method-r.com/downloads/doc_details/72-mastering-performance-with-extended-sql-trace
>
> What to look for: Where is the difference in the time? Drill in from there.
>
> Perhaps both executions use different SQL execution plans and do
> tremendously different amounts of fetch work.
> Perhaps the construction of the execution plan is causing the response time
> difference.
>
> You don't have to guess. The trace files will tell you.
>
> If you have a hard time reading the trace data, send me a zip of the two
> raw trace files (NOT tkprof output), and I'll help.
>
>
> Cary Millsap
> Method R Corporation
> http://method-r.com
> http://carymillsap.blogspot.com
>
> On Mon, Feb 7, 2011 at 11:49 AM, Michael Moore <michaeljmoore@xxxxxxxxx>
> wrote:
> I've been trying to tune a SQL statement but I get very inconsistent
> results.
>
> I always start with:
> alter system flush shared_pool;
> alter system flush buffer_cache;
>
> Then I run the SQL, but the first time I run it, it can take as much as 7
> minutes. On the 2nd, 3rd, and 4th runs, it takes
> 40 sec, 49 sec, 35 sec respectively.
>
> So my question is: What might account for the huge difference in run time
> between the first run and successive runs?
>
> Thanks,
> Mike
>
>
Other related posts:
