Things worth to mention and remember (III) - Parallel Execution Control 3

Continuing from the previous part of this series I'll cover in this post some further basics about parallel execution control:

- Keep in mind that there are two classes of parallel hints: PARALLEL and PARALLEL_INDEX. One is about the costing of parallel full table / index fast full scans, the other one about costing (driving) parallel index scans, which are only possible with partitioned indexes (PX PARTITION granule vs. PX BLOCK granule)

- The same applies to the opposite, NO_PARALLEL (or NOPARALLEL in older releases) and NO_PARALLEL_INDEX. It is in particular important to realize that specifying a NO_PARALLEL hint only tells the optimizer to not evaluate a parallel full table scan / index fast full scan, however it might still evaluate a parallel index scan if feasible and therefore still might go parallel. This is particularly important if you are under the impression that using the NO_PARALLEL hint will ensure that no parallel slaves will be used by the statement at execution time. This is not entirely correct - it still might go for a parallel index scan. You would need to use different means, either use a NO_PARALLEL_INDEX hint in addition to NO_PARALLEL, use the OPT_PARAM hint to set the optimizer parameter PARALLEL_EXECUTION_ENABLED to FALSE at statement level or disabling PARALLEL QUERY on session level.

- Furthermore note that the PARALLEL and PARALLEL_INDEX hints merely tell the optimizer to evaluate the costing of a plan with the determined parallel degree. If the optimizer however finds a serial plan with a lower cost, it will prefer that serial execution plan despite the usage of the parallel hints. This even applies to a session "forced" to use parallel query via ALTER SESSION FORCE PARALLEL QUERY.

- Remember that with DML / CTAS DDL there are actually (at least) two potential parallel parts involved: The Create Table/DML part and the query part (except for a single row INSERT INTO...VALUES DML). Both parts independently from each other can be performed in parallel or serial, so you could end up with:

- Serial DDL/DML + serial query
- Serial DDL/DML + parallel query
- Parallel DDL/DML + serial query
- Parallel DDL/DML + parallel query

Whether some combinations make sense or not is a different question, however from a technical point of view all of them are possible.

You therefore need to carefully check which part you want to execute in parallel and control it accordingly (and remember the fact that parallel DML needs to be explicitly enabled in the session). Check the (actual) execution plan to ensure you get the desired parallel execution.