Distributed Databases   «Prev  Next»
Lesson 8 Monitoring replication events with the V$ views
Objective Explain how to monitor replication by querying V$ views.

Monitoring Replication Events with the V$ Views

Oracle provides a dynamic performance view for displaying the information for all wait events within Oracle. These event names are constantly changing, but Oracle has introduced two new wait events that apply to Oracle replication and networking.
Many of the wait events inside Oracle are tied to the internal implementation of Oracle and are subject to change or deletion without notice. This may even happen during a bug fix patch release. Application developers should be aware that these views are constantly changing and that queries using these views may become invalid at any time.
As a review, let us examine the structure of the V$EVENT_NAME internal view from SQL*Plus:

SQL> DESC V$EVENT_NAME
EVENT#          NUMBER
NAME            VARCHAR2(64)
PARAMETER1      VARCHAR2(64)
PARAMETER2      VARCHAR2(64)
PARAMETER3      VARCHAR2(64)

Oracle has several hundred internal wait events in this view, but there are two values for the NAME column that are of interest to us:
  1. Queue messages: This row in V$EVENT_NAME shows when a session is waiting on an empty OLTP queue (Advanced Queue) for a message to arrive so that the session can dequeue that message.
  2. Replication dequeue: This row of V$EVENT_NAME shows the information relating to replication dequeue events of the target server.

Below is a sample query to see all wait events associated with queue messages and replication dequeue events:
SELECT NAME,
PARAMETER1 queue_id,
PARAMETER2 process_number,
PARAMETER3 wait_time
FROM 
V$EVENT_NAME
WHERE
NAME IN (‘queue messages’, 
‘replication dequeue’)

The relevant column here is the wait time value in parameter3, which provides the intended wait time for the queue. Running the above query can be useful in cases where you suspect that Oracle is waiting on a queue refresh or a replication dequeue.
Now that we have examined this new internal wait event, let us take a look at implementing snapshot security within Oracle.