Online Access Free 1Z0-117 Practice Test

Identify two situations in which full table scans will be faster than index range scans.

• A.A query fetching less number of blocks than value specified by DB_FILE_MULTIBLOCK_READ_COUNT.
• B.A query on a table with sparsely populated table blocks.
• C.A query with a highly selective filter fetching less than 5 percent of the rows from a table.
• D.A highly selective query on a table having high clustering factor for an index.
• E.A query executing in parallel on a partitioned table with partitioned indexes.

Refer to the Exhibit.

Execution plan:

What must be the correct order of steps that the optimizer executes based on the ID column the execution plan?

• A.4, 5, 3, 7, 6
• B.3, 5, 4, 6, 7
• C.3, 5, 4, 7, 6
• D.3, 4, 5, 7, 6

Examine the initialization parameters for a database an OLTP overload.

What is the effect of changing the value of the parameter?

• A.It influenced the optimizer to use bitmap indexes as the estimated cost conversion from bimap is rowed us reduced.
• B.It influences the optimizer to always use the value of the parameter?
• C.It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
• D.It influences the optimizer to use indexes instead of full table scans as the estimated cost of the using index is reduced.

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.Dictionary DOP is used to calculate statements DOP.
• D.The EMPLOYEES table is accessed in parallel.
• E.Hinted DOP is used to calculate statement DOP.

You are administering a database supporting an OLTP workload where the users perform frequent queries for fetching a new rows as possible, involving join operations on recently
inserted data. In addition at night, a few DSS queries are also performed. Examine the initialization parameters for the instance:

Which two options would you use for the optimizer?

• A.Add the hint ALL_ROWS in the DOS queries.
• B.Add a hint INDEX_COMBINE in all DSS queries.
• C.Set the OPTIMIZER_INDEX_CACHING initialization parameter to 0.
• D.Set the OPTIMIZER_INDEX_COST_ADJ initialization parameter to 100.
• E.Set the OPTIMIZER_MODE initialization parameter to FIRST_ROWS_n.