Free Oracle 1Z0-117 Exam Dumps Questions & Answers

Examine the query and its execution plan:

Which two statements are true regarding the execution plan?

• A.  An outer join returns NULL for the ORDERS table columns along with the CUSTOMERS table rows when it does not find any corresponding rows in the ORDER table.
• B.  The NESTED LOOP OUTER join is performed because the OPTIMZER_MODE parameter is set to ALL_ROWS.
• C.  The data is aggregated from the ORDERS table before joining to CUSTOMERS.
• D.  For every row of CUSTOMERS table, the row matching the join predicate from the ORDERS table are returned.

Which two tasks would you perform to achieve this?

• A.  Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to TRUE.
• B.  Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans.
• C.  Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.
• D.  Use the DBMS_SPM.ALTER_SQL_BASELINE function to accept new plans.
• E.  Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE.

You execute the following query:

Which statement is true about the usage of these hints in the query?

• A.  The optimizer evaluates the subquery as a first and then filters out rows.
• B.  The optimizer performs a join operation first and then filters out the rows.
• C.  The hint will have no effect because one of the join resultsets is an inline view.
• D.  The optimizer pushes the join predicate into the inline view.

Examine the Following Query and execution plan:

Which query transformation technique is used by the optimizer?

• A.  Subquery factoring
• B.  Subquery unnesting
• C.  Predicate pushing
• D.  Filter push down

Which two statements are true about the trcsess utility?

• A.  It produces multiple files for a service, which can be consolidated into one formatted file using the tkprof utility.
• B.  It merges multiple trace files from a particular session into one single trace file.
• C.  It merges multiple trace files and produces a formatted output file.
• D.  It merges files pertaining to a user session scattered across different processes in a shared server configuration.
• E.  It produces multiple files only for DBA sessions, which can be consolidated into one formatted file using the tkprof utility.