RE: PX Deq Credit: send blkd

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Fri, 19 Sep 2008 10:01:32 -0400
Message-ID: <667C10D184B2674A82068E06A78382B527A37073@AAPQMAILBX01V.proque.st>

Hi Martin,

I'm not in a position to test at the moment, but how about: create table new_tab parallel 32 as select /*+ parallel(t 32) */ * from tab1 t; ??

Also, you may want to add 'nologging' to make it fly even faster. (But, consider the recovery implications.) create table new_tab nologging parallel 32 as select /*+ parallel(t 32) */ * from tab1 t;

Of course, this assumes you have enough CPU and I/O available to support 32-way parallelism, else you may be in for a disappointment.

Hope that helps,

-Mark

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Martin Klier [usn_at_usn-it.de] Sent: Friday, September 19, 2008 7:55 AM To: oracle-l
Subject: Re: PX Deq Credit: send blkd

Hi again.

I've got still the same problem, but I tried to simplify it, reduce it to the essentials.

I tried to
CREATE TABLE2 TEST PARALLEL 32 AS SELECT * FROM TEST; It works, but the 32 parallel processes are only query processes, there is only ONE writing process. Due to that, all parallel query processes are waiting with "PX Deq Credit: send blkd"

How can I make it parallel on WRITING side of the statement?

The bahaviour is similar or same if I do an INSERT /*+ APPEND PARALLEL(TEST2,32) */ INTO TEST2 SELECT * FROM TEST; when both sides are identically partitioned (4 partitions).

Any further clues? Or known no-gos?

Thanks a lot,
Martin

Martin Klier schrieb:
> I've got massive wait events named "PX Deq Credit: send blkd". As far as
> I know, that's a parallel query issue, a producer is faster then the
> consumer. But thats a textbook explanantion I simply don't understand.
> The obvious option, to increase PARALLEL_EXECUTION_MESSAGE_SIZE, does
> not fit since it's set to the (x86_64) architecture's maximum, 64k.
>
> The system is rather capable, and CPU load is less than 30%, disk IO
> around 100MB/s, the underlying ASM diskgroups have been successfully
> tested with nearly 1GB/s.
>
> The query in question is a huge MERGE statement with APPEND PARALLEL
> hints, a little abstracted form here:
> ...

--
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de

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


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

Received on Fri Sep 19 2008 - 09:01:32 CDT