Which three options are true about parallel queries when PARALLEL_DEGREE_POLICY is set to
MANUAL and the session is using the default settings for parallel query, DDL, and DML?
A.
A subquery in a parallel DML is parallelized only if it includes a parallel hint.
B.
The number of parallel execution servers requested for a cursor is based on the greatest
degree of parallelism associated with any object accessed by the cursor.
C.
A SELECT statement can be executed in parallel only if no scalar subqueries are contained in
the SELECT list.
D.
In a CREATE TABLE . . . AS SELECT (CTAS) statement, SELECT is parallelized only if create
TABLE is parallelized.
E.
In an INSERT INTO . . . SELECT FROM statement, INSERT is parallelized if select is
parallelized.
F.
Single row inserts are never executed is parallel.
Explanation:
* Decision to Parallelize
A SELECT statement can be parallelized only if the following conditions are satisfied:/ The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the
schema objects referred to in the query have a PARALLEL declaration associated with them.
/ At least one of the tables specified in the query requires one of the following:
A full table scan
An index range scan spanning multiple partitions
/ (C) No scalar subqueries are in the SELECT list.
* By default, the system only uses parallel execution when a parallel degree has been explicitly set
on an object or if a parallel hint is specified in the SQL statement.
* CREATE TABLE … AS SELECT in Parallel
Parallel execution lets you parallelize the query and create operations of creating a table as a
subquery from another table or set of tables. This can be extremely useful in the creation of
summary or rollup tables.
Clustered tables cannot be created and populated in parallel.
* PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism,
statement queuing, and in-memory parallel execution will be enabled.
MANUAL
Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release
2 (11.2). This is the default.
Incorrect:
A:
* For parallel DML (INSERT, UPDATE, MERGE, and DELETE), the reference object that
determines the DOP (degree of parallelism) is the table being modified by and insert, update, or
delete operation. Parallel DML also adds some limits to the DOP to prevent deadlock. If the
parallel DML statement includes a subquery, the subquery’s DOP is the same as the DML
operation.
* For parallel DDL, the reference object that determines the DOP is the table, index, or partition
being created, rebuilt, split, or moved. If the parallel DDL statement includes a subquery, thesubquery’s DOP is the same as the DDL operation.
D: The CREATE TABLE … AS SELECT statement contains two parts: a CREATE part (DDL) and
a SELECT part (query). Oracle Database can parallelize both parts of the statement.
The query part of a CREATE TABLE … AS SELECT statement can be parallelized only if the
following conditions are satisfied:
Reference: Oracle Database VLDB and Partitioning Guide, Using Parallel Execution
B, C, F
A – incorrect
When parallel DML is disabled, no DML is executed in parallel even if the PARALLEL hint is used.
B – correct
The query uses the maximum DOP taken from all of the table declarations involved in the query and all of the potential indexes that are candidates to satisfy the query (the reference objects). That is, the table or index that has the greatest DOP determines the query’s DOP maximum query directive.
C – correct
A SELECT statement can be executed in parallel only if the following conditions are satisfied:
*The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
*At least one table specified in the query requires one of the following:
-A full table scan
-An index range scan spanning multiple partitions
*No scalar subqueries are in the SELECT list.
D – incorrect
The query part of a CREATE TABLE … AS SELECT statement can be parallelized only if the following conditions are satisfied:
*The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the CREATE part of the statement has a PARALLEL clause specification or the schema objects referred to in the query have a PARALLEL declaration associated with them.
*At least one table specified in the query requires either a full table scan or an index range scan spanning multiple partitions.
E – incorrect
The decision to parallelize the INSERT operation is independent of the SELECT operation, and vice versa.
F – correct
The INSERT VALUES statement is never executed in parallel.
For me also B,C,F are correct.
But explanation why not A is a little different. Question is about subqery, not about DML opartion. Subquery in DML operations can be executed in paralle and it can be achieved by using hint PARALLEL or it will be calculated based on parallel definition of object. It means that we have here two situations in which parallel execution for subquery can be used. In answer A we have statement that it is “ONLY if it includes a parallel hint” and this is not correct, because it is only half of truth.