Free Oracle 1Z0-117 Exam Dumps Questions & Answers

Which statement is true about an SQL plan baselines that are fixed?

• A.  New plans are added automatically by the optimizer to the baseline and are automatically evolved.
• B.  New plans can be added as fixed plans to the baseline by using the SQL Tuning Advisor to generate a SQL profile and by accepting the SQL profile.
• C.  New, better plans are added automatically as a fixed plan baseline.
• D.  New plan can be manually loaded to the baseline from the cursor cache or a SQL tuning set.

You ran a high load SQL statement that used an index through the SQL Tuning Advisor and accepted its recommendation for SQL profile creation. Subsequently you noticed that there has been a 2% growth in number of rows in the tables used by the SQL statement and database statistics have also been refreshed.
How does this impact the created SQL profile?

• A.  It remains valid and ensures that the optimizer always use the execution plan that was created before the changes happened.
• B.  It becomes invalid and no longer used the optimizer.
• C.  It becomes invalid and a new SQL profile is created for the statement by the auto tuning task.
• D.  It remains and allows the optimizer to pick a different plan required.

To accomplish this, identify the required steps in the correct order from the steps given below:
1.Use DBNMS_APPLICATION_INFO to set the name of the module
2.Use DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE to enable statistics gathering for the module.
3.Use DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE to enable tracing for the service
4.Use the trcsess utility to consolidate the trace files generated.
5.Use the tkprof utility to convert the trace files into formatted output.

• A.  3, 1, 2, 4, 5
• B.  1, 2, 4, 5
• C.  1, 3, 4, 5
• D.  2, 3, 1, 4, 5
• E.  1, 2, 3, 4, 5
• F.  2, 1, 4, 5

Examine the exhibit to view the query and its execution plan?

What two statements are true?

• A.  The HASH operation is the consumer of the HASH GROUP BY operation.
• B.  The HASH GROUP BY operation is consumer of the TABLE ACCESS FULL operation for the SALES table.
• C.  The SALES table scan is a producer for the HASH JOIN operation.
• D.  The HASH GROUP BY operation is the consumer of the HASH operation.
• E.  The HASH GROUP BY operation is the consumer of the TABLE ACCESS FULL operation for the CUSTOMER table.

You plan to bulk load data using INSERT /*+PARALLEL*/ INTO . . . . SELECT FROM statements.
Which four types of operations can execute in parallel on tables that have no bitmapped indexes or materialized views defined on term?

• A.  Direct path insert of a million rows into a partitioned, index-organized table containing one million rows.
• B.  Direct path insert of a million rows into a nonpartitioned, index-organized table containing one million rows.
• C.  Direct path insert of a million rows into a partitioned, index-organized table containing 10 million rows.
• D.  Direct path insert of a million rows into a nonpartitioned, heap-organized table containing 10 million rows.
• E.  Direct path insert of a million rows into a nonpartitioned, heap-organized table containing one million rows.