Why Isn’t My Parallel Query Running in Parallel?

by Granville Bonyata on January 13, 2018

If everything is set correctly and the plan shows that it is parallel, Oracle may still not actually assign the full number of threads (or even any extra threads at all). Here is a simple query to get the reason that Oracle downgraded the degree of parallel for a query:

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 = '3f3f4gfzdwwe'
     and plan_operation='PX COORDINATOR'
     and  otherstat_2_id=59
   order by sql_exec_id
/

Previous post:

Next post: