Re: Manual analyze 10g

From: April Sims <>
Date: Mon, 14 Nov 2011 10:04:12 -0600
Message-ID: <>

*Statistics History Automatically Available* Restoring statistics history

There is a way to restore the statistics when performance degrades from the historical record of the statistics saved by default since Oracle Database Version 10g.

SYS_at_ORCL>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; --default of 31 days for 11g

SYS_at_ORCL>select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; --the oldest statistics that can be restored

SYS_at_ORCL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history; --indicates when statistics were gathered for each table.

There are several different ways to restore a particular set of statistics:

execute DBMS_STATS.RESTORE_TABLE_STATS (‘owner','table',date);




execute DBMS_STATS.RESTORE_SCHEMA_STATS(‘owner',date);


Here is an example showing how to restore the entire database statistics to a

certain timestamp:

execute dbms_stats.restore_database_stats( as_of_timestamp => to_timestamp_tz(‘2010-03-19 11:05:00 -6:00', ‘YYYY-MM-DD HH24:MI:SS TZH:TZM') ); On Mon, Nov 14, 2011 at 9:58 AM, Guillermo Alan Bort <>wrote:

> check out:
> Alan.-
> <overquoting :(>
> --

April C. Sims
IOUG SELECT Journal Executive Editor
Twitter, LinkedIn
Oracle Database 11g – Underground Advice for Database Administrators
OCP 8i, 9i, 10g, 11g DBA
Southern Utah University

Received on Mon Nov 14 2011 - 10:04:12 CST

Original text of this message