Which three statements are true about adaptive SQL plan management?
Correct Answer: A,D,E
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. References:
Question 92
You Execute the Following command to create a password file in the database server: $ orapwd file = '+DATA/PROD/orapwprod entries = 5 ignorecase = N format = 12' Which two statements are true about the password file? (Choose two.)
Correct Answer: C,E
Explanation/Reference: Explanation:
Question 93
Which component resides in the System Global Area (SGA) of a database instance only in shared server connections?
Correct Answer: B
Question 94
You have altered a non-unique index to be invisible to determine if queries execute within an acceptable response time without using this index. Which two are possible if table updates are performed which affect the invisible index columns? (Choose two.)
Correct Answer: A,E
Unlike unusable indexes, an invisible index is maintained during DML statements. Note: * Oracle 11g allows indexes to be marked as invisible. Invisible indexes are maintained like any other index, but they are ignored by the optimizer unless the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE at the instance or session level. Indexes can be created as invisible by using the INVISIBLE keyword, and their visibility can be toggled using the ALTER INDEX command.
Question 95
Which three statements are true about adaptive SQL plan management? (Choose three.)
Correct Answer: A,D,E
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. References:
