File Library

Since their introduction with database release 6.2, parallel server and parallel execution have become very important performance features of the Oracle database. Release after release the features were improved and new capabilities were added to the database management server. However it took until database release 11, until a major limitation of Oracle's parallel execution functionality got eliminated. Before that the degree of parallelism (DOP) with which a statement is executed had to be defined in statically, either by setting a degree on the objects involved or by stating the degree in the query itself. There was no functionality for dynamic adaptation of the DOP during execution with respect to the workload of the system and/or data volumes to be processed.

Release 11 finally added dynamic behavior to parallel execution by letting the database server software decide what DOP a statement shall be executed with and whether to do that immediately, or whether to queue the statement for a little while, until the required resources for parallel execution become available. Automatic degree of parallelism and parallel statement queuing are extremely powerful features for the development and operation of robust, low maintenance, high performance database applications with varying data volumes and workloads. In conjunction with the Oracle Resource Manager and In-Memory Parallel execution for Real Application Clusters they are key for handling complex queries and dynamic workloads. Today, the new database release 12c brings evolutionary enhancements in the dynamic parallel execution framework, plus revolutionary new features in the optimizer that are concerned with dynamically handling data distribution between parallel slaves.

This presentation reviews the 11g parallel execution features in detail and provides comparisons of typical query scenarios to be handled with and without application dynamic parallel execution. Best practices for applying them to various problems are given. Moreover, integration of dynamic parallel execution with Oracle Resource Manager. is discussed. Following that changes and improvements of the parallel execution framework in Oracle database release 12c are presented. Furthermore, the new 12c data distribution feature for parallel execution is explained. Finally, it is presented how the 12c optimizer feature 'adaptive execution plans' works regarding parallel execution. After having digested the talk's content, attendees will be equipped with the knowledge to develop database applications for database 12c. They'll be able to make efficient use of the latest parallel execution features.