Free Oracle 1Z0-117 Exam Dumps Questions & Answers

You are administering database that supports an OLTP workloads. Most of the queries use an index range scan or index unique scan as access methods.
Which three scenarios can prevent the index access being used by the queries?

• A.  When the histogram statistics are not collected for the columns used in where clause.
• B.  When highly selective filters is applied on an indexed column of a table with sparsely populated blocks.
• C.  When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.
• D.  When the rows are filtered with an IS NULL operator on the column with a unique key defined
• E.  When the statistics for the table are not current.

How can you reduce fragmentation of an index without affecting the current transactions that are using the index?

• A.  Use the ANALYZE INDEX . . . command
• B.  Use the ALTER INDEX . . . VALIDATE STRUCTURE command
• C.  Use the ALTER INDEX . . . DEALLOCATE UNUSED command
• D.  Us the ALTER INDEX . . . REBUILD ONLINE command

You want to run SQL Tuning Advisor statements that are not captured by ADDM, AWR, and are not in the library cache.
What is the prerequisite?

• A.  Create a SQL Tuning Set (STS) containing the SQL statements
• B.  Create a SQL plan baseline for each query
• C.  Gather statistics for objects used in the application
• D.  Enable SQL plan management

Which type of SQL statement would be selected for tuning by the automatic SQL framework?

• A.  Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.
• B.  Serial and parallel queries that are among the costliest in any or all of the four categories: the past week, and day in the past week, any hour in the past week, or a single response, and that can benefit from access method changes.
• C.  Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.
• D.  Serial and parallel queries that top the AWR Top SQL in the past week only and have been SQL profiled by the SQL Tuning Advisor.
• E.  Serial queries that are among the costliest in any or all of the four categories: the past week, any day in the past week, any hour in the past week, or single response, and have the potential for improvement

Examine the exhibit.

Which is true based on the information obtainable from the execution plan?

• A.  A full partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.
• B.  A full table scan on the DEPARTMENTS table performed serially by the query coordinator.
• C.  A full table scan on the EMPLOYEES table is done in parallel.
• D.  A partial partition-wise join performed between the EMPLOYEES and DEPARTMENTS tables.
• E.  A full table scan on the DEPARTMENTS table is performed serially by a single parallel execution server process.