Re: MLOG$ and RUPD$ tables are not analyzed

  • From: "Yavor Ivanov" <Yavor_Ivanov@xxxxxxxx>
  • To: lucdemanche@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 05 Jun 2007 17:45:18 +0300

        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@xxxxxxxxx> 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
>



--
//www.freelists.org/webpage/oracle-l


Other related posts: