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:
The 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?
It’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:
The 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 😉
Pickleball spielen 
#1 von Azhar Syed am Juli 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 von Stefan Koehler am Juli 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 von Uwe Hesse am Juli 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.