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…

Question

Options

• ASet the STALE_PERCENT preference to 0.
• BSet the OPTIMIZER_MODE parameter to ALL_ROWS.
• CSet the OPTIMIZER_DYNAMIC_SAMPLING parameter to 0.
• DUse the FIRST_ROWS_n hint in the queries.
• EUnlock and gather statistics for the table after inserts are done and lock them again.

• A
  2% (1)
• B
  2% (1)
• C
  5% (2)
• D
  14% (6)
• E
  77% (34)

Explanation

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). 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 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 1. ALL_ROWS considers both index scan and full scan and based on their contribution to the overall query, it uses them. If Selectivity of a column is low, optimizer may use index to fetch the data (for example `where employee_code=7712'), but if selectivity of column is quite high ('where deptno=10'), optimizer may consider doing Full table scan. With ALL_ROWS, optimizer has more freedom to its job at its best. 2. Good for OLAP system, where work happens in batches/procedures. (While some of the report may still use FIRST_ROWS depending upon the anxiety level of report reviewers) 3. Likes hash joins over nested loop for larger data sets. 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.

No community discussion yet for this question.