Free Oracle 1Z0-117 Practice Test & Real Exam Questions

Exam Code/Number: 1Z0-117
Exam Name/Title: Oracle Database 11g Release 2: SQL Tuning Exam
Certification Provider: Oracle
Corresponding Certification: Oracle Database

Exam Questions: 125
Updated On: Aug 08, 2026

Question #1

You execute the following query:

Which statement is true about the usage of these hints in the query?

A. The optimizer pushes the join predicate into the inline view.

B. The optimizer performs a join operation first and then filters out the rows.

C. The hint will have no effect because one of the join resultsets is an inline view.

D. The optimizer evaluates the subquery as a first and then filters out rows.

Discussion 0

Correct Answer: A Vote an answer

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

Question #2

Which three options are true about MVIEWs?

A. The defining query of an MVIEW may be based on non_partitioned table.

B. The defining query of an MVIEWs may be based on a populated table.

C. Queries that are rewritten to an MVIEW will never obtain results from the result cache.

D. All MVIEWs may be configured to support "refresh on commit"

E. All MVIEWS may be configured to support "refresh on demand".

Discussion 0

Correct Answer: B,C,E Vote an answer

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

Question #3

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, better plans are added automatically as a fixed plan baseline.

C. New plan can be manually loaded to the baseline from the cursor cache or a SQL tuning set.

D. 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.

Discussion 0

Correct Answer: D Vote an answer

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

Question #4

---
Your database supports a workload consisting of three categories of SQL statements: Statements that should execute in less than one minute Statement that may execute for up to 15 minutes Statements that may be executed for more than 15 minutes You set PARALLEL_DEGREE_POLICY to Auto.
You plan to prioritize queued statements by using the Database Resource manager.
Which two are true about parallelism prioritization by a consumer group?

A. PARALLEL_TARGET_PERCENTAGE us used to prioritize a consumer group's use of the overall SPAN CLASS = 'OracleCode'> PARALLEL_MAX_SERVERS.

B. PARALLEL_TARGET_PERCENTAGE is used to prioritize a consumer group's use of the overall PARALLEL_SERVER_TARGET.

C. Separate queue timeout using PARALLEL_QUEUE_TIMEOUT require the use of management attributes (MGMT_P1, MGMT_P2 etc . . . groups)

D. Queuing is done for a consumer group exceeding its percentage, even if the number of busy PX servers in the instance has not reached PARALLEL_SERVERS_TARGET.

E. Having separate queues for consumer groups requires the use of management attributes (MGMT_P1, MGMT_P2 etc. . . )

Discussion 0

Correct Answer: B,E Vote an answer

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

Question #5

You instance has these parameter settings:

Which three statements are true about these settings if no hints are used in a SQL statement?

A. A statement with a computed degree of parallelism of 20 will be queued if the number of available parallel execution processes is less 5.

B. A statement with a computed degree of parallelism greater than 8 will be queued for a maximum of 10 seconds.

C. A statement estimated for more than 10 seconds always has its degree of parallelism computed automatically.

D. A statement with any computed degree of parallelism will be queued if the number of busy parallel execution processes exceeds 64.

E. A statement that executes for more than 10 seconds always has its degree of parallelism computed automatically.

F. A statement with a computed degree of parallelism greater than 8 will raise an error.

Discussion 0

Correct Answer: A,D,E Vote an answer

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

Question #6

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. DBMS_SERVICE package

C. Trcsess and tkprof

D. DBA_ENABLED_TRACES view

Discussion 0

Correct Answer: D Vote an answer

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

Question #7

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

Which two are true concerning the use of this code?

A. ALTER SESSION ENABLE PARALLEL DML must be executed in the session prior to executing the anonymous PL/SQL code.

B. The user executing the anonymous PL/SQL code requires execute privilege on the DBMS_JOB package.

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 must have the CREATE JOB system privilege.

E. The user executing the anonymous PL/SQL code requires privilege on the DBMS_SCHEDULER package.

F. All chunks are committed together once all tasks updating all chunks are finished.

Discussion 0

Correct Answer: D,E Vote an answer

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

Question #8

Examine the following command:

Which query transformation technique is used by the optimizer in this case?

A. View merging

B. Filter push-down

C. Predicate move-around

D. Predicate pushing

Discussion 0

Correct Answer: D Vote an answer

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

Question #9

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 highly selective filters is applied on an indexed column of a table with sparsely populated blocks.

B. When a highly selective filter is applied on the indexed column and the index has very low value for clustering factor.

C. When the statistics for the table are not current.

D. When the rows are filtered with an IS NULL operator on the column with a unique key defined

E. When the histogram statistics are not collected for the columns used in where clause.

Discussion 0

Correct Answer: A,C,D Vote an answer

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

Download Free Oracle 1Z0-117 Demo

Simply submit your e-mail address below to get started with our free demo of your Oracle 1Z0-117 exam.