You enabled auto degree of parallelism (DOP) for your instance.
Examine the query: 
Which two are true about the execution of this query?
A.
Dictionary DOP will be used, if present, on the tables referred in the query.
B.
DOP is calculated if the calculated DOP is 1.
C.
DOP is calculated automatically.
D.
Calculated DOP will always by 2 or more.
E.
The statement will execute with auto DOP only when PARALLEL_DEGREE_POLICY is set to
AUTO.
Explanation:
C: * You can use the PARALLEL hint to force parallelism. It takes an optional
parameter: the DOP at which the statement should run. In addition, theNO_PARALLEL hint
overrides a PARALLEL parameter in the DDL that created or altered the table.
The following example illustrates computing the DOP the statement should use:
SELECT /*+ parallel(auto) */ ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno;
Not A: to override the dictionary dop, we could use hints at object level
Not E: statement hints override the PARALLEL_DEGREE_POLICY.
Note:
* Automatic Parallel Degree PolicyWhen the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database
automatically decides if a statement should execute in parallel or not and what DOP it should use.
Oracle Database also determines if the statement can be executed immediately or if it is queued
until more system resources are available. Finally, Oracle Database decides if the statement can
take advantage of the aggregated cluster memory or not.
The following is a summary of parallel statement processing when parallel degree policy is set to
automatic.
For me answers are C and E
D is not correct. See the following example:
SQL> set linesize 180
SQL> set pagesize 100
SQL> set autotrace traceonly
SQL> select /*+ PARALLEL(AUTO) */ * FROM SH.SALES;
918843 ligne(s) sÚlectionnÚe(s).
Plan d’exÚcution
———————————————————-
Plan hash value: 1550251865
———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
———————————————————————————————
| 0 | SELECT STATEMENT | | 918K| 25M| 494 (3)| 00:00:06 | | |
| 1 | PARTITION RANGE ALL| | 918K| 25M| 494 (3)| 00:00:06 | 1 | 28 |
| 2 | TABLE ACCESS FULL | SALES | 918K| 25M| 494 (3)| 00:00:06 | 1 | 28 |
———————————————————————————————
Note
—–
– automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
Statistiques
———————————————————-
13661 recursive calls
0 db block gets
65730 consistent gets
1725 physical reads
0 redo size
33489036 bytes sent via SQL*Net to client
674235 bytes received via SQL*Net from client
61258 SQL*Net roundtrips to/from client
114 sorts (memory)
0 sorts (disk)
918843 rows processed
Errata. It seems that D is correct.
SQL> select /*+ parallel(auto) */ c.cust_first_name, c.cust_last_name, max(s.quantity_sold), avg(s.quantity_sold)
2 from sh.sales s, sh.customers c
3 where s.cust_id = c.cust_id
4 group by c.cust_first_name,c.cust_last_name;
3776 ligne(s) sÚlectionnÚe(s).
Plan d’exÚcution
———————————————————-
Plan hash value: 3916477200
————————————————————————————————————————————————–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
————————————————————————————————————————————————–
| 0 | SELECT STATEMENT | | 7059 | 441K| | 523 (6)| 00:00:07 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 7059 | 441K| | 523 (6)| 00:00:07 | | | Q1,03 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 7059 | 441K| 536K| 523 (6)| 00:00:07 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 7059 | 441K| | 521 (6)| 00:00:07 | | | Q1,03 | PCWP | |
| 5 | PX SEND HASH | :TQ10002 | 7059 | 441K| | 521 (6)| 00:00:07 | | | Q1,02 | P->P | HASH |
|* 6 | HASH JOIN | | 7059 | 441K| | 521 (6)| 00:00:07 | | | Q1,02 | PCWP | |
| 7 | PX RECEIVE | | 7059 | 303K| | 296 (10)| 00:00:04 | | | Q1,02 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10001 | 7059 | 303K| | 296 (10)| 00:00:04 | | | Q1,01 | P->P | BROADCAST |
| 9 | VIEW | VW_GBC_5 | 7059 | 303K| | 296 (10)| 00:00:04 | | | Q1,01 | PCWP | |
| 10 | HASH GROUP BY | | 7059 | 56472 | | 296 (10)| 00:00:04 | | | Q1,01 | PCWP | |
| 11 | PX RECEIVE | | 7059 | 56472 | | 296 (10)| 00:00:04 | | | Q1,01 | PCWP | |
| 12 | PX SEND HASH | :TQ10000 | 7059 | 56472 | | 296 (10)| 00:00:04 | | | Q1,00 | P->P | HASH |
| 13 | HASH GROUP BY | | 7059 | 56472 | | 296 (10)| 00:00:04 | | | Q1,00 | PCWP | |
| 14 | PX BLOCK ITERATOR | | 918K| 7178K| | 274 (3)| 00:00:04 | 1 | 28 | Q1,00 | PCWC | |
| 15 | TABLE ACCESS FULL| SALES | 918K| 7178K| | 274 (3)| 00:00:04 | 1 | 28 | Q1,00 | PCWP | |
| 16 | PX BLOCK ITERATOR | | 55500 | 1083K| | 225 (1)| 00:00:03 | | | Q1,02 | PCWC | |
| 17 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 1083K| | 225 (1)| 00:00:03 | | | Q1,02 | PCWP | |
————————————————————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
6 – access(“ITEM_1″=”C”.”CUST_ID”)
Note
—–
– automatic DOP: Computed Degree of Parallelism is 2
Statistiques
———————————————————-
724 recursive calls
4 db block gets
3468 consistent gets
3078 physical reads
0 redo size
102694 bytes sent via SQL*Net to client
3180 bytes received via SQL*Net from client
253 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
3776 rows processed
SQL>
D is wrong.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#BABHFDDH
“PARALLEL (AUTO): The database computes the degree of parallelism, which can be 1 or greater. If the computed degree of parallelism is 1, then the statement runs serially.”
@raka: Just because the Automatic DOP is calculated as 2 in your environment, it doesn’t mean that it will always be like that.
C is correct and E seems to be, as well.
Only C is correct.
E is not correct because in description of PARALLEL hint we can find “This hint overrides the value of the PARALLEL_DEGREE_POLICY initialization parameter.”.
I checked this and it is doesn’t matter if PARALLEL_DEGREE_POLICY is set to AUTO or MANUAL. Oracle will always calculate DOP automatically.
BC
In the exam, the answer b is:
b) if the calculated DOP is 1, then execute serially