logminer read rate

Posted by Roshan on Apr 12, 2021; 9:14am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/logminer-read-rate-tp9605.html

Hi Erman,

I have checked your blog on logminer performance.

https://ermanarslan.blogspot.com/2019/11/rdbms-oracle-log-miner-read-rate.html

In fact I will configure logminer using redo logs only and not archivelogs

I have added the redo logs as follows:
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/app/PMTG/db/apps_st/data/log01a.dbf', -
OPTIONS => DBMS_LOGMNR.NEW);>

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/app/PMTG/db/apps_st/data/log02a.dbf', -
OPTIONS => DBMS_LOGMNR.ADDFILE);> >

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/app/PMTG/db/apps_st/data/log03a.dbf', -
OPTIONS => DBMS_LOGMNR.ADDFILE);> >

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR
(STARTSCN => 315614665840, ENDSCN => 315615487985,
OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION+ DBMS_LOGMNR.NO_SQL_DELIMITER+DBMS_LOGMNR.NO_ROWID_IN_STMT+ DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

where startscn and endscn is from v$log;


SQL> set arraysize 1000
SQL> set timing on
SQL> SELECT * FROM V$LOGMNR_CONTENTS WHERE TABLE_NAME = 'TABLENOTEXIT';

no rows selected

Elapsed: 00:02:49.90




Rate= archived_log_count x archive_log_size / duration.

3 x 1.074/(60/2.8)

=0.15 G/hr


The amount of archive log generated per hour is low (almost 1G/h).
archive12Apr.archive12Apr

log file sequential read 6,958 1.1 0.15 .0 System I/O
AWR:awrrpt_1_4354_4355.html

Does this mean our system/DB is well tuned?

I see from Finding 1 that this logminer is taking time.

12aqm6fhn7z7c

How can we tune it?

Thanks,

Roshan