I want to take a detour from the Auto DOP series to talk about system statistics related to DOP downgrades. In earlier posts I talked about finding the downgrades for individual SQL statements in SQL Monitor and finding the reason for the downgrades.
Rather than individual statements if you are trying to find out how many statements are getting downgraded in your system the obvious place to look at is (g)v$sysstat, or AWR for historical data. The statistics available are listed in the documentation as:
Parallel operations not downgraded
Parallel operations downgraded to serial
Parallel operations downgraded 75 to 99 pct
Parallel operations downgraded 50 to 75 pct
Parallel operations downgraded 25 to 50 pct
Parallel operations downgraded 1 to 25 pct
The statistic names are self-explaining so there is no need to describe each of them here.
One thing to be aware of is that as I have mentioned in the Auto DOP series Database Resource Manager (DBRM) limits are integrated with Auto DOP. So, for statements using Auto DOP you will not see any downgrade numbers as their DOP will be already adjusted according to DBRM, those statements will be counted under "Parallel operations not downgraded" unless they are downgraded for reasons other than DBRM. Here is an example showing this behavior.
With no DBRM plan and parallel_degree_limit set to CPU I get a DOP of 4 because of the parallel degree limit in my system.
With a plan that limits the DOP to 2 for OTHER_GROUPS which my username is mapped to I get a DOP of 2 because of the plan limit.
alter system set resource_manager_plan='plan1';
When you run this statement with the plan enabled and look at v$sysstat you will see that "Parallel operations not downgraded" is incremented.
Even if you have Auto DOP enabled in your system or session you can still have statements using manual DOP by using hints. Manual DOP is not integrated with DBRM, so the requested DOP will not be same as the actual DOP if the DOP limit in your plan is less than the DOP in the hint. These statements will show as downgrades in v$sysstat.
Here's the same query with a hint that specifies a specific (manual) DOP.
If you run this query you will see that the appropriate downgrade statistic is incremented.
Since the requested DOP was 8 and the actual DOP was 2 I got a 75% downgrade as indicated by the statistics.
For all other reasons other than DBRM downgrade statistics will be incremented in case of downgrades for both Auto DOP and manual DOP.