Re: Parallel DML on a 2 CPU box

From: Heikki Siltala <peaceprogress_at_yahoo.com>
Date: 28 Jul 2001 00:07:53 -0700
Message-ID: <b162336e.0107272307.4b7c12ef@posting.google.com>

Actually I'm running 8.1.7 EE with partitioning option. I'm using cost based optimizer and the table is analyzed.

On your advise I checked the execution plans. When running the query with one process the plan was

| SELECT STATEMENT | | 36 | 1K| 128356 |
| |
| SORT GROUP BY | | 36 | 1K| 128356 |
| |
| PARTITION HASH ALL | | | | | 1
| 4 |
| TABLE ACCESS FULL |TEST_TABLE| 13M| 391M| 50827 | 1
| 4 |

and running it with four processes (Oracle chooses this)

| SELECT STATEMENT | | 36 | 1K| 128356 |
| |
| SORT GROUP BY | | 36 | 1K| 128356 | 4,02
| P->S |
| SORT GROUP BY | | 36 | 1K| 128356 | 4,01
| P->P |
| SORT GROUP BY | | 36 | 1K| 128356 | 4,00
| P->P |
| PARTITION HASH ALL | | | | | 4,00
| PCWP |
| TABLE ACCESS FULL |TEST_TABLE| 13M| 391M| 50827 | 4,00
| PCWP |

so it seems that the optimizer "chooses wrong".

Thinking simple: there are one "sort group by" in the parallel degree 1 execution plan and three of them in parallel degree 4 execution plan. As I said, the latter takes three times longer to execute. My guess is that "sort group by" is the bottleneck.

--
Heikki Siltala

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> You don't give a version number for Oracle,
> but I suspect it was not 8.1
> 
> Earlier versions has a problem with distinct()
> in parallel queries. Your execution path was
> probably to select all rows, then distribute
> ALL the rows by ranges of (a,b) across
> a second layer of slaves - resulting in
> millions of messages, with continuous
> TQ blocking - followed by the sorting
> at the higher level do the distinct.
> 
> Newer versions of Oracle are able to
> handle some of the grouping and distinct
> at the lower level, resulting in much less
> message passing, contention and task
> switching.
> 
> When testing parallel query, start a new
> session, run the query, then look at
> v$pq_tqstat - (see my website for further
> details).
> 
> 
> --
> Jonathan Lewis
> 
> Host to The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> 
> Author of:
> Practical Oracle 8i: Building Efficient Databases
> See http://www.jlcomp.demon.co.uk/book_rev.html
> 
> Seminars on getting the best out of Oracle
> See http://www.jlcomp.demon.co.uk/seminar.html
> 
> Screensaver or Lifesaver: http://www.ud.com
> Use spare CPU to assist in cancer research.
>

Received on Sat Jul 28 2001 - 02:07:53 CDT