Capture Related Useful SQL Queries

The v$streams_capture and dba_capture views provide useful details about the overall Capture process and its health. The following sections present some examples.

The event enqueuing latency specifies the number of seconds between when an event was recorded in the redo log and when the event was enqueued by the Capture process. This is indicative of the lag in scanning the redo file and the preparing of LCR events by the Capture process. The lag may be caused by low buffers or the general load on the system. In this case, consideration should be given to adding parallelism to the Capture process.  Consideration should also be given to adding to the Streams buffer pool. Sometimes shortage in the Streams pool may slow the capture activity since the events will have to be written to physical table instead of keeping them in the SGA buffers.

The associated values are the event creation time, which is the time when the DML or DDL change generated the redo information for the most recently enqueued event. The enqueue time shows when the Capture process enqueued the event into its queue. These values are for a specific message number. It is a representative in nature. This needs to be verified periodically to assess the latency. Monitoring the latency delays will help DBAs identify the need for and to take corrective action. Otherwise, messages may pile up and cause a big backlog for replication