Parallel_degree_policy AUTO caused serial plan in qat

Recently during testing a merge statement was running forever in our qat environment after a simple change. We had simply added a few columns to an existing table. After looking around we found that our qat environment had the parameter parallel_degree_policy set to AUTO, but our other environments had it set to MANUAL. I thought I would show how we figured this out.

I had the SQL_ID for the problem merge statement and used my sqlstat.sql script to see if the same merge statement ran in production. I did not find any production executions. I was told that this merge statement was already in our production code and ran all the time without an issue. So, then I used my findsql.sql script to find the SQL_ID of the production version of the merge statement. 

To use findsql.sql you have to edit it to replace MYUSER with a real user on your database. I use my own personal user. Then you have to update the LIKE statement in the where clause to identify the query that you want. I replaced this line in the findsql.sql text on GitHub:

SQL_TEXT like '%a.FISC_WK_OF_Yr < to_number(to_char(sysdate+1, ''iW''))%' and

with this line which is part of the text of the problem merge statement:

upper(SQL_TEXT) like upper('%MERGE INTO DDMADM.FWB_MDL MDL%') and

I easily found the equivalent merge statement in production. The QAT merge statement had SQL_ID 00cmatqz5wymd and the prod one had 7pgqd82bgdah2. My coworker, who had brought me in on this issue, said that on QAT the query was not running in parallel. So, I used my getplans.sql script to look at the two plans. On prod the plan used parallelism:

-----------------------------------------
| Id  | Operation                        
-----------------------------------------
|   0 | MERGE STATEMENT                  
|   1 |  MERGE                           
|   2 |   TEMP TABLE TRANSFORMATION      
|   3 |    PX COORDINATOR                
|   4 |     PX SEND QC (RANDOM)          
|   5 |      LOAD AS SELECT              
|   6 |       UNION-ALL                  
|   7 |        WINDOW SORT               
|   8 |         PX RECEIVE               
|   9 |          PX SEND HASH            
|  10 |           VIEW                   
|  11 |            UNPIVOT               
|  12 |             PX BLOCK ITERATOR    
|  13 |              TABLE ACCESS FULL   

On QAT it did not:

-----------------------------------------------------
| Id  | Operation                                    
-----------------------------------------------------
|   0 | MERGE STATEMENT                              
|   1 |  MERGE                                       
|   2 |   VIEW                                       
|   3 |    NESTED LOOPS OUTER                        
|   4 |     VIEW                                     
|   5 |      TEMP TABLE TRANSFORMATION               
|   6 |       LOAD AS SELECT                         
|   7 |        UNION-ALL                             
|   8 |         WINDOW SORT                          
|   9 |          VIEW                                
|  10 |           UNPIVOT                            
|  11 |            PARTITION RANGE SINGLE            
|  12 |             TABLE ACCESS BY LOCAL INDEX ROWID
|  13 |              INDEX RANGE SCAN                

I found this extra line at the end of the QAT plan:

- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

This triggered a memory of a coworker of mine who was trying out some degree of parallelism parameters a couple of years ago. After searching the internet, I quickly found that the automatic DOP message was related to the parameter parallel_degree_policy. We found that it was set to manual on prod, so we set it to manual on QAT and the merge statement went back to the expected performance.

I guess the point of this post is to keep a close watch on the messages that show up at the end of your execution plan. They could lead you to a parameter setting that is the source of your problem.

Lastly, this is my first post using the new WordPress Gutenberg plugin. What do you think? It was pretty easy to put in the output from my scripts in little unformatted boxes by themselves. Much easier than with the previous WordPress editor.

Bobby

P.S. I have found one negative to Gutenberg. I can’t find a way to use the Jetpack Proofreading function to check my grammar. So, if you see that I have used passive voice in a bunch of my sentences you will know why. Hopefully they will add this into a future update. Of course, I could just use my brain to check the grammar but where is the fun in that?

P.P.S. I started working on another blog post and found a simple solution to my grammar checking needs using Gutenberg. I just copy and paste the blog post text into Microsoft Word and use its grammar checker. Pretty neat.