Oracle Parallel Query

4 min readSep 6, 2021

Most of the time people use parallel select statements to take fast performance. In Oracle there are number of ways to enable Query Parallelism. But in order to get understand on how Oracle handle parallelism, I just though to share my experience.

Before that it will be great to know terms that used in Oracle Parallel Query technology. Let’s go through them first.

Degree of Parallelism (DOP)

The number of parallel execution processes associated with an operation is called its degree of parallelism (DOP). DBA can use manual DOP or automatic DOP.

Default Parallelism

When we use Parallel cluse without DOP, Oracle use default parallelism. This is calculated in below manner.

For a single instance,

DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT

For an Oracle RAC configuration,

DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNT x INSTANCE_COUNT

INSTANCE_COUNT: Number of Nodes in the Cluster. Default it takes all the nodes in the cluster, if you have not limited the nodes count using services.

PS: The DOP for a SQL statement can also be limited by Oracle Database Resource Manager.

How to Enable Parallel Query

There are several options that can be implemented to achieve Parallel Query. Below criteria describe how Oracle achieve this.

Oracle retrieves the PARALLEL specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings. If Query contains several tables with different DOP, Oracle Database uses highest DOP.
Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step.

Conclusion

The degree of parallelism used for a SQL statement can be specified at three different levels:

Statement level

Using hints or the PARALLEL clause

Object level

Found in the definition of the table, index, or other object

Instance/Session level

Using values for the Instance or Session

Oracle determines the degree of parallelism to use for a SQL statement by checking each item in this list in the order shown. Oracle first checks for a degree of parallelism specification at the statement level. If it can’t find one, it then checks the table or index definition. If the table or index definition does not explicitly specify value for Parallel, Oracle uses the default values established for the instance or session level.

Test Cases

Parallel Query Enabled on Session Level & Table doesn’t have parallelism in it’s DDL. Oracle run query in serial mode.

Parallel Query Enabled on Session Level & Table level parallelism set to 6. Oracle run query in parallel mode as per the parallel parameters set at Table level.

Parallel Query Enabled on Session Level, Table level parallelism set to 6 & Query include hint with parallelism at 8. Oracle bypass Table level values and use the value from SQL hint.

Parallel Query Disabled on Session Level, Table level parallelism set to. Oracle runs query in serial mode and omit Table level parallelism.

Parallel Query Disabled on Session Level, Table level parallelism set to 6 & Query include hint with parallelism at 8. Even though session level Parallel Query disabled Oracle run the query with Parallel 8 from the hint.

Parallel Query Forced to 10 on Session Level, Table level parallelism set to 6 & Query include hint with parallelism at 8. Even though session level Parallel Query Forced to 10, Oracle run the query with Parallel 8 from the hint.

Notes:

Get SID of Current Session

select distinct sid from v$mystat

Check status of Parallel DML,DDL, and QUERY use below Columns in V$SESSION

PDML_STATUS: If ENABLED, the session is in a PARALLEL DML enabled mode. If DISABLED, PARALLEL DML enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DML.

PDDL_STATUS: If ENABLED, the session is in a PARALLEL DDL enabled mode. If DISABLED, PARALLEL DDL enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL DDL.

PQ_STATUS: If ENABLED, the session is in a PARALLEL QUERY enabled mode. If DISABLED, PARALLEL QUERY enabled mode is not supported for the session. If FORCED, the session has been altered to force PARALLEL QUERY.

SQL> select PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select distinct sid from v$mystat)PDML_STATU PDDL_STATU PQ_STATUS
---------- ---------- ----------
DISABLED   ENABLED    FORCED