truncate MLOG$

转自：http://xsb.itpub.net/post/419/50466

Doc ID: Note:69432.1 Subject: CAN AN INDEX BE CREATED ON MLOG$ (SNAPSHOT LOG)? Type: BULLETIN Status: PUBLISHED

Can an index be created on MLOG$ (SNAPSHOT LOG)?
================================================

You have noticed that your fast refreshes are becoming slower and slower and 
less efficient as time goes on. You want to add an index on the MLOG$ snapshot 
log table to speed up the refreshes and to prevent a Full Table Scan on the 
snapshot log.

First of all, the answer is NOT to add an index on the snapshot log table.  
Oracle has to do a full table scan on the snapshot log so that it can read all 
the rows that it needs to refresh for a particular snapshot. Besides, all SQL 
statements generated by the refresh operation is hardcoded in the kernel.  

What may be causing the performance degradation is that your snapshot log's 
High Water Mark (HWM) may be wastefully high.  The snapshot log table grows at 
peak times, but never shrinks once the refresh is done.  Therefore, during a 
fast refresh of the snapshot, Oracle will read the snapshot log using full 
table scan all the way to the HWM. 

The answer to speeding up the performance of the snapshot refresh is to reset 
the HWM.  The only way to do this is to truncate the snapshot log or recreate 
it.  

Once your snapshot log is purged (meaning all snapshots have already refreshed 
against that master table), then issue a truncate on it.

i.e.  SQL> truncate table mlog$_EMP;
      
This will reset the HWM without affecting any of your snapshot's fast refreshes.  

If you choose to RECREATE your snapshot log, you will have to follow up by
performing a COMPLETE refresh on all the affected snapshots.