2017-05-18

Restoring objects statistics

From book Expert Oracle SQL: Optimization, Deployment, and Statistics I found new thing: you don’t need to export-import automatically gathered statistics. Instead of you can use DBMS_STATS.RESTORE* procedures.
By default oracle stores data for 31 days.
Quote from Chapter 9 of the book:

• There are several procedures for restoring statistics including DBMS_STATS.RESTORE_SCHEMA_STATS.
• User statistics set with DBMS_STATS.SET_xxx_STATS procedures are not restored. So, for example, any hand-crafted histogram would have to be reapplied after statistics are restored.
• Although this is normally the default behavior, it is good practice to explicitly invalidate any bad plans in the shared pool by using the NO_INVALIDATE => FALSE parameter setting
• The view DBA_OPTSTAT_OPERATIONS provides a history of gather and restore operations.
• By default superseded statistics are retained for 31 days. This can be managed by the function DBMS_STATS.GET_STATS_HISTORY_RETENTION and the procedure DBMS_STATS.ALTER_STATS_HISTORY_RETENTION.

Комментариев нет: