Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

New Way to Enable Parallel DML

Yasin Baskan
Director, Product Management
This post was triggered by Jonathan Lewis' tweet here.

The conventional way of enabling parallel DML is to enable it with an ALTER SESSION command as explained in the documentation. This command enables parallel DML for the session and all subsequent DML statements are candidates for parallel execution provided that the rules and restrictions for parallel DML are met.

12c introduces a new way of enabling and disabling parallel DML. Rather than enabling or disabling it session-wise you can enable or disable it statement-wise using new hints. The hint ENABLE_PARALLEL_DML enables parallel DML for the statement, and the hint DISABLE_PARALLEL_DML disables it for the statement. All rules and restrictions for parallel DML still apply, these hints are only alternatives to the related ALTER SESSION commands.

This example shows the plans for the same statement without and with the ENABLE_PARALLEL_DML hint.

We can see that parallel DML is not enabled for the above statement indicated by the LOAD operation being above the PX COORDINATOR in the plan. In 12c the notes section nicely and clearly shows that it is not enabled.

Here we see that the notes section does not mention parallel DML as disabled and the LOAD operation is under the PX COORDINATOR, both of these indicate that parallel DML is enabled.

These two new hints are available starting with and can be used regardless of the value of the OPTIMIZER_FEATURES_ENABLE parameter. They can be used in INSERT, UPDATE, DELETE, and MERGE statements. We are updating the related documentation to include these, I will update this post to include links when the documentation is refreshed.

Sep 2, 2015 UPDATE: The documentation is updated to include these hints, here and here.

Join the discussion

Comments ( 1 )
  • Jordan Monday, March 2, 2015

    Very convenient here! Glad you'll be updating the documentation to include this. Thanks so much for sharing.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.