RE: PQ_DISTRIBUTE Usage in NON Parallel Queries

Jonathan,

There weren't any noparallel or noparallel_index hints in the outline.

My first test case trying this Friday evening before sending the email was on 9.2.0.1 EE Windows 2000:

select ename from dept d, emp e where d.deptno = e.deptno and loc = 'DALLAS'

I looked at the OL$HINTS table and there were no PQ_DISTRIBUTE hints. So I sent out that email.

But when trying again today in response to you email I also noticed a rule hint in OL$HINTS!!! Duh, that's why I was having problems reproducing with my simple test case.

So I gathered stats on EMP and DEPT, did a create or replace outline on the statement, and sure enough there was a PQ_DISTRIBUTE hint -- "PQ_DISTRIBUTE (E NONE NONE)". I executed the SQL and verified in V$SQL.OUTLINE_CATEGORY that the outline was used (there is only one outline, so I know by the category it was used, and by the same token I could have done 10046 with a level 4 and seen the value for the binds used in the recursive SQL against the OL$ tables).

So, I then altered the two tables to include parallel degrees of 2. I flushed the shared pool, and re-executed the SQL. Once again the outline was picked up, the one created when the objects were noparallel.

So I then did a create or replace on the outline with the degree of parallelism set on the tables set to 2. The PQ_DISTRIBUTE hint became a "PQ_DISTRIBUTE (E NONE BROADCAST)". I flushed the shared pool and re-executed the SQL statement. The outline was picked up.

So to have even more fun I then removed the degree of parallelism from the tables, but the outline was still based on the tables having a degree of parallelism, including the "PQ_DISTRIBUTE (E NONE BROADCAST)". I flushed the shared pool and re-executed the statement. Once again the outline was used.

So what you were getting at with the "then the query could go parallel" statement was that even though the outline was based on serial access, that if one were to put a degree of parallelism on the object(s), that the outline would still be used, and that that's the reason for the pq_distribute hint being in the outline? In other words even though the outline was created based on serial access, if you then placed a degree of parallelism on one or more of the objects, the outline would still be used. Interesting (though I'm not sure I'm crazy about it as I might prefer different access and join methods when using parallel processing versus serial processing, but it does make sense considering the SQL statement itself is still the same).

Thanks for the response.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
> Sent: Sunday, February 06, 2005 10:50 AM
> To: oracle-l_at_freelists.org
> Subject: Re: PQ_DISTRIBUTE Usage in NON Parallel Queries
>
>
>
> Did the OL$HINTS table also have a noparallel
> and noparallel_index hint ? If not, then the query
> could go parallel, which would make a pq_distribute
> hint relevant..
>
> Alternatively, there are a few oddities in the 9.2.0.5
> when it comes to parallel query that I have not been
> able to reproduce in 9.2.0.4 and 9.2.0.6 (yet), so maybe
> you've found a symptom of a generic parallel bug.
>
>
> Regards
>
> Jonathan Lewis

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