Re: Inconsistent SQL tuning results
Date: Mon, 7 Feb 2011 11:57:32 -0600
Message-ID: <AANLkTikiwDAgk5QJjnbQrQiug=ahHRzYYRWS96PGxDxm_at_mail.gmail.com>
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_at_gmail.com>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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 07 2011 - 11:57:32 CST