My Oracle Support Banner

How to Analyze Why PX Queries Are Slow with AutoDOP Feature (Doc ID 1380736.1)

Last updated on AUGUST 17, 2021

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.


For developers and DBAs to troubleshoot slow running SQLs with the Automatic Degree of Parallelism (AutoDOP) feature.

How to analyze why PX queries are slow with Automatic Degree of Parallelism( AutoDOP ).
The idea behind calculating the AutoDOP is to find the highest possible DOP (ideal DOP) that still scales. In other words, if we were to increase the DOP even more above a certain DOP we would see a tailing off of the performance curve and the resource cost / performance would become less optimal. Therefore the ideal DOP is the best resource/performance point for that statement.

This article would like to give troubleshooting steps to analyze performance issues around PX queries running with AutoDOP feature.

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Troubleshooting Steps
 DB parameters that control the AutoDOP feature
 Step 1 Do you have a better execution plan for the statement?
 Step 2 Verify that the AutoDOP was really used for the slow statement.
 Step 3 Verify that the statement ran with parallel plan
 Step 4 Is the slowness due to parallel statement queuing?
 Step 5 RAC with interconnect bandwith issues
 Step 6 Check the statement with in-memory parallel execution turned off
 Step 7 If none of the above steps got you closer to the cause
 Still have questions?

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.