Oracle 1Z0-117 : Oracle Database 11g Release 2: SQL Tuning Exam

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

• A. The shared pool can be used for parallel execution messages buffers.
• B. Each parallel execution process has an additional connection to the parallel execution coordinator.
• C. Communication between parallel execution processes is never required if a query uses full partition-wise joins.
• D. The buffer cache can be used for parallel execution message buffers.
• E. The number of logical pathways between parallel execution producers and consumers depends on the degree parallelism.
• F. The large pool can be used for parallel execution messages buffers.

Examine the following anonymous PL/SQL code block of code:

Which two are true concerning the use of this code?

• A. Each chunk will be committed independently as soon as the task updating that chunk is finished.
• B. The user executing the anonymous PL/SQL code must have the CREATE JOB system privilege.
• C. The user executing the anonymous PL/SQL code requires execute privilege on the DBMS_JOB package.
• D. ALTER SESSION ENABLE PARALLEL DML must be executed in the session prior to executing the anonymous PL/SQL code.
• E. All chunks are committed together once all tasks updating all chunks are finished.
• F. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package.

How can you reduce fragmentation of an index without affecting the current transactions that are using the index?

• A. Use the ALTER INDEX . . . DEALLOCATE UNUSED command
• B. Us the ALTER INDEX . . . REBUILD ONLINE command
• C. Use the ALTER INDEX . . . VALIDATE STRUCTURE command
• D. Use the ANALYZE INDEX . . . command

Exhibit

Examine the following SQL statement:

Examine the exhibit to view the execution plan. Which statement is true about the execution plan?

• A. The rows with the ID 3 and 4 are executed simultaneously.
• B. The row with the ID 0 is the first step executed in the execution plan.
• C. The row with the ID 3 is the first step executed in the execution plan.
• D. The EXPLAIN PLAN generates the execution plan and stores it in c$SQL_PLAN after executing the query. Subsequent executions will use the same plan.
• E. The EXPLAIN PLAN generates the execution plan and stores it in PLAN_TABLE without executing the query. Subsequent executions will always use the same plan.

View the exhibit and examine the findings provided by the SQL Tuning Advisor for SELECT Statement.

A SWL plan baseline already exists for the execution plan.
What two methods can you use to ensure that an alternate plan becomes an accepted plan?

• A. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.
• B. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function.
• C. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function.
• D. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics.