12-8. Using the Optimizer Statistics

Go to the Enterprise Manager page for managing optimizer statistics by clicking Manage Optimizer Statistics on the Administration tabbed page. Note that GATHER_STATS_JOB should be enabled and for this to work, you should set STATISTICS_LEVEL initialization parameter is set to at least TYPICAL.

From Manage Optimizer Statistcs page, you can perform the following tasks on statistics:

• Gather optimizer statistics manually. this action submits the job that GATHER_STATS_JOB automatically does. This should be done in the case where a table's contents have changed so much between automatic gathering jobs that the statistics no longer represent the table accurately. Examples of this are a table that is truncated in the middle of the day and a batch job that runs and adds large amounts of data to a table.
• Restore optimizer statistics to a point in the past. The point in time chosen must be within the optimizer statistics retention period, which defaults to 30 days.
• Lock optimizer statistics to guarantee that the statistics for certain objects are never overwritten. This is useful if statistics have been calculated for a certain table at a time when well-representative data is present, and you want to always have those statistics. No fluctuation in the table affects the statistics if they are locked.
• Unlock optimizer statistics to undo the previously done lock
• Delete optimizer statistics to delete statistics.