Monitoring Performance in a Replication Environment

1. Tracking the Average Number of Row Changes in a Replication Transaction

SELECT   DECODE (TXNS_ENQUEUED,
 0, 'No Transactions Enqueued',
 (CALLS_ENQUEUED / TXNS_ENQUEUED))
 "Average Number of Row Changes"
 FROM   V$REPLQUEUE;

2. Tracking the Rate of Transactions Entering the Deferred Transactions Queue

SELECT   (R.TXNS_ENQUEUED / ( (SYSDATE - I.STARTUP_TIME) * 24 * 60 * 60))
 "Average TPS"
 FROM   V$REPLQUEUE R, V$INSTANCE I;

3. Determining the Average Network Traffic Created to Propagate a Transaction

set linesize 120;
COLUMN AV_BYTES HEADING 'Average Bytes' FORMAT 999999999
COLUMN AV_TRIPS HEADING 'Average Round Trips' FORMAT 9999999

SELECT   SUBSTR (DBLINK, 1, 30) Server_Name,
 DECODE (
 TOTAL_TXN_COUNT,
 0,
 'No Transactions',
 ROUND (
 (TOTAL_BYTES_SENT + TOTAL_BYTES_RECEIVED) / TOTAL_TXN_COUNT
 )
 )
 AV_BYTES,
 DECODE (TOTAL_TXN_COUNT,
 0, 'No Transactions',
 (TOTAL_ROUND_TRIPS / TOTAL_TXN_COUNT))
 AV_TRIPS
 FROM   DEFSCHEDULE
 WHERE   DBLINK LIKE '%C5%';

If this query returns ‘No transactions’ in both columns, then no transactions have been propagated to the specified remote site since the statistics were last cleared.

4. Determining the Average Amount of Time to Apply Transactions at Remote Sites

SELECT   AVG_LATENCY "Average Latency"
 FROM   DEFSCHEDULE
 WHERE   UPPER (DBLINK) LIKE 'REPB%';

Average latency is the average number of seconds between the first call of a transaction on the current site and the confirmation that the transaction was applied at the remote site. The

5. Clearing the Statistics for a Remote Master Site in the DEFSCHEDULE View

To clear the propagation statistics in the DEFSCHEDULE view for a particular remote master site, use

BEGIN
 DBMS_DEFER_SYS.CLEAR_PROP_STATISTICS (dblink => 'REPB');
END;

6. Determining the Transactions Currently Being Propagated to a Remote Master

SELECT                                                        /*+ ORDERED */
 P  .XID "Tran Being Propagated",
 (MAX (C.CALLNO) + 1) "Number of Calls in Tran",
 (P.SEQUENCE / MAX (C.CALLNO) + 1) * 100 "% Processed Calls"
 FROM   V$REPLPROP P, DEFCALL C
 WHERE       P.NAME LIKE '%SLAVE%'
 AND P.DBLINK = 'CEL1.WORLD'
 AND C.DEFERRED_TRAN_ID = P.XID
GROUP BY   P.XID, P.SEQUENCE;