See the table below:
All parallel execution servers are available and sessions use default settings for parallelism.
Which three are true about parallel execution in your instance?
A. Parallel execution occurs when estimated serial execution time exceeds the minimum time threshold.
B. Parallel execution occurs for all DML statements.
C. Parallel execution occurs for those statements that access tables with dictionary DOP defined.
D. Parallel execution occurs for those statements that access tables with no dictionary DOP defined.
E. Parallel execution occurs for all DDL statements.
Correct Answer: ACD
Explanation/Reference:
A(not B, Not E):PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query’s estimated execution time is likely to run longer than the acceptable value (in seconds) for pARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at the cost of other non-parallelizable operations.The default of this parameter is 10 seconds.
C, D:In earlier versions of the Oracle Database, we had to determine the DOP more or less manually, either with a parallel hint or by setting a parallel degree with alter table. There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula CPU_COUNT * PARALLEL_THREADS_PER_CPU.
If there were insufficient parallel servers to satisfy the requested DOP, one of three things could occur:
/The SQL would be run at a reduced DOP (be downgraded)
/The SQL would run in serial mode (be serialized)
/If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate with "ORA-12827: insufficient parallel query slaves available".
Note:
*PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO
MANUAL - This is the default. Disables Auto DOP, statement queuing and in-memory parallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2).
*Oracle supports parallel processing for a wide range of operations, including queries, DDL and
• Queries that involve table or index range scans.
• Bulk insert, update or delete operations.
• Table and index creation.
*Oracle’s parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it.
*Three modes are available to request a DOP :
default
fixed adaptive
*The DOP is determined in the following priority order:
hint
session
table and limited by the Oracle Database Resource Manager (DBRM) settings.
Reference:AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
