Reply To: Why does it take a long time before capture starts moving after a restart?

#8841
Frank Knot
Participant

Long-running transactions that were active when the capture was shut down cause this behavior. HVR will go back in the transaction logs to find the oldest open transaction that it was tracking when it was shut down. This may be many archived transaction logs back. HVR will then read through every single transaction log file to find out what ever happened to this transaction. Only once the capture reaches the point that transactions commit that were not previously captured will the log report activity on the capture process. This can take minutes and in extreme cases very many minutes. Note that even if the transaction did not yet touch any tables in the channel then still HVR will go all the way back into the transaction logs to process its history.

In an Oracle Database a DBA can find out at the current point in time what the open transactions are. Below is a query that provides such information. The longest running transaction will be first in the list. The script with formatting etc. runs in sqlplus; to run it in another tool simply take out the formatting. Consider running this query before shutting down capture on an Oracle Database to see if some meaningless transactions in this list have a very long duration. Traditionally the database management tool Toad used to be a common violator of meaningful transactions; upon connecting it would start a transaction and only end it upon disconnecting.

set linesize 200
set pagesize 5000
col transaction_duration format a45

with transaction_details as
( select inst_id
  , ses_addr
  , sysdate - start_date as diff
  from gv$transaction
)
select s.username
, to_char(trunc(t.diff))
             || ' days, '
             || to_char(trunc(mod(t.diff * 24,24)))
             || ' hours, '
             || to_char(trunc(mod(t.diff * 24 * 60,24)))
             || ' minutes, '
             || to_char(trunc(mod(t.diff * 24 * 60 * 60,60)))
             || ' seconds' as transaction_duration
, s.program
, s.terminal
, s.status
, s.sid
, s.serial#
from gv$session s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc
/

This query has also been included in the Oracle Log-based Capture check script that is on the wiki in an expert note.

Test drive
Contact us