Home » Oracle » 1z0-117 » What must be done to ensure that an optimizer uses the latest statistics of the table?
You are administering a database that supports an OLTP workload in which one of the applications inserts rows in a table until 12 noon every, after which multiple years perform frequent queries on the table. You want the statistics to be more representative of the table population.
What must be done to ensure that an optimizer uses the latest statistics of the table?
A. Set the STALE_PERCENT preference to 0.
B. Set the OPTIMIZER_MODE parameter to ALL_ROWS.
C. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
D. Use the FIRST_ROWS_n hint in the queries.
E. Unlock and gather statistics for the table after inserts are done and lock them again.
Correct Answer: E
Explanation/Reference:
*
For tables that are substantially modified in batch operations, such as with bulk loads, gather statistics on these tables as part of the batch operation. Call the DBMS_STATS procedure as soon as the load operation completes.
* Statistics for a table or schema can be locked. After statistics are locked, you can make no modifications to the statistics until the statistics have been unlocked. Locking procedures are useful in a static environment in which you want to guarantee that the statistics never change.
The DBMS_STATS package provides two procedures for locking
(LOCK_SCHEMA_STATS and LOCK_TABLE_STATS) and two procedures for unlocking statistics
(UNLOCK_SCHEMA_STATS andUNLOCK_TABLE_STATS).
Incorrect:
A: STALE_PERCENT cannot be set to 0.
*With the DBMS_STATS package you can view and modify optimizer statistics gathered for database objects.
STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.
B: Optimizer_mode applies to the database, not to a specific table.
*
Possible values for optimizer_mode = choose/ all_rows/ first_rows/ first_rows[n]
Important facts about ALL_ROWS
C:Optimizer dynamic sampling refers to the ability of the SQL optimizer to take a sample of rows from a table to calculate missing statistics. Dynamic sampling can be controlled with the OPTIMIZER_DYNAMIC_SAMPLING parameter or the DYNAMIC_SAMPLING hint. level 0 - do not use dynamic sampling
D: First_row_n cannot be used in this way.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
