Posted by
ErmanArslansOracleBlog on
Apr 13, 2021; 11:26am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/logminer-read-rate-tp9605p9617.html
Based on your previous updates; you read 3 logs each 1GB in size in 2 min 49 secs right?
Actually it takes a little less based on your trace file.
Anyways, lets say it is 3 min.
So you read 1GB per min. This makes 60GB per hour.
LGWR performance depends on lots of things, including the performance of IO Subsytem of yours. It depends on the system activity as well..
60GB looks okay for a traditional system.. You say, you generate 1GB sized redo per hour, so I don't see a problem here.
In your trace, you only spent time on the following sql and it is normal ..
SELECT *
FROM
V$LOGMNR_CONTENTS WHERE TABLE_NAME = 'TABLENOTEXIT'
As for logmnr memory analysis, you can use the following query ->
SQL> select sysdate,session_state, AVAILABLE_TXN, DELIVERED_TXN,AVAILABLE_TXN-DELIVERED_TXN DIFFERENCE, MAX_MEMORY_SIZE, USED_MEMORY_SIZE FROM v$logmnr_session;
Description of the columns of v$logmnr_session ->
https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1161.htmcheck the following blogpost of mine as well ->
https://ermanarslan.blogspot.com/2020/03/rdbms-cdc-log-miner-read-performance.html