LOGMINER的記憶體消耗

如下只是9i的行為

2組日誌：
1
ALTER SYSTEM ARCHIVE LOG CURRENT;
BEGIN
  FOR i IN 1..500000 LOOP
    INSERT INTO test VALUES(i,i-2,'a','a'); 
  END LOOP;
  COMMIT;
END;
/
-rw-r-----  1 oracle9 dba  104857088  1??  9 13:36 arch1_131.dbf
-rw-r-----  1 oracle9 dba  104857088  1??  9 13:36 arch1_132.dbf
-rw-r-----  1 oracle9 dba  104857088  1??  9 13:37 arch1_133.dbf
-rw-r-----  1 oracle9 dba   28555776  1??  9 13:42 arch1_134.dbf

2
ALTER SYSTEM ARCHIVE LOG CURRENT;
BEGIN
  FOR i IN 1..500000 LOOP
    INSERT INTO ctais2.test VALUES(i,i-2,'a','a');
    COMMIT; 
  END LOOP;
END;
/
-rw-r-----  1 oracle9 dba  104857088  1??  9 14:26 arch1_140.dbf
-rw-r-----  1 oracle9 dba  104857088  1??  9 14:27 arch1_141.dbf
-rw-r-----  1 oracle9 dba  104856576  1??  9 14:27 arch1_142.dbf
-rw-r-----  1 oracle9 dba  104856576  1??  9 14:28 arch1_143.dbf

嘗試對1的arch1_131.dbf挖日誌
SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_131.dbf',OPTIONS => DBMS_LOGMNR.new);
  3   DBMS_LOGMNR.START_LOGMNR(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
END;  4 
  5  /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM v$logmnr_contents WHERE peration='INSERT';

  COUNT(*)
----------
         0

SQL> SELECT a.PGA_USED_MEM,a.PGA_ALLOC_MEM,a.PGA_FREEABLE_MEM,a.PGA_MAX_MEM
  2    FROM V$PROCESS a
  3   WHERE ADDR IN (SELECT PADDR
  4               FROM V$SESSION
  5              WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT))
  6  ;

PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------- ---------------- -----------
    11105825      11122817                0   243185793

SQL> BEGIN
  2    dbms_logmnr.end_logmnr;
  3  END;  
  4  /

PL/SQL procedure successfully completed.

然後挖所有日誌

SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_131.dbf',OPTIONS => DBMS_LOGMNR.new);
  3   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_132.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
  4   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_133.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
  5   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_134.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
  6   DBMS_LOGMNR.START_LOGMNR(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM v$logmnr_contents WHERE peration='INSERT';

  COUNT(*)
----------
    500000

SQL> SELECT a.PGA_USED_MEM,a.PGA_ALLOC_MEM,a.PGA_FREEABLE_MEM,a.PGA_MAX_MEM
  2    FROM V$PROCESS a
  3   WHERE ADDR IN (SELECT PADDR
  4               FROM V$SESSION
  5              WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT))
  6  ;

PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------- ---------------- -----------
    11112457      11179137                0   744461441

SQL> BEGIN
  2    dbms_logmnr.end_logmnr;
  3  END;  
  4 
  5  /

PL/SQL procedure successfully completed.

這裡忘記記錄執行時間了，起碼執行了50分鐘才挖出來，記憶體消耗700+MB

那麼嘗試挖下情況2的日誌

SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_140.dbf',OPTIONS => DBMS_LOGMNR.new);
  3   DBMS_LOGMNR.START_LOGMNR(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM v$logmnr_contents WHERE peration='INSERT';

  COUNT(*)
----------
    110187

SQL> SELECT a.PGA_USED_MEM,a.PGA_ALLOC_MEM,a.PGA_FREEABLE_MEM,a.PGA_MAX_MEM
  2    FROM V$PROCESS a
  3   WHERE ADDR IN (SELECT PADDR
  4               FROM V$SESSION
  5              WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT));

PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------- ---------------- -----------
    11104665      11188353                0    22067329

SQL> BEGIN
  2    dbms_logmnr.end_logmnr;
  3  END;
  4  /

PL/SQL procedure successfully completed.

挖全部
SQL> BEGIN
  2   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_140.dbf',OPTIONS => DBMS_LOGMNR.new);
  3   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_141.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
  4   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_142.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
  5   DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u01/oracle9/app/oracle/product/9.2.0/db_1/dbs/arch1_143.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
  6   DBMS_LOGMNR.START_LOGMNR(Options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM v$logmnr_contents WHERE peration='INSERT';

  COUNT(*)
----------
    441471

SQL> SELECT a.PGA_USED_MEM,a.PGA_ALLOC_MEM,a.PGA_FREEABLE_MEM,a.PGA_MAX_MEM
  2    FROM V$PROCESS a
  3   WHERE ADDR IN (SELECT PADDR
  4               FROM V$SESSION
  5              WHERE SID = (SELECT DISTINCT SID FROM V$MYSTAT))
  6  ;

PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
------------ ------------- ---------------- -----------
    11065365      11196865                0    22075841

SQL> BEGIN
  2    dbms_logmnr.end_logmnr;
  3  END;  
  4  /

PL/SQL procedure successfully completed.

這樣只要22M記憶體。

上面實驗2個目的：
事務只有rollback/commit後才能在v$logmnr_contents中查詢出來，如果一個日誌一個日誌的挖，對於大事務，跨日誌了的事務，如果在該日誌中無該事務的commit/rollback，那麼無法挖出來
在logminer沒有找到commit/rollback標記以前，挖出來的資訊暫時保持在PGA中，記憶體消耗巨大。測試時挖一個100M的日誌，PGA使用操作了1G，系統分頁卡死掉了。需要的PGA量是和日誌檔案中的內容相關的
logminer不會使用TEMP表空間（通過v$sort_usage監控的）

以前遇到過1個故障，單個程式記憶體消耗超過16G，等待log file sequential read的時間很長，就是有人在挖日誌
在ODS這種系統上搞日誌挖掘，不怎麼現實

PS:使用COMMITTED_DATA_ONLY會加大PGA消耗

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-624753/，如需轉載，請註明出處，否則將追究法律責任。