My Oracle Support Banner

How To Verify Parallel Execution is Running (Doc ID 237287.1)

Last updated on OCTOBER 03, 2019

Applies to:

Oracle Database - Personal Edition - Version 8.0.3.0 and later
Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Oracle Database - Standard Edition - Version 8.0.3.0 and later
Oracle Database Cloud Schema 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

Parallelism can be defined at 3 levels:
 
1. Instance level, defined in init.ora
2. Object level, defined in create/alter table/index
3. Query level, defined in a PARALLEL hint
 
A PARALLEL hint at the query level overrides table definition and the PARALLEL
parameters in init.ora, if any.  Similarly, the degree of parallelism in the
table definition overrides the PARALLEL parameters in init.ora.  Therefore it
is not enough to just check for the definitions at the instance level or the
table level to see if a query is run in parallel mode.  In fact, even a
PARALLEL hint in a query, when coded improperly, can be ignored as comments.
 
This bulletin shows different ways that can be used to check if parallel query
option is utilized.  The explain plan mentioned in Item 2 is the best way to
verify that a query is run in parallel mode.  The dynamic views and OS
commands are also good ways to verify PX, as long as they are issued at the
time of execution of the query to "catch" multiple processors in action.

Details

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

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


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