Online Access Free 1Z0-117 Exam Questions

Examine the parallelism parameters for your instance:

What are true about the execution of the query?

• A.It may execute serially.
• B.DOP for the statement is determined by the dictionary DOP of the accessed objects.
• C.It will execute in parallel only if the LINEITEM table has a dictionary DOP defined.
• D.DOP for the statement is calculated automatically.
• E.It will execute in parallel only if the estimated execution time is 10 or more seconds.
• F.It is generated to execute in parallel.

Examine the Exhibit to view the structure of an indexes for the SALES table.

The SALES table has 4594215 rows. The CUST_ID column has 2079 distinct values.
What would you do to influence the optimizer for better selectivity?

• A.Gather statistics for the indexes on the SALES table.
• B.Drop bitmap index and create balanced B*Tree index on the CUST_ID column.
• C.Create a height-balanced histogram for the CUST_ID column.
• D.Use the ALL_ROWS hint in the query.

See the table below:

All execution servers are currently available and the sessions use defaults for all parallel settings.
In which two cases will statements execute in parallel?

• A.When parallelism is defined at the statement level.
• B.When the degree of parallelism is explicitly defined for tables and indexes but only if estimated serial execution takes more than 10 seconds.
• C.When the degree of parallelism is explicitly defined in the data dictionary for tables and indexes accessed by a query.
• D.When parallel hints are used but only if estimated serial execution takes more than 10 seconds.
• E.Parallel DDL statements but only if estimated serial DDL execution time is greater than 10 seconds.

You recently gathered statistics for a table by using the following commands:

You noticed that the performance of queries has degraded after gathering statistics. You want to use the old statistics. The optimizer statistics retention period is default.
What must you do to use the old statistics?

• A.Use the flashback to bring back the statistics to the desired time.
• B.Set OPTIMIZER_USE_PENDING_STATISTICS to TRUE.
• C.Delete all the statistics collected after the desired time.
• D.Restore statistics from statistics history up to the desired time.

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