blog. aregger.io

A blog about Oracle, SQL and Java

twitter github

DOP Downgrade Reason Codes

20 Jul 2020

Starting with Oracle 12.1 SQL Monitor reports show you the reason in case your parallel query has been downgraded. This blog post by Mohamed Houri demonstrates that the reason codes along with a description can be found in some fixed table.

As the numbers can change from release to release and I don’t always have easy access to fixed tables I decided to quickly query the reason codes for all major releases and list them here. Maybe they come in handy in the future.

The following query is used to get the reason codes:

SELECT
  indx
  ,qksxareasons
FROM
  x$qksxa_reason
WHERE
  qksxareasons like '%DOP downgrade%';

12cR1

INDX QKSXAREASONS
---- -----------------------------------------------------
 350 DOP downgrade due to adaptive DOP
 351 DOP downgrade due to resource manager max DOP
 352 DOP downgrade due to insufficient number of processes
 353 DOP downgrade because slaves failed to join

12cR2

INDX QKSXAREASONS
---- -----------------------------------------------------
 351 DOP downgrade due to adaptive DOP
 352 DOP downgrade due to resource manager max DOP
 353 DOP downgrade due to insufficient number of processes
 354 DOP downgrade because slaves failed to join

18c & 19c

INDX QKSXAREASONS
---- -----------------------------------------------------
 352 DOP downgrade due to adaptive DOP
 353 DOP downgrade due to resource manager max DOP
 354 DOP downgrade due to insufficient number of processes
 355 DOP downgrade because slaves failed to join