Re: Parallel DML on a 2 CPU box

You need a better version of plan output when using partitions and parallel query really - there is one on my website which shows partitioning information and
the PX code.

The multiple 'sort group by' is a side-effect of the new strategies Oracle uses in PQ - you may find that there are various 'SYS_OP%' functions at one or two of the level.

The extra time though is possibly highlighted by the P -> P at the bottom sort group by. My guess would be that this sort does not reduce the number of rows significantly - try running my v$pq_tqstat report after
running the query , one of output columns is the number of rows passed from layer
to layer.

I'll risk being wrong on another guess - which is that the (a,b) columns are
NOT part of the partition hash key.

Have I suggested looking at v$session_wait and v$session_event whilst this is going on - if I am close to correct, then you will see lots of waits for (something like).

    "PX Deq: Send blocked"

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




Heikki Siltala wrote in message ...


>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

>