Re: Inconsistent SQL tuning results

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Mon, 7 Feb 2011 12:59:04 -0800
Message-ID: <AANLkTikx5=_JEQHhN-ty3Ngavj1PqOWJE5QTgAqmscK0_at_mail.gmail.com>

Looks interesting Cary.
I use TOAD SGA Trace. Not sure how it stacks up to other tools. It requires the V$ tables which I can have in DEV, but not in PROD. Hopefully our DEV environment can give me a clue as to what MIGHT happen in our PROD environment.

I've been able to get stats on all of my current runs which as I explained, run pretty fast (30 secs or so) but I am not able to reproduce the 7 minute run which happened once on my first test of the day.

In case anybody is curious, here is the hint I am using: /*+ PARALLEL(CAMPUS, 4) PARALLEL(BRS, 4) PARALLEL(BRG, 4) PARALLEL(TAFFILIATE, 4) */
Quest SQL Optimizer said this hint gave the fastest result.

I wouldn't worry about the 7 minute run if this SELECT statement ran several times a day, but it does not. It runs once a month and the actual job is taking about 5 hours. I'm afraid that the 7 minute (first time) test is more analogous to what is likely to happen in prod. Anyway, I'll wait until tomorrow morning and see if I can reproduce the 7 min run.

Thanks to everybody.

On Mon, Feb 7, 2011 at 12:37 PM, Cary Millsap <cary.millsap_at_method-r.com>wrote:

> 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_at_gmail.com>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 - 14:59:04 CST