Re: truncate WRI$ tables !

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_at_hotmail.com> 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_at_gmail.com> 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
>

-- 
Cheers
Nitin

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