Path: news.easynews.com!easynews!crtntx1-snh1.gtei.net!cpk-news-hub1.bbnplanet.com!news.gtei.net!opentransit.net!proxad.net!news-hub.cableinet.net!blueyonder!amsnews01.chello.com!newsfeed.wxs.nl!transit.news.xs4all.nl!newsfeed.xs4all.nl!xs4all!not-for-mail
From: Herman de Boer <h.de.boer@itcg.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: Forcing PQ
Date: Thu, 08 Aug 2002 09:34:07 GMT
Organization: IT Consultancy Group bv
Lines: 55
Message-ID: <aitdu0$5gf$1@news1.xs4all.nl>
References: <aitaq1$h81$1@ctb-nnrp1.saix.net>
NNTP-Posting-Host: wc-106.r-195-85-159.essentkabel.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: news1.xs4all.nl 1028799232 5647 195.85.159.106 (8 Aug 2002 09:33:52 GMT)
X-Complaints-To: abuse@xs4all.nl
NNTP-Posting-Date: 8 Aug 2002 09:33:52 GMT
User-Agent: Noworyta News Reader/2.6
Xref: easynews comp.databases.oracle.server:157247
X-Received-Date: Thu, 08 Aug 2002 02:30:58 MST (news.easynews.com)

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.
>