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

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 Received on Wed Mar 19 2008 - 11:32:08 CDT