My Oracle Support Banner

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

Last updated on AUGUST 14, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata 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.

Purpose

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
Purpose
Troubleshooting Steps
 DB parameters that control the AutoDOP feature
 PARALLEL_DEGREE_POLICY
 PARALLEL_MIN_TIME_THRESHOLD
 PARALLEL_DEGREE_LIMIT
 PARALLEL_SERVERS_TARGET
 PARALLEL_MAX_SERVERS
 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?
References

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