Re: Under CBO, should WRH$_* and WRI$_* tables and I_WRI$_* index be reorg'ed regularly?

On Mar 19, 12:32 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.
> On Mar 19, 10:43 am, bfoga..._at_gaports.com wrote:
>
> > Should I periodically reorg. these fast growing objects in the SYSAUX
> > tablespace?
>
> Probably not.
>
> > Not long ago I enabled Oracle's GATHER_STATS_JOB and switched from RBO
> > to CBO. Since then I have seen a few dozen objects in the SYSAUX
> > tablespace grow quickly. The tables start with WRH$_* and WRI$_* and
> > the index with I_WRI$_*. Is this growth related to CBO and the
> > gathering of statistics?
>
> I doubt it, but they could be growing due to the gathering of system
> statistics as they are part of the Automatic Workload Repository
> (AWR).  Though you may not be licensed to query these views nor to
> generate the reports the data is gathered regardless.  My guess is
> this is an active system with a large number of users and that's where
> your growth originates.
>
> > I found one note in Metalink (# 454678.1) that talks of reorganizing
> > the WRI$_* tables and indexes. The reorg of indexes is more
> > complicated than Oracle says because they become "unusable" after the
> > table is reorged via the "alter table ... move". I have to drop the
> > indexes then recreate them. Objects that referenced the indexes become
> > invalid and must be recompiled. It gets real messy, especially since I
> > am not sure what effect all of this has on a production database that
> > is being read and updated while I work.
>
> I would hesitate to reorganize such tables/indexes as these belong to
> a product/functionality Oracle has written and designed and is part of
> Grid Control.  Unless you see an actual problem stemming from this
> growth I would go on to other areas which are likely of more concern
> and importance.
>
> > Has anyone else seen growth in these SYSAUX objects? How did you
> > respond to it?
>
> I did nothing, as they are part of AWR.  And I've not seen any need to
> increase the size of the SYSAUX tablespace because of it.
>
>
>
> > Thank you,
> > Bill
>
> David Fitzjarrell

David,

What you and Shakespeare said makes sense, but I do not understand why the objects in SYSAUX started to grow only recently.

I ran the database using RBO for about eight months. Grid Control's AWR had generated hourly snapshots in the AWR the whole time, with a retention of seven days. Freespace in SYSAUX was stable.

I enabled Oracle's GATHER_STATS_JOB about three weeks ago. About two weeks ago I switched to CBO and changed AWR retention to nine days. The nine day change was to allow me to compare a one week old RBO snapshot with a new CBO snapshot. After a few days I changed retention back to seven days then went back to RBO.

The objects in SYSAUX now grow by about 10 MB a day. They did not do this before the changes I just described. If they are related to the AWR why did the growth begin only within the last few weeks?

Bill Received on Wed Mar 19 2008 - 15:53:47 CDT