Re: Inconsistent SQL tuning results

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 7 Feb 2011 11:15:55 -0700
Message-Id: <EF4431B4-BD18-423E-AB2E-9133DA19C354_at_centrexcc.com>

If you have the privilege to execute "alter system ... " commands, the privilege to start a trace should be child's play.

On 2011-02-07, at 11:07 AM, Michael Moore 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_at_optimaldba.com> 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
>>

>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 07 2011 - 12:15:55 CST