Re: Inconsistent SQL tuning results

  • From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
  • To: michaeljmoore@xxxxxxxxx
  • Date: Mon, 7 Feb 2011 11:57:32 -0600

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: