Re: Forcing PQ

Hello Billy,

try other join methods: use_hash instead of use_nl. Set the hash_area_size to a value of at least 20 Mbyte.

Also check: parallel_max_servers, and the number of available slaves (select slave_name, status from v$pq_slave). Is there a sequence used or a stored function?

It would be useful for this kind of questions to include the query and the plan in the post, along with relevant parameters.

Kind Reghards,

Herman de Boer.
sr consultant
IT Consultancy Group bv.

Billy Verreynne wrote:

>The software:
> Oracle 8.1.7
>HP-UX platform
>
>The tables:
>Big one (30+ million rows).
>Several smaller ones ranging from few 100 rows to a 10's of 1000's of rows
>(indexed).
>All tables analyzed (estimate 10% on big one).
>
>The query:
>Straight forward full tablescan of the big one, using nested loop joins with
>the smaller ones.
>
>The result to be stored in a new table (i.e. create table unrecoverable as
>select). Hints ORDERED and USE_NL are used to force the correct join order.
>All rows in big table needs to be processed first, thus the FTS, then using
>nested loops with unique index scans to the smaller tables.
>
>The problem:
>The query itself is fast. Okay, relatively fast given the large number of
>rows. I can also force it to run in parallel when it is a pure SELECT
>count(*) (i.e. no CREATE TABLE). However, when adding the CREATE TABLE, it
>ignores the PARALLEL hint in the SELECT and the PARALLEL clause in the
>CREATE TABLE.
>
>Have tried ALTER SESSION ENABLE PARALLEL DML. No change. The SQL still runs
>serial.
>
>Suggestions?
>
>Thanks.
>
Received on Thu Aug 08 2002 - 04:34:07 CDT