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

On Mar 19, 1:53 pm, bfoga..._at_gaports.com wrote:
> 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

Maybe the steps in Note:396502.1 will help? You must have flipped something on, maybe watching that stuff will give a clue.

jg

--
@home.com is bogus.
...the Itanium(R) Processor Family, from Intel(R) Corporation, is endian
agnostic, i.e., it can operate in either big endian or little endian
mode. Itanium(R) processors running Microsoft(tm) Windows(tm), available from
Microsoft Corporation, and Linux, available free from a number of
sources, operate in little endian mode. HP-UX, available from Hewlett-
Packard Company, operates in big endian mode.