Home » Oracle » 1z0-117 » What does the optimizer do in this situation?
While tuning a SQL statement, the SQL Tuning Advisor finds an existing SQL profile for a statement that has stale statistics. Automatic optimizer statistics is enabled for the database.
What does the optimizer do in this situation?
A. Updates the existing SQL profiles for which the statistics are stale.
B. Makes the statistics information available to GATHER_DATABASE_STATS_JOB_PROC
C. Starts the statistics collection process by running GATHER_STATS_JOB
D. Writes a warning message in the alert log file
Correct Answer: B
Explanation/Reference:
Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROCprioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes.
Note:
*The optimizer relies on object statistics to generate execution plans. If these statistics are stale or missing, then the optimizer does not have the necessary information it needs and can generate poor execution plans. The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output:
/Recommendations to gather relevant statistics for objects with stale or no statistics
Because optimizer statistics are automatically collected and refreshed, this problem occurs only when automatic optimizer statistics collection is disabled. See "Managing Automatic Optimizer Statistics Collection".
/Auxiliary statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics
The database stores this auxiliary information in an object called a SQL profile.
*Oracle recommends that you enable automatic optimizer statistics collection. In this case, the database automatically collects optimizer statistics for tables with absent or stale statistics. If fresh statistics are required for a table, then the database collects them both for the table and associated indexes.
Automatic collection eliminates many manual tasks associated with managing the optimizer. It also significantly reduces the risks of generating poor execution plans because of missing or stale statistics.
Automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. This internal procedure operates similarly to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that GATHER_DATABASE_STATS_JOB_PROC prioritizes database objects that require statistics,so that objects that most need updated statistics are processed first, before the maintenance window closes.
Reference: OracleDatabase Performance Tuning Guide,Managing Automatic Optimizer Statistics
Collection
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
