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博客 ” ，链接：https://blog.itpub.net/8242091/viewspace-624753/ ，如需转载，请注明出处，否则将追究法律责任。
