X

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

  • October 1, 2015

DOP Downgrades, or Avoid The Ceiling

Yasin Baskan
Director, Product Management

We talked about how to find the reason of a DOP downgrade before. Let's look at the most undesirable downgrade reason, which is "DOP downgrade due to insufficient number of processes", and why it is undesirable.

PX Server Pool

Oracle allocates PX servers to SQL statements from a pool of processes. The size of this pool is determined by the parameter parallel_max_servers. This parameter acts as the limit for the number of PX servers that can be spawned by the database instance, just like the processes parameter limits the total number of processes for the instance. When the number of PX servers in an instance reaches parallel_max_servers no more PX servers will be spawned. This makes sure that the system does not get overloaded with processes.

The single exception for this is the usage of PX servers to access GV$ views in RAC. When you query a GV$ view one PX server is allocated on each instance, these PX servers are not counted against parallel_max_servers so they do not effect the number of PX servers that can be used by statements accessing other objects.

You can use the view V$PX_PROCESS to see the PX servers in your instance. This view also lists the PX servers used for queries accessing GV$ views in RAC, so you can use the predicate IS_GV='FALSE' to filter out those PX servers.

SQL> select server_name,status from v$px_process where is_gv='FALSE';
SERVER_NAME STATUS
-------------------- ---------
P007 IN USE
P001 IN USE
P005 IN USE
P004 IN USE
P003 IN USE
P000 IN USE
P002 IN USE
P006 IN USE
P008 AVAILABLE
P00A AVAILABLE
P00F AVAILABLE
P00D AVAILABLE
P009 AVAILABLE
P00E AVAILABLE
P00B AVAILABLE
P00C AVAILABLE
16 rows selected.

Be aware that Oracle will spawn PX servers up to parallel_max_servers when needed, this view only lists already spawned PX servers. So, do not expect to see the number of PX servers in this view to be equal to parallel_max_servers. The PX servers currently being used have STATUS='IN USE'.

The following query looks at the in-use PX servers and parallel_max_servers and shows the number of in-use PX servers and the number of available PX servers.

SELECT  
(SELECT COUNT(*) FROM v$px_process WHERE is_gv='FALSE' AND status='IN USE'
) inuse,
(SELECT value-
(SELECT COUNT(*) FROM v$px_process WHERE is_gv='FALSE' AND status='IN USE'
)
FROM v$parameter
WHERE name='parallel_max_servers'
) available
FROM dual;
INUSE AVAILABLE
----- ---------
8 28

PX Server Allocation to Statements

When a SQL statement requires PX servers these processes are allocated from the PX server pool. The number of PX servers required for a statement depends on the degree of parallelism (DOP) and the execution plan shape. Most statements are executed with DOP*2 number of PX servers because of the producer/consumer model. There are rare cases when a statement uses more than DOP*2 number of PX servers depending on the plan shape. Let's not go into the details of when this can happen as I will cover these cases in another blog post soon.

Here is an example showing two queries running on an instance with parallel_max_servers=36. One query is running with DOP=8 and using 16 PX servers, the other query is running with DOP=4 and using 8 PX servers.

As you see in the picture there are 12 PX servers not used by any statement, these are available for any statement.

Downgrades Due to PX Server Shortage

With the default configuration of the Oracle database (specifically when parallel_degree_policy=MANUAL) when the required number of PX servers for a statement is higher than the number of available PX servers that statement gets downgraded. This means the statement will run with fewer PX servers than it requires. When the statement is downgraded because there are not enough number of available PX servers you will see the reason "DOP downgrade due to insufficient number of processes".

Continuing with the same example the following picture shows a new query submitted with DOP=8 and requires 16 PX servers.

Since there are only 12 available PX servers this statement gets downgraded from DOP=8 to DOP=6 and gets all 12 available PX servers.

Consider another query submitted at this time with DOP=8. Even though this query requires 16 PX servers it will be given none because there are no available PX servers left. This query gets downgraded to DOP=1 and runs serially.

Downgraded? So What?

DOP downgrades can have a huge negative impact on SQL performance. It is not easy to quantify this impact without testing your query with different DOPs as it depends on the scalability of the statement and the downgrade percentage. If your statement gets downgraded from DOP=48 to DOP=36 maybe you can live with that but it gets worse as the downgrade percentage gets higher. For example, if the last statement above would complete in 10 minutes with DOP=8, it will now finish in 80 minutes assuming linear scalability as it will be using one process instead of eight. A statement cannot change its DOP after it starts, so even if the required number of PX servers become available just at the start of execution this statement will not be able to use them.

DOP downgrades due to PX server shortage also makes your SQL performance unpredictable. The performance of a statement depends on the number of available PX servers when it is executed, there is no guarentee that it will get the required number of PX servers. This causes users to get widely varying response times for the same statement.

Parallel Statement Queuing to Prevent Downgrades

This brings us to the next post in which we will talk about the Parallel Statement Queuing feature introduced in 11.2. Parallel Statement Queuing prevents downgrades by limiting the number of concurrent parallel statements in the system. When the required number of PX servers for a statement is higher than the number of available PX servers it queues that statement until that number of PX servers become available. More details to follow in the next post.

Be the first to comment

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