Home » Oracle » 1z0-117 » Which two options might help to avoid the performance degradation of the queries?
You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently during day time and you notice a performance degradation for queries using those tables due to stale statistics.
Which two options might help to avoid the performance degradation of the queries?
A. Set the global statistics preference STALE_PERCENT to 0.
B. Use dynamically sampling hint for the queries on frequently updated tables.
C. Create histogram for the columns used frequently in the WHERE clause.
D. Gather statistics with global statistics preference NO_VALIDATE to TRUE.
E. Set the OPTIMZER_USE_PENDING_STATISTICS parameter to TRUE.
Correct Answer: BC
Explanation/Reference:
B:Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its “guesses.” This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling.
There are two ways to use dynamic sampling:
The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command. The DYNAMIC_SAMPLING query hint can be added to specific queries.
C:A histogram is a collection of information about the distribution of values within a column.In some cases, the distribution of values within a column of a table will affect the optimizer’s decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index access. Histograms are also important for determinine the optimal table join order.
Incorrect:
A: Too much statistics would be gathered.
Note: 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%.
D:In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column in a table.
E:OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses pending statistics when compiling SQL statements.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
