X

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

  • November 8, 2016

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

Yasin Baskan
Director, Product Management

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.

Obsoleted and desupported parameters

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.

parallel_server
parallel_server_instances
parallel_io_cap_enabled
parallel_automatic_tuning

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

Join the discussion

Comments ( 2 )
  • Mikhail Velikikh Wednesday, November 9, 2016

    Hello Yasin,

    I have just checked 12.2 documentation and I see that old restriction about the first statement exceeding parallel_server_limit is still present:

    http://docs.oracle.com/database/122/ADMIN/managing-resources-with-oracle-database-resource-manager.htm#ADMIN13466:

    If a consumer group does not have any parallel statements running within an Oracle RAC database, then the first parallel statement is allowed to exceed the limit specified by PARALLEL_SERVER_LIMIT.

    It means that to effectively restrict the number of parallel processes allocated to the consumer group, we have to use parallel_degree_limit_p1 together with parallel_server_limit in a DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE call.

    IMHO, it would be great if we could do the same using only PARALLEL_SERVER_LIMIT alone.

    To be honest, I have not checked this restriction in 12c, but it was present in 11.2.0.4. That is what my SR is about: SR 3-9923952441 : RC:PARALLEL TARGET PERCENTAGE RESTRICTION DID NOT WORK


  • Yasin Baskan Thursday, November 10, 2016

    Hi Mikhail,

    Yes, the behavior is the same in 12.2.

    When there are no queries running, the first query can exceed parallel_servers_target or parallel_server_limit.

    We recommend using parallel_degree_limit_p1 to cap the DOP. For example, if parallel_servers_target=128 and parallel_server_limit=50%, the queuing point for your consumer group will be 64. If you set parallel_degree_limit_p1=32, for most of the queries the number of PX servers used will be capped to 64 (because of the 2 PX server set model), which means they will stay under parallel_server_limit.


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