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?

Question

Exhibit

Options

• AParallel execution occurs when estimated serial execution time exceeds the minimum time threshold.
• BParallel execution occurs for all DML statements.
• CParallel execution occurs for those statements that access tables with dictionary DOP defined.
• DParallel execution occurs for those statements that access tables with no dictionary DOP defined.
• EParallel execution occurs for all DDL statements.

• A
  69% (18)
• B
  12% (3)
• E
  19% (5)

Explanation

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 / 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". * 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 : * The DOP is determined in the following priority order: and limited by the Oracle Database Resource Manager (DBRM) settings. Reference: AUTOMATIC DEGREE OF PARALLELISM (DOP) IN ORACLE 11G R2

No community discussion yet for this question.