Why your Parallel DML is slower than you thought

In the Data Warehouse Administration course that I delivered this week, one topic was Parallel Operations. Queries, DDL and DML can be done in parallel, but DML is special: You need to enable it for your session! This is reflected in v$session with the three columns PQ_STATUS, PDDL_STATUS and PDML_STATUS. Unlike the other two, PDML_STATUS defaults to disabled. It requires not only a parallel degree on the table respectively a parallel hint inside the statement, but additionally a command like ALTER SESSION ENABLE PARALLEL DML; Look what happens when I run an UPDATE with or without that command:

Two updates running in parallelThe table sales has a parallel degree of 4. The two marked statements seem to be identical – and they are. But the second has a much longer runtime. Why is that?

Parallel DML with QC doing the updateIt’s because Parallel DML is disabled in that session. The fetching of the rows can be done in parallel, but the Query Coordinator (QC) needs to do the update! That is of course not efficient. The mean thing is that you see actually Parallel Processes (PXs) running and appearing in the execution plan, so this may look like it does what it is supposed to – but is does NOT. Here is how it should be, with the correct ALTER SESSION ENABLE PARALLEL DML before the update:

Parallel DML with PXs doing the workThe QC does only the job of coordinating the PXs here that do both, fetching and updating the rows now. Result is a way faster execution time. I’m sure you knew that already, but just in case ;-)

 

About these ads

,

  1. #1 by Azhar Syed on July 12, 2013 - 17:49

    Thanks for the wonderful article as ever , we faced a similar situation in one of our production environments where in a parallel hint was used on a DML stmt which had a select subquery in it and the query caused contention and each parallel session was occupying insane amounts of temp tablespace (temp tbspc ~ 200gb) and hanging there bringing down the entire DB to its knees

    Is there any problem in using parallel hints on a RAC system ?

  2. #2 by Stefan Koehler on July 12, 2013 - 19:44

    Hi Uwe,
    great article. It seems like you do a lot of courses :-))

    Just in case if anybody needs more information about that topic. Check out the recorded webinar of Randolf Geist. It covers the basics (especially with SQL Monitoring) very well: http://allthingsoracle.com/analyzing-and-troubleshooting-oracle-parallel-execution/

    > Is there any problem in using parallel hints on a RAC system ?
    It depends as always, but not in general :-)) Your described issue does not look like a RAC specific one, but we would need more information about it.

    Regards
    Stefan

  3. #3 by Uwe Hesse on July 19, 2013 - 19:18

    Azhar, thank YOU for the nice feedback!
    Stefan, yes, plenty. But now I’m on vacation, starting today :) And I agree with your comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,708 other followers

%d bloggers like this: