Parameter Changes for Parallel Execution in Oracle Database 12c Release 2

As our new database release, Oracle Database 12c Release 2, is now available on the Exadata Express Cloud Service, the Exadata Cloud Service, and the Database Cloud Service, we can start talking about the new features and changes it brings.

In regards to Parallel Execution let me start with the initialization parameter changes in this new release.

The following parameters were deprecated long time ago but were still there prior to Oracle Database 12.2. We have now obsoleted and removed these parameters.


In Oracle Database 12.2 we are deprecating the Adaptive Parallelism feature which is controlled by the parameter parallel_adaptive_multi_user.

This feature adjusts statement DOPs based on the system load when the statement is submitted. If Oracle thinks the system load is high, the statement will be executed with a lower DOP than requested. In the worst case, it will even run in serial. This results in unpredictable performance for users as the response time of a statement depends on whether it is downgraded or not.

Prior to Oracle Database 12.2, the default value of this parameter was true which meant the feature was enabled by default. Now, the default value of this parameter is false and the feature is disabled by default.

To control system load and utilization we recommend using Parallel Statement Queuing and Database Resource Manager. Classifying users with difference performance requirements into resource manager consumer groups and allocating parallel resources to those consumer groups based on performance requirements is a much better way of controlling system utilization and ensuring predictable performance for users. Here are my slides from Open World 2015 that talk about how Parallel Statement Queuing and Database Resource Manager work and how you can configure them.

Please also check the documentation for all parameter changes in Oracle Database 12c Release 2.

In the coming days, I will be posting more about Parallel Execution changes and features in the new release.

Let’s block ads! (Why?)

The Data Warehouse Insider