Until 11.2 there were several ways for developers/DBAs to request parallelism for a SQL statement. 11.2 brought another method which is called Automatic Degree of Parallelism (Auto DOP). I will try to cover what Auto DOP is, how it works, and how to control and configure it in a series of blog posts here. Some of this information is already covered in the documentation and the Parallel Execution with Oracle Database 12c Fundamentals white paper, so it would be a good idea to start with those and complement them with this blog series.
This first part covers what Auto DOP is basically.
Before we talk about Auto DOP let's look at the two most common ways to request parallelism for a SQL statement before Auto DOP. Since these methods require manual intervention in the table/index or SQL level we call the DOP set by these methods manual DOP.
You can set a specific DOP for tables/indexes and SQL statements accessing those tables/indexes will request the specified DOP.
ALTER TABLE sales PARALLEL 16;
You can use statement level or object level PARALLEL hints to request a specific DOP for a statement.
SELECT /*+ parallel(16) */ COUNT(*) FROM customers;
You can think of several problems with manual DOP but I think the biggest problem is time; as time passes the DOP you set for today may not give you the same performance tomorrow. The database and the platform it runs on is not static. What happens when your data size doubles? When you change your HW with a faster system? Tomorrow you will need maybe more, maybe less DOP for the same statement. Think about the case when the database gets even smarter and starts to execute operations faster, this means you can get better performance with fewer processes which means less DOP. All of these changes will require you to go back to the tables/indexes and your application to adjust the DOP settings.
Other things to consider are; how does a user decide on a DOP for a statement? What about business users who do not know how to manually set the DOP? Does a user consider the HW configuration when setting a DOP, etc...?
Introduced in 11.2 Auto DOP enables the optimizer to calculate the DOP for a statement based on resource requirements and HW characteristics. As opposed to manual DOP it does not require table/index decorations or hints.
Here is the basic decision flow for a SQL statement with Auto DOP.
The optimizer first generates a serial plan for the SQL statement and estimates the execution time. If the estimated execution time is less than the specified threshold the statement runs serially. If the estimated execution time is greater than the threshold the optimizer generates a parallel plan and calculates a DOP based on resource requirements. I am not going into details like how to set the threshold, how the optimizer calculates the DOP, etc... here, they are for upcoming posts in this series.
With Auto DOP since the optimizer is deciding when to use parallel execution and the DOP to use, depending on how you configure Auto DOP, the number of parallel statements and their DOPs may change when you enable it. Some serial statements may start running in parallel, some parallel statements may start running serially. This can change the response time of individual statements and also the resource utilization in your system. Auto DOP relieves the burden of deciding on a DOP for each statement and enables you to focus on optimizing the whole workload together with Parallel Statement Queuing and Database Resource Manager. We will talk about how these work together in later posts.
In the next post we will look at how to configure and control Auto DOP, in the meantime please comment if you have any questions here.
UPDATE: Next post of this series is here.