Correct Answer: ACDG

C: PARALLEL_SERVERS_TARGET specifies the number of parallel server processes allowed to run parallel statements before statement queuing will be used. When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle will queue SQL statements that require parallel execution, if the necessary parallel server processes are not available. Statement queuing will begin once the number of parallel server processes active on the system is equal to or greater than PARALLEL_SERVER_TARGET.

By default, PARALLEL_SERVER_TARGET is set lower than the maximum number of parallel server processes allowed on the system (PARALLEL_MAX_SERVERS) to ensure each parallel statement will get all of the parallel server resources required and to prevent overloading the system with parallel server processes.

D: Note: ORA-12827: insufficient parallel query slaves available Cause: PARALLEL_MIN_PERCENT parameter was specified and fewer than minimum slaves were acquired Action: either re-execute query with lower PARALLEL_MIN_PERCENT or wait until some running queries are completed, thus freeing up slaves

A, G: PARALLEL_MIN_TIME_THRESHOLD specifies the minimum execution time a statement should have before the statement is considered for automatic degree of parallelism. By default, this is set to 30 seconds. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to AUTO or LIMITED.

Correct Answer: AE

A, not C, not D: Line 3, HASH GROUP BY, consumes line 6 (HASH JOIN BUFFERED).

E: Line 14, TABLE ACCESS FULL (Sales), is one of the two producers for line 6 (HASH JOIN).

Correct Answer: CE

A scan is slower than a row fetch (from hash value or rowid).

Correct Answer: ACF

C: The INDEX_COMBINE hint explicitly chooses a bitmap access path for the table.

F: If you specify indexspec, then the optimizer tries to use some Boolean combination of the specified indexes. Each parameter serves the same purpose as in "INDEX Hint". For example:

SELECT /*+ INDEX_COMBINE(e emp_manager_ix emp_department_ix) */ *

FROM employees e

WHERE manager_id = 108

OR department_id = 110;

Correct Answer: ACG

C: OPTIMIZER_FEATURES_ENABLE acts as an umbrella parameter for enabling a series of optimizer features based on an Oracle release number.

Note: The estimator determines the overall cost of a given execution plan. The estimator generates three different types of measures to achieve this goal:

*

Selectivity

This measure represents a fraction of rows from a row set. The selectivity is tied to a query predicate, such as last_name='Smith', or a combination of predicates.

*

Cardinality

This measure represents the number of rows in a row set. 1,

*

Cost

This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.

If statistics are available, then the estimator uses them to compute the measures. The statistics improve the degree of accuracy of the measures.