Logminer memory usage

Question

we replicate data from several databases using LogMiner. In all of DBs it works as it should except one.

When I execute following commands:

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/arch/offline_log.dbf');
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
select count(*) from V$LOGMNR_CONTENTS where rownum <1000;
EXECUTE DBMS_LOGMNR.END_LOGMNR;

The oracle session allocates 4GB PGA memory. Without where condition, it allocates 32GB PGA and dies with ORA-04030. In other DBs this command produces same symptoms whereas using own redo log file requires about 40MB PGA only.

All our databases are version 19.21

Edit - heapdump:

Additional info:
=======================================
PRIVATE MEMORY SUMMARY FOR THIS PROCESS
---------------------------------------
******************************************************
PRIVATE HEAP SUMMARY DUMP
3299 MB total:
  3298 MB commented, 512 KB permanent
   870 KB free (0 KB in empty extents),
  3278 MB,   2 heaps:   "Logminer LCR c "  646 KB free held

*** 2024-03-12 18:24:08.870
------------------------------------------------------
Summary of subheaps at depth 1
3298 MB total:
  3297 MB commented, 314 KB permanent  519 KB free (68 KB in empty extents),
  1929 MB, 1819373 chunks:  "krvuinl             " 2 KB free held
  1117 MB, 2729074 chunks:  "krvtadc1            " 3 KB free held

Regards, Darvud

Answer 1

Oracle Doc Doc 807080.1 ( Release 10.2 to 12.1 ... but it works for 19c also )

According to the documentation in Oracle® Database Utilities:

If the COMMITTED_DATA_ONLY option is specified and you issue a query, LogMiner stages all redo records within a single transaction in memory until LogMiner finds the commit record for that transaction. Therefore, it is possible to exhaust memory, in which case an "Out of Memory" error will be returned.

Solution:

restart LogMiner without the COMMITTED_DATA_ONLY option specified and reissue the query.

Without COMMITTED_DATA_ONLY option finished in 0,07 seconds and heapdump was not logged any PGA usage.