11g Database Very Slow When Using The Logminer CONTINOUS_MINE Option (Doc ID 971162.1)

Last updated on APRIL 12, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.

Symptoms


Select from v$logmnr_contents can be very slow when LogMiner is set up to use the CONTINUOUS_MINE option.
The following statement is consuming lot of elapsed time, which comes from LogMiner itself:

SELECT SCN, TO_CHAR(SCN) AS SCN_CHAR, SCN AS SCN_FLOAT, NVL(:aucEolSCN -SCN, :aucEolSCN) AS SCN_EOF_DIFF, TO_CHAR(TIMESTAMP) AS TIMESTAMP,
TO_CHAR(XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS TRANID, SEG_OWNER,SEG_NAME, USERNAME, ROLLBACK, OPERATION_CODE, SQL_REDO, SQL_UNDO, CSF,
STATUS FROM V$LOGMNR_CONTENTS;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- --------------------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 73534 8.53 805.12 0 0 0 73554
------- ------ -------- ---------- ---------- ---------- --------------------
total 73534 8.53 805.12 0 0 0 73554

Statistics indicate 'log file sequential read' as the top wait event for the session:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 73534 0.00 0.03
SQL*Net message from client 73534 1.71 17.14
log file sequential read 2238 0.59 47.76
SQL*Net more data to client 4388 0.00 0.06
wait for activate message 749 0.97 731.37
control file sequential read 3730 0.10 0.79

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms