RE: PQ_DISTRIBUTE Usage in NON Parallel Queries
- From: "Larry Elkins" <elkinsl@xxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 6 Feb 2005 12:19:55 -0600
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@xxxxxxxxx
214.954.1781
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jonathan Lewis
> Sent: Sunday, February 06, 2005 10:50 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
