Re: Parallel adaptive tuning!!

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 16 Dec 2005 14:10:37 +0100
Message-ID: <dnudom$21k$1@news2.zwoll1.ov.home.nl>

Jonathan Lewis wrote:
> "Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message
> news:dntuku$bke$1_at_news5.zwoll1.ov.home.nl...
>

>>Dexter wrote:
>>
>>>When i run a query with a parallel hint say /*+ parallel (a,8) */ , it
>>>doesnot seem to get any parallel  slaves, though all parallel servers
>>>are idle.
>>>
>>>When i check the run-time plan, it appears to have chosen parallel but
>>>there are no parallel slaves.
>>
>>And an hint is an hint, not a directive.
>>- are table(s) involved parallel?
>>- are statistics up to date?
>>- how did you come to the conclusion, no slaves
>> were being used?
>>- how did you generate the plan?
>>

>
>
> Wrong - a hint IS a directive, not just a mild
> suggestion that the optimizer can ignore at whim.
>
> If a hint is not followed then
> the syntax is wrong
> or the hint is out of context
> or there is a bug
>
> Mostly the problem with hints is that they aren't
> documented properly and no-one knows what
> each one is really supposed to do.
>
> Maybe the OP has used a table name when there
> is a table alias in place
>
> Maybe the query has an index-only execution path -
> the hint only says go parallel on the table if you have
> to visit the table. I have a test case with the following
> three SQL statements:
>
> select /*+ parallel(t1,2) */ count(*) from t1;
> select /*+ parallel(t1,2) */ count(id) from t1;
> select /*+ parallel(t1,2) */ count(id) from t1 where id > 0;
>
> The first two run parallel, the third runs serially - because
> it does a fast full scan on an index, and doesn't visit the
> table - and there is no parallel_index hint to make it go
> parallel.
>
>
> A question for the OP, how are you checking the
> run-time plan ?
>

We do actually agree here, but it's the choice of words: http://asktom.oracle.com/pls/ask/f?p=4950:8:11506770631431808713::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:8912905298920

<quote>
A hint "should be taken", if it is possible and sensible to take it </quote>

So: a hint cannot be a directive (as you do acknowledge), as it can be out of context.
Maybe I take the word directive too strong? Army green and blue pop to my mind with that word. And I do acknowledge the fact hints cannot (should not) be ignored at whim by the optimizer.

As to your example: the question about indexes being used either parallel or not, was on my list, but as the OP stated "explain showed parallel execution was used, but no parallel slaves were seen", I took it of again.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Fri Dec 16 2005 - 07:10:37 CST