物化試圖日誌過大

發現一張物化試圖日誌表達到了2G。...

[@more@]

1. 遠端試圖使用的rowid重新整理，可我的日誌用的是主鍵建立的，所以fast重新整理失敗，結果使用了完全重新整理，導致日誌表一直增加。

順便看了下物化檢視重新整理的順序。

更新的順序是先刪除後更新，最後插入。

首先查詢出需要刪除的資料：

SELECT /*+ remote_mapped(MASPROD104.CIMC.COM) */
DISTINCT M_ROW$$
FROM (SELECT M_ROW$$
FROM "MAS9I"."MLOG$_ACTION_TEST1"@MASPROD104.CIMC.COM MLOG$
WHERE "SNAPTIME$$" > :1
AND ("DMLTYPE$$" != 'I')) LOG$
WHERE (M_ROW$$) NOT IN
(SELECT ROWID
FROM "MAS_TAB$"
WHERE MAS_TAB$.ROWID = LOG$.M_ROW$$);

DELETE FROM "MAS9I"."MV_ACTION_TEST1" SNAP$ WHERE M_ROW$$ = :1;

然後查出需要update和insert的資料
SELECT /*+ remote_mapped(MASPROD104.CIMC.COM) */
CURRENT$."R_ID",
CURRENT$."OWNER",
CURRENT$."OBJECT_NAME",
CURRENT$."SUBOBJECT_NAME",
CURRENT$."OBJECT_ID",
CURRENT$."DATA_OBJECT_ID",
CURRENT$."OBJECT_TYPE",
CURRENT$."CREATED",
CURRENT$."LAST_DDL_TIME",
CURRENT$."TIMESTAMP",
CURRENT$."STATUS",
CURRENT$."TEMPORARY",
CURRENT$."GENERATED",
CURRENT$."SECONDARY",
ROWIDTOCHAR(CURRENT$.ROWID) M_ROW$$
FROM (SELECT "A".ROWID "R_ID",
"A"."OWNER" "OWNER",
"A"."OBJECT_NAME" "OBJECT_NAME",
"A"."SUBOBJECT_NAME" "SUBOBJECT_NAME",
"A"."OBJECT_ID" "OBJECT_ID",
"A"."DATA_OBJECT_ID" "DATA_OBJECT_ID",
"A"."OBJECT_TYPE" "OBJECT_TYPE",
"A"."CREATED" "CREATED",
"A"."LAST_DDL_TIME" "LAST_DDL_TIME",
"A"."TIMESTAMP" "TIMESTAMP",
"A"."STATUS" "STATUS",
"A"."TEMPORARY" "TEMPORARY",
"A"."GENERATED" "GENERATED",
"A"."SECONDARY" "SECONDARY"
FROM "A") CURRENT$,
(SELECT DISTINCT M_ROW$$
FROM "MAS9I"."MLOG$_ACTION_TEST1"@MASPROD104.CIMC.COM MLOG$
WHERE "SNAPTIME$$" > :1
AND ("DMLTYPE$$" != 'D')) LOG$
WHERE CURRENT$.ROWID = LOG$.M_ROW$$;

UPDATE "MAS9I"."MV_ACTION_TEST1"
SET "R_ID" = :1,
"OWNER" = :2,
"OBJECT_NAME" = :3,
"SUBOBJECT_NAME" = :4,
"OBJECT_ID" = :5,
"DATA_OBJECT_ID" = :6,
"OBJECT_TYPE" = :7,
"CREATED" = :8,
"LAST_DDL_TIME" = :9,
"TIMESTAMP" = :10,
"STATUS" = :11,
"TEMPORARY" = :12,
"GENERATED" = :13,
"SECONDARY" = :14
WHERE M_ROW$$ = :15
select * from sys.snap_reftime$;

INSERT INTO "MAS9I"."MV_ACTION_TEST1" ("R_ID","OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY","M_ROW$$") VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15)
UPDATE sys.snap$ SET status = :1, rscn = :2, refhnt = :3, flag = :4 WHERE vname = :5 AND sowner = :6 AND instsite = :7