Oracle Database Discussions

Hi,

We have a 4 TB DWH on 11.1.0.7. We have recently added approximately 2000 new MV partitions to the DB, and due to this we believe that the automatic stats job is not completing in its alloted time as many new partition stats have yet to be gathered due to the increase in number of objects to be analyzed.

We have 2 tables that use incremental stats which are not gathering stats on latest partitons due to aforementioned reason, and the two below tables, which hold incremental stats for partitions are approximetly 17GB in side now. So if we were to convert all other tables to using incremental stats this may well render these tables to grow to inordinate sizes.

SYSAUX.I_WRI$_OPTSTAT_SYNOPSIS
SYSAUXWRI$_OPTSTAT_SYNOPSIS$

When running some SQL tests on partitons which have stats versus partitons that have no stats we got identical results so it appears that the optimiser is making correct decisions at this point in time.

Would it be wise to run ad-hock stats gathering jobs on certain MV's during out of business hours and do this frequently, or run one off stats gather job & lock down stats, or convert other tables to incremental, or increase weekend window, or just do nothing as tests have proved that we are not seeing negative impact (yet).

If anyone has similar experience or sound advice please by all mean post a reply.

Thanks,
firefly