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

<bfogarty_at_gaports.com> schreef in bericht news:c423a9bd-9ea7-4e38-b140-2adcee6c5afb_at_e60g2000hsh.googlegroups.com...
> Should I periodically reorg. these fast growing objects in the SYSAUX
> tablespace?
>
> 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 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.
>
> Has anyone else seen growth in these SYSAUX objects? How did you
> respond to it?
>
> Thank you,
> Bill

I already posted an answer, but for some reason it did not show up here (maybe later, in that case sorry for the repetition). With the right retention settings, the growing would stop eventually, and empty space will be reused. At least, that's what I read from the docs. So if after your retention time the objects are not too large to handle, I would leave them as they are....

Shakespeare Received on Wed Mar 19 2008 - 11:39:08 CDT