关闭

truncate MLOG$

标签: tableperformanceoraclesqllessup
797人阅读 评论(0) 收藏 举报
转自: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.
 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:59801次
    • 积分:801
    • 等级:
    • 排名:千里之外
    • 原创:12篇
    • 转载:38篇
    • 译文:1篇
    • 评论:4条
    文章分类
    文章存档
    最新评论
    五花八门