truncate MLOG$

转载 2007年09月14日 17:12:00
转自: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.
 

相关文章推荐

hdu5521 修改的mlog(n)的Dijkstra算法 2015ACM沈阳现场赛题

题意 给你n个点,从其中选出一些点可以构成一个点集合,给你m个点集合,在一个集合里的点相互到达的时间是d 问你从1号点和n号点分别出发一个人,选在哪些点相遇,可以使总的相遇时间最短。 n是2~1000...

mlog-1.2.zip

  • 2015年09月06日 17:37
  • 25.93MB
  • 下载

sql中 truncate 和 delete 有什么区别

  • 2011年05月27日 14:30
  • 21KB
  • 下载

使用binlog找回truncate数据

测试环境: MariaDB-10.0.13 日志格式: mixed 表结构如下: CREATE TABLE `tb_test` ( `id` int(11) NOT NULL AUTO_...

oracle truncate恢复工具

  • 2016年04月18日 13:30
  • 8.93MB
  • 下载

truncate text and add ellipses

  • 2017年11月08日 08:33
  • 1.93MB
  • 下载

Oracle数据库中truncate命令和delete命令的区别

Oracle数据库中truncate命令和delete命令的区别 首先讲一下,truncate命令: 语法:TRUNCATE  TABLE  table; 表格里的数据被清空,存储空间被...

sql之truncate、delete与drop区别

  • 2012年03月06日 10:40
  • 5KB
  • 下载

sql语句中----删除表数据drop、truncate和delete的用法

说到删除表数据的关键字,大家记得最多的可能就是delete了 然而我们做数据库开发,读取数据库数据.对另外的两兄弟用得就比较少了 现在来介绍另外两个兄弟,都是删除表数据的,其实也是很容易理...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:truncate MLOG$
举报原因:
原因补充:

(最多只允许输入30个字)