Re: Parallel adaptive tuning!!

"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 ?

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005