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

Auto DOP is enabled for your instance.
You execute the following statements:

Which three are true about the execution of the join?

• A. The hint operates at the level of each table accessed by the statement.
• B. The DEPARTMENTS table is accessed in parallel.
• C. The EMPLOYEES table is accessed in parallel.
• D. Dictionary DOP is used to calculate statements DOP.
• E. Hinted DOP is used to calculate statement DOP.

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

In Your Database, The Cursor_Shareing Parameter is set to EXACT. In the Employees table, the data is significantly skewed in the DEPTNO column. The value 10 is found in 97% of rows.
Examine the following command and out put.

Which three statements are correct?

• A. The instance collects statistics and based on the pattern of executions creates a histogram on the column containing the bind value.
• B. The value for the bind variable will considered by the optimizer to determine the execution plan.
• C. The same execution plan will always be used irrespective of the bind variable value.
• D. The DEPTNO column will become bind aware once histogram statistics are collected.
• E. Bind peeking will take place only for the first execution of the statement and subsequent execution will use the same plan.

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. Create histogram for the columns used frequently in the WHERE clause.
• B. Gather statistics with global statistics preference NO_VALIDATE to TRUE.
• C. Set the global statistics preference STALE_PERCENT to 0.
• D. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRU
• E. Use dynamically sampling hint for the queries on frequently updated tables.

You are administering a database that supports an OLTP application. To set statistics preferences, you issued the following command:
SQL > DBMS_STATS.SET_GLOBAL_PREFS ('ESTIMATE_PERCENT', '9');
What will be the effect of executing this procedure?

• A. New objects created will use global preference even if table preferences are specified.
• B. The automatic statistics gathering job running in the maintenance window will use global preferences unless table preferences for the same table exist.
• C. It will influence the gathering of statistics for a table based on the value specified for ESTIMATE_PERCENT provided on table preferences for the same table exist.
• D. It will influence dynamic sampling for a query to estimate the statistics based on ESTIMATE_PERCENT.