Re: Inconsistent SQL tuning results

Mike,

I feel your pain. See
http://carymillsap.blogspot.com/2010/08/mister-trace.html.


Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com


On Mon, Feb 7, 2011 at 12:07 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:

> Thanks guys.
> I will run trace, even if it does take an act of congress and a letter from
> my doctor to get the dbas to give my the necessary privs.
>
> I'll let you know how it went.
> Thanks again,
> Mike
>
>
> On Mon, Feb 7, 2011 at 9:59 AM, Daniel W. Fink <daniel.fink@xxxxxxxxxxxxxx
> > wrote:
>
>>  Without additional information, the only option available is "Guess".
>>
>> Run extended sql trace (dbms_monitor.session_trace_enable, event 10046,
>> etc), run the trace file(s) through tkprof and analyze the differences.
>>
>>
>>
>> On 2/7/2011 10:49 AM, Michael Moore 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
>>
>>
>>
>> No virus found in this incoming message.
>> Checked by AVG - www.avg.com
>> Version: 9.0.872 / Virus Database: 271.1.1/3428 - Release Date: 02/07/11 
>> 00:34:00
>>
>>
>>
>

Other related posts: