Thanks for advice. I am trying reduce downtime . I am upgrading from
11.1.0.7 to 11.2.0.4.
Jonathan has written a very good document
https://jonathanlewis.wordpress.com/2016/04/27/stats-history/ ;.
So whole reason is to save downtime which is around 6 minutes on this step
itself:
I have gone thru catupgrd log and it shows following :
1> Create a new partitioned table wri$_optstat_histhead_history2 which has
the same schema as wri$_optstat_histhead_history
2> Transfer/Insert pending stats (if any) into the new table
3> delete the pending stats (if any) from the old table :
'delete from wri$_optstat_histhead_history where savtime > sysdate; '
commit;
4> Drop table wri$_optstat_histhead_history.
5> Rename wri$_optstat_histhead_history2 as wri$_optstat_histhead_history.
' alter table wri$_optstat_histhead_history2 rename to
wri$_optstat_histhead_history;'
6> create wri* indexes as parallel :
'create index i_wri$_optstat_h_st on
wri$_optstat_histgrm_history (savtime)
parallel tablespace sysaux; '
** The above is same for both WRI$_OPTSTAT_HISTHEAD_HISTORY and
WRI$_OPTSTAT_HISTGRM_HISTORY tables
On Tue, Jan 9, 2018 at 8:52 AM, Dominic Brooks <dombrooks@xxxxxxxxxxx>
wrote:
Why do you want to truncate them?
Upgrades from < 11.2.0.4 to >=11.2.0.4 included a step to partition these
tables which was reported on this list as being time consuming if the
tables were large
In my experience, this history is rarely useful but you only need it when
you need it...
however an upgrade is one of those times you might be particularly
sensitive to histogram changes.
DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL) should take care of it if
that’s what you want to do but that option is listed as only to be used
under the advice of Oracle Support.
Caveat DBA.
Sent from my iPhone
On 9 Jan 2018, at 16:34, Nitin Saxena <ntnsxn7@xxxxxxxxx> wrote:
Hi All,
Any negative impact of truncating following tables before upgrade?
WRI$_OPTSTAT_HISTHEAD_HISTORY;
WRI$_OPTSTAT_HISTGRM_HISTORY;
Do these tables have impact on current stats , performance and SPM?
Thanks
Nitin
