DOP Downgrade?

쿼리를 수행하다가 보니 SQL_MONITOR에서 DOP Downgrade라는 부분이 있었습니다.

저는 해당 쿼리를 PARALLEL 8로 수행하였는데, SQL_MONITOR에서는 DOP Downgrade : 100%라고 나오며 serial하게 수행되고 있었던 것이죠.

저도 첨보는 현상이라 검색을 좀해보니 여러 이유로 일어날 수 있는 일이고, 쿼리에서 전체 다 그런것이 아니라 일부분만 serial하게 처리되고 나머지부분은 parallel하게 처리될수도 있더라구요.

 

그래서 v$sql_monitor를 이용한 쿼리를 통해서 DOP downgrade가 일어날때 어떤 이유로 일어났는지에 대해서 알수 있는 방법을 적어보겠습니다.

 

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'

 

출처에서 가져온 쿼리인데, 아래를 보면 350 ~ 353번까지의 번호가 나타나게됩니다. 이때 이 번호를 통해 어떤 이유로 DOP downgrade가 발생했는지를 알 수 있네요.

 

 

출처 : https://blogs.oracle.com/datawarehousing/post/finding-the-reason-for-dop-downgrades

Finding the Reason for DOP Downgrades

출처 : https://smarttechways.com/2021/10/22/check-the-dop-downgraded-sql-in-oracle-cause-performance/

Check the DOP downgraded SQL in Oracle cause performance