Free Oracle 1Z0-117 Exam Dumps Questions & Answers

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 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.
• C.  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.
• D.  The query fetches rows from CUSTOMERS and ORDERS table simultaneously, and filters the rows that satisfy the conditions from the resultset.

View the exhibit and examine the plans in the SQL baseline for a given statement. Which interpretation is correct?

• A.  Plan SYS_SQL_bbedc741a57b5fc2 can be used by the optimizer if the cost of the query is less than plan SYS_SQL_PLAN_bbedc741f554c408.
• B.  A new plan cannot be evolved because SYS_SQL_bbedc41f554c408 is accepted.
• C.  Plan SYS_SQL_PLAN_bbdc741f554c408 will always be used by the optimizer for the query.
• D.  A new plan must be evolved using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function before it can be used.
• E.  Plan SYS_SQL_PLAN_bbedc741f554c408 will not be used until it is fixed by using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function.

An application user complains about statement execution taking longer than usual. You find that the query uses a bind variable in the WHERE clause as follows:

You want to view the execution plan of the query that takes into account the value in the bind variable PCAT.
Which two methods can you use to view the required execution plan?

• A.  Execute the statement with different bind values and set AUTOTRACE enabled for session.
• B.  Use the DBMS_XPLAN.DISPLAY function to view the execution plan.
• C.  View the execution plan for the statement from V$SQL_PLAN.
• D.  Identify the SQL_ID for the statement and fetch the execution plan PLAN_TABLE.
• E.  Identify the SQL_ID for the statementsand use DBMS_XPLAN.DISPLAY_CURSOR for that SQL_ID to view the execution plan.

Examine the parallelism parameters for your instance:

What are true about the execution of the query?

• A.  It will execute in parallel only if the LINEITEM table has a dictionary DOP defined.
• B.  DOP for the statement is calculated automatically.
• C.  It will execute in parallel only if the estimated execution time is 10 or more seconds.
• D.  It is generated to execute in parallel.
• E.  DOP for the statement is determined by the dictionary DOP of the accessed objects.
• F.  It may execute serially.

Examine the parallelism parameters for you instance. Now examine the DSS_PLAN with parallel statement directives:


Which two are true about the DSS_PLAN resource manager plan?

• A.  URGENT_GROUP sessions will not be queued if 64 parallel execution servers are busy because their PARALLEL_TARGET_PERCENTAGE is not specified.
• B.  An ETL_GRP sessions will be switched to URGENT_GROUPS if the session requests more than eight parallel executions servers.
• C.  OTHER_GROUPS sessions are queued for maximum of six minutes.
• D.  URGENT_GROUPS sessions will always be dequeued before sessions from other groups.
• E.  ETL_GROUP sessions can collectively consume 64 parallel execution servers before queuing starts for this consumer group.