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