Oracle Oracle Certifications 1Z0-117 Questions & Answers

• Question 121:

  You have enabled DML by issuing: ALTER session ENABLE PARALLEL DML;

  The PARELLEL_DEGREE_POLICY initialization parameter is set to AUTO.

  Which two options true about DML statements for which parallel execution is requested?

  A. Statements for which PDML is requested will execute serially estimated time is less than the time specified by the PARALLEL_MIN_THRESHOLD parameter.

  B. Statements for which PDML is requested will be queued if the number of busy parallel execution servers greater than PARALLEL_MIN_SERVERS parameter.

  C. Statements for which PDML is requested will always execute in parallel if estimated execution in parallel if estimated execution time is greater than the time specified by the PARELLEL_MIN_TIME_THRESHOLD parameter.

  D. Statements for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_SERVERS_TARGET parameter.

  E. Statement for which PDML is requested will be queued if the number of busy parallel execution servers is greater than PARELLEL_DEGREE_LIMIT parameter.

  Correct Answer: CD

  C: 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.

  D: 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.

  Note:

  *

  PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.

  AUTO

  Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.

  *

  PARALLEL_MIN_SERVERS specifies the minimum number of parallel execution processes for the instance. This value is the number of parallel execution processes Oracle creates when the instance is started.

  Reference: Oracle Database Reference; PARALLEL_SERVERS_TARGET

• Question 122:

  Which four statements are correct about communication between parallel execution process?

  A. The number of logical pathways between parallel execution producers and consumers depends on the degree parallelism.

  B. The shared pool can be used for parallel execution messages buffers.

  C. The large pool can be used for parallel execution messages buffers.

  D. The buffer cache can be used for parallel execution message buffers.

  E. Communication between parallel execution processes is never required if a query uses full partition-wise joins.

  F. Each parallel execution process has an additional connection to the parallel execution coordinator.

  Correct Answer: ABEF

  A: Note that the degree of parallelism applies directly only to intra-operation parallelism. If inter-operation parallelism is possible, the total number of parallel execution servers for a statement can be twice the specified degree of parallelism. No more than two sets of parallel execution servers can run simultaneously. Each set of parallel execution servers may process multiple operations. Only two sets of parallel execution servers need to be active to guarantee optimal inter-operation parallelism.

  B: By default, Oracle allocates parallel execution buffers from the shared pool.

  F: When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation. If necessary, Oracle can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout job execution, then become available for other operations. After the statement has been processed completely, the parallel execution servers return to the pool.

  Reference: Oracle Database Data Warehousing Guide, Using Parallel Execution

• Question 123:

  

  View the exhibit and examine the query and its execution plan from the PLAN_TABLE. Which statement is true about the execution?

  A. The row with the ID column having the value 0 is the first step execution plan.

  B. Rows are fetched from the indexes on the PRODUCTS table and from the SALES table using full table scan simultaneously, and then hashed into memory.

  C. Rows are fetched from the SALES table, and then a hash join operator joins with rows fetched from indexes on the PRODUCTS table.

  D. All the partitions of the SALES table are read in parallel.

  Correct Answer: C

• Question 124:

  Examine the query and its execution plan:

  

  Which statement is true regarding the execution plan?

  A. This query first fetches rows from the CUSTOMERS table that satisfy the conditions, and then the join return NULL from the CUSTOMER_ID column when it does not find any corresponding rows in the ORDERS table.

  B. The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset.

  C. The query first fetches rows from the ORDERS table that satisfy the conditions, and then the join returns NULL form CUSTOMER_ID column when it does not find any corresponding rows in the CUSTOMERS table.

  D. The query first joins rows from the CUSTOMERS and ORDERS tables and returns NULL for the ORDERS table columns when it does not find any corresponding rows in the ORDERS table, and then fetches the rows that satisfy the conditions from the result set.

  Correct Answer: A

• Question 125:

  Which three statements are true about histograms?

  A. They capture the distribution of different values in an index for better selectivity estimates.

  B. They can be used only with indexed columns.

  C. They provide metadata about distribution of and occurrences of values in a table column.

  D. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution.

  E. They help the optimizer in deciding whether to use an index or a full table scan.

  F. They help the optimizer to determine the fastest table join order.

  Correct Answer: CEF

  C: A histogram is a frequency distribution (metadata) that describes the distribution of data values within a table.

  E: It's well established that histograms are very useful for helping the optimizer choose between a full-scan and and index-scan.

  F: Histograms may help the Oracle optimizer in deciding whether to use an index vs. a full-table scan (where index values are skewed) or help the optimizer determine the fastest table join order. For determining the best table join order, the WHERE clause of the query can be inspected along with the execution plan for the original query. If the cardinality of the table is too-high, then histograms on the most selective column in the WHERE clause will tip-off the optimizer and change the table join order. Note:

  * The Oracle Query Optimizer uses histograms to predict better query plans. The ANALYZE command or DBMS_STATS package can be used to compute these histograms.

  Incorrect:

  B: Histograms are NOT just for indexed columns.

  Adding a histogram to an un-indexed column that is used in a where clause can improve performance.

  D: Histograms Opportunities Any column used in a where clause with skewed data Columns that are not queried all the time Reduced overhead for insert, update, delete