Hi, Luc
I had the same issue with MLOG$_* tables (with 10.1.0.4) and I logged an SR a year ago. The support could not find why dbms_stats.gather_schema_stats and dbms_stats.gather_database_stats does not work form MLOG$ tables. They advised me to use dbms_stats.gather_table_stats for MLOG$ tables. It is safe and it is working.
MLOG$ tables have very strange behaviour. They have some VPD-like behaviour so that one log keeps data for many mviews. This is not explained in the docs in any way.
In fact, MLOG$ have to be close-to-empty, if all mviews refresh fine. But if some mview is not refreshed for a log time (for example, there was a bug when you drop mview but it does not unregister form the log), the MLOG$ on master table can grow very big. This is the expected behaviour.
Anyway, your statistics on MLOG$ tables will never be accurate. Maybe this is why dbms_stats.gather_database/schema_stats omit them. But without statistics on them, you can get tons of recursive SQLs for dynamic sampling in the shared pool. So you'd better use gather_table_stats and acquiesce that the stats are wrong.
--
Regards,
Yavor Ivanov
Senior Database Expert
Stemo Ltd
On Mon, 04 Jun 2007 22:39:59 +0300, Luc Demanche <lucdemanche_at_gmail.com> wrote:
> Hi,
>
> I'm using Oracle 10.1.
> Is it normal that all the tables MLOG$ and RUPD$ (associated to an
> materialized view) are not analyzed by DBMS_STATS ?
>
> Thanks
> Luc
>
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 05 2007 - 09:45:18 CDT
