1Z0-117 by Oracle Valid Free Exam Practice Test

Question 1

View the code sequence:

Which three statements are true about the execution of this query?

A. The optimizer uses an index join as an access path. B. The optimizer performs bitmap conversion from the row IDs of the combined maps. C. The optimizer performs bitmap conversion to row IDs of the combined maps. D. The optimizer uses a B* tree access path for the EMPLOYEES table. E. The optimizer uses a bitmap access path for the EMPLOYEES table. F. The optimizer performs bitmap conversion from row IDs.

Correct Answer: A,B,E

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

Question 2

Examine the following anonymous PL/SQL code block of code:

Which two are true concerning the use of this code?

A. The user executing the anonymous PL/SQL code requires execute privilege on the DBMS_JOB package. B. ALTER SESSION ENABLE PARALLEL DML must be executed in the session prior to executing the anonymous PL/SQL code. C. Each chunk will be committed independently as soon as the task updating that chunk is finished. D. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package. E. All chunks are committed together once all tasks updating all chunks are finished. F. The user executing the anonymous PL/SQL code must have the CREATE JOB system privilege.

Correct Answer: D,F

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

Question 3

Examine the query:

The RESULT_CACHE_MODE parameter is set to MANUAL for the database.
Which two statements are true about the usage of the result cache?

A. The SQL runtime environment does check for the query result in the result cache because the RESULT_CACHE_MODE parameter is set to MANUAL. B. If the query result does not exist in the cache and the query is executed, the result is generated as output, and also sorted in the result cache. C. The SQL runtime environment checks whether the query result is cached in the result cache; if the result exists, the optimizer fetches the result from it. D. The SQL runtime environment checks for the query result in the result cache only when the query is executed for the second time.

Correct Answer: B,C

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

Question 4

The following parameter values are set for the instance:
OPTIMIZER_CAPTURE_SQL_BASELINE = FALSE OPTIMIZER_USESQL_PLAN_BASELINE = TRUE The SQL plan baseline for a SQL statement contains an accepted plan.
You want to add a new plan automatically as an accepted plan to the existing SQL plan baseline.
Examine the following tasks.
1.Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE parameter to TRUE.
2.Evolve the new plan.
3.Fix the existing accepted plan.
4.Manually load the new plan.
Identify the task(s) that must be performed to accomplish this.

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

Correct Answer: F

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

Question 5

Examine the utilization parameters for an instance:

You notice that despite having an index on the column used in the where clause, queries use full table scans with highly selective filters.
What are two possible reasons for the optimizer to use full table scans instead of index unique scans and index range scans?

A. The blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_READ_COUNT parameter. B. The OPTIMIZER_INDEX_COST_ADJ initialization parameter is set to 100. C. The number of leaf blocks for the indexes is high. D. The clustering factor for the indexes is high. E. The OPTIMIZER_MODE parameter is set to ALL_ROWS.

Correct Answer: A,B

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

Question 6

Which three statements are true about histograms?

A. They provide improved selectivity estimates in the presence of data skew, resulting in execution plans with uniform distribution. B. They help the optimizer to determine the fastest table join order. C. They can be used only with indexed columns. D. They capture the distribution of different values in an index for better selectivity estimates. E. They help the optimizer in deciding whether to use an index or a full table scan. F. They provide metadata about distribution of and occurrences of values in a table column.

Correct Answer: B,E,F

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

Question 7

A database instance is configured in the shared server mode and it supports multiple applications running on a middle tier. These applications connect to the database by using different services and tracing is enabled for the services. You want to view the detailed tracing setting for particular service.
What would you use to view the tracing information?

A. DBMS_MONITOR package B. DBA_ENABLED_TRACES view C. DBMS_SERVICE package D. Trcsess and tkprof

Correct Answer: B

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

Question 8

You have created some materialized views to improve the performance of several queries.
Which four must be true to enable sessions to benefit from improved response time made
possible by these materialized views?

A. All or part of the query results must be obtainable from one MVIEW. B. The MVIEWs must be enabled for query rewrite. C. Session users must have query rewrite privilege. D. Bitmap join indexes must exist on all the columns involved in the join operations. E. All or part of the query results must be obtainable from one or more MVIEWs. F. Query rewrite must be enabled for the sessions. G. Bitmap indexes must exist on all the columns involved in the join operations for the defining query of the MVIEWs.

Correct Answer: B,E,F,G

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

Oracle Database 11g Release 2: SQL Tuning - 1Z0-117 Exam Practice Test