Re: Inconsistent SQL tuning results

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Mon, 7 Feb 2011 10:07:33 -0800
Message-ID: <AANLkTi=QZmQkJWuxmRxxW0ZfWTxVQw1vTdU3--LcJ5eE_at_mail.gmail.com>

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:07:33 CST