Capture to Dequeue Latency for an Event

The following SQL statement shows the dequeue latency.  In order to assess the latency pattern, this SQL should be run repeatedly when an Apply process is actively dequeuing messages.

COLUMN APPLY_NAME HEADING 'Apply Process|Name' FORMAT A17
COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999
COLUMN CREATION HEADING 'Event Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT
999999

SELECT APPLY_NAME,(DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400
LATENCY, TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
CREATION, TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
DEQUEUED_MESSAGE_NUMBER
FROM V$STREAMS_APPLY_READER
/

Latency can also be found in the dba_apply_progress view. The difference between apply_time and applied_message_create_time determine the latency in seconds as shown below: