In my last post I mentioned cases where a SQL statement uses more PX servers than expected, I said "There are rare cases when a statement uses more than DOP*2 number of PX servers depending on the plan shape.". I started this post to talk about those cases but then I thought maybe we should clarify the terminology before doing that. So let's go over the basic parallel execution terminology we use in the documentation and in the monitoring tools (V$ views, SQL Monitor, etc...).
A DFO is the basic unit of work carried out by PX servers in an execution plan. The set of PX servers working on a DFO is called a PX server set. The number of PX servers in a set is determined by the statement DOP.
Consider the following example.
SQL> explain plan for select /*+ parallel(2) */ * from sales; Explained. SQL> select * from table(dbms_xplan.display);
As a general rule DFO boundaries are indicated by two columns in an execution plan; the lines having PX SEND in the Operation column or TQ (Table Queue) in the Name column indicate DFO boundaries. In this plan we see that we have only one DFO, indicated by Line Id 2. This means this statement will use one PX server set having 2 PX servers as the DOP is 2.
Only very basic statements can be executed with a single DFO, most statements are executed by multiple DFOs. The following plan shows 2 DFOs which means 2 PX server sets.
SQL> explain plan for 2 select /*+ parallel(2) */ cust_id,count(*) 3 from sales 4 group by cust_id; Explained. SQL> select * from table(dbms_xplan.display);
We can say this again by looking at the PX SEND operations, Line Id 2 and 5. Since we have 2 DFOs this statement will use 2 PX server sets, each set will have 2 PX servers as the DOP is 2. So this statement needs a total of 4 PX servers. One PX server set will perform Line Id 5-8, the other set will perform Line id 2-4.
Any PX COORDINATOR in an execution plan is called a parallelizer. As we see in the above example there may be multiple DFOs under a parallelizer, these DFOs are grouped under a DFO tree, so the terms parallelizer and DFO tree are used interchangably. In the above examples we see that there is one parallelizer meaning one DFO tree.
A DFO tree in the plan is carried out by at most 2 PX server sets. If there is only one DFO under the DFO tree as the first example shows there will be only one PX server set. If there are two or more DFOs under the DFO tree 2 PX server sets will be used, we limit the number of PX server sets to 2 for a DFO tree. This is why most statements use 2 PX server sets meaning they will use DOP*2 number of PX servers. Let's look at a plan that has 3 DFOs.
SQL> explain plan for 2 select /*+ parallel(2) */ count(*) 3 from sales s, customers c 4 where s.cust_id=c.cust_id; Explained. SQL> select * from table(dbms_xplan.display);
Even though we have 3 DFOs there is only one DFO tree (one PX COORDINATOR), this means this statement will need 2 PX server sets, 4 PX servers as the DOP is 2.
You can find the number of PX server sets for a statement using the view v$pq_sesstat. If we run the last example statement above here is what we see.
SQL> select /*+ parallel(2) */ count(*) 2 from sales s, customers c 3 where s.cust_id=c.cust_id; COUNT(*) ---------- 9584391 SQL> select * from v$pq_sesstat; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------------ ---------- ------------- ---------- Queries Parallelized 1 1 0 DML Parallelized 0 0 0 DDL Parallelized 0 0 0 DFO Trees 1 1 0 Server Threads 4 0 0 Allocation Height 2 0 0 Allocation Width 1 0 0 Local Msgs Sent 387913 387913 0 Distr Msgs Sent 0 0 0 Local Msgs Recv'd 387913 387913 0 Distr Msgs Recv'd 0 0 0 DOP 2 0 0 Slave Sets 2 0 0 13 rows selected.
It shows what we had 1 parallelizer (indicated by the statistic DFO Trees), 2 PX server sets (indicated by the statistic Slave Sets), 4 PX servers (indicated by the statistic Server Threads, and the DOP was 2.
SQL Monitor shows the DFO trees and PX server sets in the Parallel tab of the SQL Monitor report as can be seen in the below screenshot.
It shows the PX server sets as "Parallel Set" and PX servers as "Parallel Server". It seems like we are doing everything to confuse the users by using different names everywhere. In this SQL Monitor report we see that we had 2 PX server sets and 4 PX servers.
Some statements can have more than one parallelizer (DFO tree). Since each parallelizer can use 2 PX server sets these statements can use more than DOP*2 number of PX servers. You can identify such statements by looking at the plan as explained above. If the plan has multiple PX coordinators it means the statement has multiple parallelizers. The following plan has 2 parallelizers as indicated by the number of PX coordinators.
SQL> explain plan for 2 select /*+ parallel(2) */ cust_id, (select max(cust_id) from customers) 3 from customers 4 order by cust_id; Explained. SQL> select * from table(dbms_xplan.display);
Here is what v$pq_sesstat shows after running this statement.
STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------------ ---------- ------------- ---------- Queries Parallelized 1 7 0 DML Parallelized 0 0 0 DDL Parallelized 0 0 0 DFO Trees 2 10 0 Server Threads 6 0 0 Allocation Height 2 0 0 Allocation Width 1 0 0 Local Msgs Sent 11878 1190228 0 Distr Msgs Sent 0 0 0 Local Msgs Recv'd 11873 1190215 0 Distr Msgs Recv'd 0 0 0 DOP 2 0 0 Slave Sets 3 0 0
We had 2 parallelizers (DFO Trees), 3 PX server sets (Slave Sets) and 6 PX servers (Server Threads). There are cases where v$pq_sesstat shows incorrect information when the plan has multiple parallelizers, so do not rely on this information if you have plans with multiple parallelizers, I will talk about those cases in the next post.
SQL Monitor shows DFO trees as Parallel Group in the Parallel tab as can be seen in the below screenshot.
Again we see that we had 2 parallelizers and 3 PX server sets.
After covering the basic terminology we can now talk about multiple parallelizers in more detail and look at how they work. Stay tuned for the next post. UPDATE 9 Dec 2015: That post is now published.