READ Free Dumps For Oracle- 1z0-062
| Question ID 21751 | Examine the parameter for your database instance:
You generated the execution plan for the following query in the plan table and noticed that the nested loop join was done. After actual execution of the query, you
notice that the hash join was done in the execution plan:
Identify the reason why the optimizer chose different execution plans.
|
| Option A | The optimizer used a dynamic plan for the query.
|
| Option B | The optimizer chose different plans because automatic dynamic sampling was enabled.
|
| Option C | The optimizer used re-optimization cardinality feedback for the query.
|
| Option D | The optimizer chose different plan because extended statistics were created for the columns used.
|
| Correct Answer | B |
Explanation Explanation: * optimizer_dynamic_sampling OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics. Range of values0 to 11
| Question ID 21752 | Which three statements are true about adaptive SQL plan management?
|
| Option A | It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.
|
| Option B | The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.
|
| Option C | It adds new, bettor plans automatically as fixed plans to the baseline.
|
| Option D | The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.
|
| Option E | The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.
|
| Correct Answer | A,D,E |
Explanation Explanation: With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time. Note: * The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the plan. * Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement. Reference: SQL Plan Management with Oracle Database 12c