Query running in parallel in one environment not in other

Question

I have one insert into select query, like :

INSERT /*+ append */ INTO  
select col1,col2...........  
from table1,table2(multiple table join) 

When I compare plan between prod and UAT, I see UAT is running in 4 DOP but prod is not running in parallel.

Query is exactly same, and checked parallelism at table level, but degree=1 in prod and UAT.

Not sure what is the reason running in parallel?

I assume when we use /*+ append */ internally it will opt for parallel, but why it is ignored in prod?

DB : oracle11g

Answer 1

Just execute

select * from table(dbms_xplan.display_cursor('<sql_id>', childnumber, format => '+NOTE ADVANCED ADAPTIVE');

And Oracle will tell you, at very bottom there is Note section:

Note
-----
   - Degree of Parallelism is 96 because of table property

There can be various reasons like TRIGGERS or Oracle Text index present.