Oracle Database 11g Release 2: SQL Tuning - 1Z0-117 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

Examine Exhibit 1 to view the query and its execution plan.

Examine Exhibit 2 to view the structure and indexes for the EMPLOYEES and DEPARTMENTS tables.

Examine Exhibit 3 to view the initialization parameters for the instance.

Why is sort-merge join chosen as the access method?

A. Because of the LIKE operator used in the query to filter out records B. Because the data is not sorted in the LAST_NAME column of the EMPLOYEES table C. Because the OPTIMIZER_MODE parameter is set to ALL_ROWS. D. Because of an inequality condition.

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 2

Examine the statements being executed for the first time:

Steps followed by a SQL statement during parsing:
1.Search for a similar statement in the shared pool.
2.Search for an identical statement in the shared pool.
3.Search the SQL area of identical statement already in the shared pool.
4.Proceed through the remaining steps of the parse phase to ensure that the execution plan of the existing statements is applicable to the view statement.
5.Perform hard parsing.
6.Share the SQL area of the similar statement already in the shared pool.
Identify the required steps in the correct sequence used by the third query.

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

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 3

Examine the Exhibit 1 to view the structure of and indexes for EMPLOYEES and DEPARTMENTS tables.

Which three statements are true regarding the execution plan?

A. The optimizer chooses sort-merge join as a join method because an equality predicate is used for joining the tables. B. The in-line query in the select list is processed as a view and then joined. C. The view operator collects all rows from a query block before they can be processed but higher operations in the plan. D. The optimizer pushes the equality predicate into the view to satisfy the join condition. E. The optimizer chooses sort-merge join because sorting is required for the join equality predicate.

Discussion 0

Correct Answer: B,C,D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 4

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_BASELINE function to accept new plans. C. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to fix the plans. D. Set the OPTIMIZER_USE_SQL_PLAN_BASELINES initialization parameter to TRUE. E. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to fix the new plans.

Discussion 0

Correct Answer: D,E Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 5

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_SPM.ALTER_SQL_PLAN_BASELINE function. B. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function and run the DBMS_STATS to manually refresh stale statistics. C. Use the DBMS_SPM.LOAD_PLANS_FROM_SQLSET function. D. Use the DBMS_SQLTUNE.CREATE_SQL_PLAN_BASELINE function.

Discussion 0

Correct Answer: B,C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 6

You are administering a database that supports a DSS workload, where in an application a set of queries use the query rewrite on materialized views. You notice that these queries are performing poorly.
Which two actions can you make to improve the performance of theses queries?

A. Create an STS for all the queries in the application and use the SQL Tuning Advisor to generate recommendations. B. Create an STS for all the queries in the application and use the SQL Access Advisor to generate a recommendation for optimizing materialized views for maximum query rewrite usage and fast refresh. C. Create an STS for all the queries and use SQL performance Analyzer to generate recommendations for determining the regressed SQL statements. D. USE DBMS_ADVISOR.QUICK_TUNE to analyze the query rewrite usage of materialized views for the entire workload. E. Use DBMS_MVIEW.EXPLAIN_REWRITE to analyze whether the queries are rewritten.

Discussion 0

Correct Answer: A,B Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 7

You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently during day time and you notice a performance degradation for queries using those tables due to stale statistics.
Which two options might help to avoid the performance degradation of the queries?

A. Gather statistics with global statistics preference NO_VALIDATE to TRUE. B. Use dynamically sampling hint for the queries on frequently updated tables. C. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRU D. Create histogram for the columns used frequently in the WHERE clause. E. Set the global statistics preference STALE_PERCENT to 0.

Discussion 0

Correct Answer: B,D Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 8

You need to migrate database from oracle Database 10g to 11g. You want the SQL workload to start the 10g plans in the 11g database instance and evolve better plans.
Examine the following steps:
1.Capture the pre-Oracle Database 11g plans in a SQL Tuning Set (STS)
2.Export the STS from the 10g system.
3.Import the STS into Oracle Database 11g.
4.Set the OPTIMIZER_FEATURES_ENABLE parameter to 10.2.0.
5.Run SQL Performance Analyzer for the STS.
6.Set the OPTIMIZER_FEATURES_ENABLE parameter to 11.2.0.
7.Rerun the SQL Performance Analyzer for the STS.
8.Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE to TRUE.
9.Use DBMS_SPM.EVOLVE_SQL_BASELINE function to evolve the plans.
10.
Set the OPTIMIZER_USE_SQL_PLAN_BASELINE to TRUE.
Identify the required steps in the correct order.

A. 1, 2, 3, 4, 5, 6, 7, B. 1, 2, 3, 6, 9, 5 C. 1, 2, 3, 4, 8, 10 D. 1, 2, 3, 5, 9, 10 E. 4, 8, 10

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).

Question 9

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

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

Discussion 0

Correct Answer: A Vote an answer

Explanation: Only visible for FreeCram members. You can sign-up / login (it's free).