Displaying the Amount of Time Between Capture and Apply

You can query the V$STREAMS_APPLY_COORDINATOR dynamic performance view to monitor the performance of an Oracle Streams replication environment. Specifically, you can query this view to determine the amount of time between when a change was captured at the source database and when it was applied by an apply process at the current database. The query shows the amount of time for a recent change that was applied by each apply process in the current database. In an Oracle Streams replication environment, the amount of time between capture and apply should be relatively short.

Some changes might take longer than others to be captured, propagated, and applied. The query shows the message number of the change you are monitoring, and each change has a different message number. Therefore, you can run this query multiple times to view the amount of time between capture and apply for different changes.

To determine the amount of time between capture and apply for a recent change: 

1. Open SQL*Plus and connect to the database running the apply process as the Oracle Streams administrator.

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

2. Run the following query:

   COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A30
   COLUMN 'Time Between Capture and Apply' FORMAT 999999
   COLUMN HWM_MESSAGE_NUMBER HEADING 'Applied Message Number' FORMAT 999999999
    
   SELECT APPLY_NAME,
         (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Time in Seconds",
          HWM_MESSAGE_NUMBER
     FROM V$STREAMS_APPLY_COORDINATOR;
   

   The output will be similar to the following:

   Apply Process Name             Time in Seconds Applied Message Number
   ------------------------------ --------------- ----------------------
   APPLY$_DB_2                                  3                1486062