Skip to Main Content
  • Questions

  • MV log segments growing to big and to fast

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dario.

Asked: November 24, 2016 - 10:14 am UTC

Last updated: November 28, 2016 - 6:50 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I have 2 11g DB connected over DB link.
- DB1 has master table (MASTER_TBL) and
- DB2 materialized view (MV) connected to master table (MASTER_TBL) with fast refresh.

MASTER_TBL is:
- updated constantly from different sources.
- size=11GB

MLOG$_MASTER_TBL:
- COUNT(*) = 772
- size=23GB
- last time truncated one month ago

Do you have any idea how to stop MLOG$_MASTER_TBL growing? I would try to avoid locking MASTER_TBL since it is updated constantly from different sources.

Br,
Dario

and Connor said...

The log will basically grow due to either

a) single very large transactions (hence lots of rows)
b) long time between refreshes (hence lots of rows)
c) lots of concurrency, hence deleted row re-use not optimal

but like any table, the mv log size reflects the "one-off worst" occurrence. For example, if normal refresh was hourly, and you had an error refreshing for (say) 24 hours...them the mv log will be sized to hold 24 hours of changes "forever more".

You can shrink an mv log like you would a table, ie

ALTER MATERIALIZED VIEW LOG ON t ENABLE ROW MOVEMENT;
ALTER MATERIALIZED VIEW LOG ON t SHRINK SPACE;

That should help without creating locking issues.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can't shrink MLOG$

Dario Nikolic, November 25, 2016 - 12:22 pm UTC

Hi Connor,

I tryed to shrink table with commands below but:
- it only shrinked from 21GB to 18GB
SELECT S.BYTES/1024/1024/1024 SIZE_GB,S.* FROM DBA_SEGMENTS S WHERE SEGMENT_NAME LIKE 'MLOG$_MASTER_TBL'

- MLOG$ rownumber = 912
select count(*) from MLOG$_MASTER_TBL
;
- MLOG$ extent number = 18512
SELECT count(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'MLOG$_MASTER_TBL';




Commands for shrinking MVLOG
-- compact
ALTER MATERIALIZED VIEW LOG ON MASTER_TBL ENABLE ROW MOVEMENT;
ALTER MATERIALIZED VIEW LOG ON MASTER_TBL _A7 SHRINK SPACE COMPACT;
ALTER MATERIALIZED VIEW LOG ON MASTER_TBL disable ROW MOVEMENT;

-- lower HWM
ALTER MATERIALIZED VIEW LOG ON MASTER_TBL ENABLE ROW MOVEMENT;
ALTER MATERIALIZED VIEW LOG ON MASTER_TBL _A7 SHRINK SPACE;
ALTER MATERIALIZED VIEW LOG ON MASTER_TBL disable ROW MOVEMENT;

Connor McDonald
November 26, 2016 - 3:25 am UTC

Can you give us a 'describe' on the table and the mview log

bug11072728_mv_refresh_truncate_log

Mikhail Velikikh, November 28, 2016 - 5:05 am UTC

Hello Dario,

I'd been experiencing similar issues in the past till I found the note: Space Not Reclaimed from MLOG$ Segments After MVIEW Refresh (Doc ID 1941137.1) https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1941137.1

Check out whether it help in your case.
Beware that I have experienced some ORA-00054 errors due to that parameter _bug11072728_mv_refresh_truncate_log in few SQL utilising "FOR UPDATE NOWAIT".
This patch have not caused any other significant drawbacks in my environment.
Connor McDonald
November 28, 2016 - 6:50 am UTC

Nice input. Hope this helps our original poster.

Dario Nikolic, December 02, 2016 - 8:42 am UTC

Tnx Mikhail. I've forwarded your input to DBA responsible for this database and will review when get feedback from him.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.