Re: Manual analyze 10g

From: April Sims <aprilcsims_at_gmail.com>
Date: Mon, 14 Nov 2011 10:04:12 -0600
Message-ID: <CAK+cZDfYuoxE61nugMMcET8COjRtHy3CcNF7P+voGF8gz6T8aA_at_mail.gmail.com>

*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_DATABASE_STATS(date);

execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date);

execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date);

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

execute DBMS_STATS.RESTORE_SYSTEM_STATS(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 <cicciuxdba_at_gmail.com>wrote:

> check out: http://www.dbasupport.com/oracle/ora9i/CBO4_6.shtml
> Alan.-
> <overquoting :(>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
April C. Sims
IOUG SELECT Journal Executive Editor
http://aprilcsims.wordpress.com
Twitter, LinkedIn
Oracle Database 11g – Underground Advice for Database Administrators
<http://www.amazon.com/Oracle-Database-Underground-Advice-Administrators/dp/1849680000/ref=sr_1_1?ie=UTF8&s=books&qid72289339&sr=8-1#noop>
https://www.packtpub.com/oracle-11g-database-implementations-guide/book
OCP 8i, 9i, 10g, 11g DBA
Southern Utah University
aprilcsims_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

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