Online Access Free 1Z0-117 Practice Test

Examine the Following Query and execution plan:

Which query transformation technique is used by the optimizer?

• A.Subquery factoring
• B.Filter push down
• C.Predicate pushing
• D.Subquery unnesting

You created a SQL Tuning Set (STS) containing resource-intensive SQL statements. You plan to run the SQL Tuning Advisor.
Which two types of recommendations can be provided by the SQL Tuning Advisor?

• A.Creating a materialized view to benefit from query rewrite for the entire workload
• B.Creating a partition table to benefit from partition pruning for each statement
• C.Gathering missing or stale statistics at the schema level for the entire workload
• D.Semantic restructuring for each SQL statement
• E.Gathering missing or stale statistics for objects used by the statements.

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

• A.Serial queries that top the AWR Top SQL in the past week only and whose poor performance can be traced to concurrency issues.
• B.Serial queries that have been tuned within the last 30days and have been SQL profiled by the SQL tuning Advisor.
• C.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
• D.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.
• E.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.

You execute the following query for the first time:

Examine the SQL statement processing steps:
1.The value of the variable SAL is obtained to run the query.
2.The syntax of the query is checked
3.A parse tree for the query is generated
4.Semantics for the query are checked
5.The required rows are fetched
6.The SQL is executed to produce the required result.
Which is the correct order of execution of the above query?

• A.2, 3, 1, 4, 6, 5
• B.1, 4, 2, 3, 6, 5
• C.2, 4, 1, 3, 6, 5
• D.1, 2 3, 4, 5, 6

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

EXAMINE the SQL statement and its execution plan:

Which two statements are correct regarding the execution plan?

• A.Step 2 is performing nested operation on JOB_ID column of the JOBS table, which is the driven table and the EMPLOYEES table is the driven table.
• B.Step 1 is performing nested loop operation on the DEPARTMENT_ID column of the DEPARTMENTS table, which is the driven table and results returned by step 2 in the driving resultset.
• C.In step 2 for every row returned by the JOBS table matching rows from the EMPLOYEES table are accessed.
• D.The performance of the query can be improved by creating bitmapped index on the DEPARTMENT_ID column of the EMPLOYEES table.
• E.The performance of the query can be improved by creating bitmap index on the JOB_ID column of the EMPLOYEES table.