Check the parallel query is downgraded cause performance effects

Following query will check the DOP downgraded and reason:

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
select SID, sql_id, sql_exec_id, sql_exec_start,
case otherstat_2_value
when 350 then 'DOP downgrade due to adaptive DOP'
when 351 then 'DOP downgrade due to resource manager max DOP'
when 352 then 'DOP downgrade due to insufficient number of processes'
when 353 then 'DOP downgrade because slaves failed to join'
end reason_for_downgrade
from GV$SQL_PLAN_MONITOR
where sql_id = '&sql_id' and 
plan_operation='PX COORDINATOR' 
and otherstat_2_id=59;

Value meaning:

350: 'DOP downgrade due to adaptive DOP'
351: 'DOP downgrade due to resource manager max DOP'   e.g. max DOP is set for resource group
352: 'DOP downgrade due to insufficient number of processes'  e.g. value of parallel_max_servers would have been exceeded otherwise
353: 'DOP downgrade because workers failed to join'

Example :

Output:

3979 acbd4vsf6fhds 100663296 03-FEB-21 350 DOP downgrade due to adaptive DOP
1382 5uqn84sy70yrw 134217799 03-FEB-21 351 DOP downgrade due to resource manager max DOP
3574 gh611crjgchwv 50331648 04-FEB-21 353 DOP downgrade because slaves failed to join
4407 9vsyms57mn3gz 50332265 04-FEB-21 353 DOP downgrade because slaves failed to join
328 51un8afkg0j20 134217751 02-FEB-21 351 DOP downgrade due to resource manager max DOP
854 d5msut3f0r7c4 134217741 03-FEB-21 350 DOP downgrade due to adaptive DOP