We had to relocate one of the replication node to another location. During move, other master nodes had a huge queue build up i.e  more than 1.2 million waiting transactions recorded at deftran on 5 other nodes. When the server was finally back, transaction queues were replicated to original server but we hit performance problem.

I could see deftran had 0 entries at all 5 nodes but still taking 5 minutes to return result for “select count(*) from deftran”

select segment_name,sum(bytes)/1000000 “SIZE IN MB”
from dba_segments
where segment_name like ‘%DEF%’ group by segment_name order by 2 desc;

Showed DEF$_AQCALL is still more than 1GB in size and index on same table DEF$_TRANORDER in excess of 400MB.

I decided to truncate following system tables on all nodes to release space.

select count(*) from deferror;
COUNT(*)
———-
0
select count(*) from deftran;

COUNT(*)
———-
0

select count(*) from  system.DEF$_AQCALL;
select count(*) from  system.DEF$_AQERROR;
select count(*) from  system.DEF$_CALLDEST;
select count(*) from  system.DEF$_DEFAULTDEST;
select count(*) from  system.DEF$_ERROR;
select count(*) from  system.DEF$_ORIGIN;

truncate table system.DEF$_AQCALL;
truncate table system.DEF$_AQERROR;
truncate table system.DEF$_CALLDEST;
truncate table system.DEF$_DEFAULTDEST;
truncate table system.DEF$_ERROR;
truncate table system.DEF$_ORIGIN;

DEF$_AQCALL and DEF$_TRANORDER   dropped to 1 MB and queries are lightening fast.

DEFCALL Contains information about all deferred RPCs. Queries SYSTEM.DEF$_CALL table.

DEFCALLDEST Contains the destination database(s) for each deferred RPC in DEFCALL. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_CALLDEST, SYSTEM.DEF$_ERROR, and SYSTEM.REPCAT$_REPPROP.

DEFDEFAULTDEST Contains the default destinations for deferred RPCs. Queries SYSTEM.DEF$_DEFAULTDEST.

DEFERROR Contains error information for deferred calls that could not be applied at their destination. Queries DEF$_ERROR.

DEFERRORCOUNT Contains the count of errors for each destination. Queries SYSTEM.DEF$_ERROR.

DEFSCHEDULE Contains information about the scheduling of deferred jobs. Queries SYSTEM.DEF$_DESTINATION and SYS.JOB$.

DEFTRAN Contains information about all deferred calls. Queries SYSTEM.DEF$_CALL and SYS.USER$.

DEFTRANDEST Contains the destination database(s) for each deferred transaction. Queries SYSTEM.DEF$_CALL, SYSTEM.DEF$_DESTINATION, SYSTEM.DEF$_ERROR, SYSTEM.DEF$_CALLDEST, and SYSTEM.REPCAT$_REPPROP.

SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
FROM DEFSCHEDULE
WHERE upper(DBLINK) like ‘&1’;

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

SQL> SELECT substr(DBLINK,1,25), AVG_LATENCY “Average Latency”
2 FROM DEFSCHEDULE
3 WHERE upper(DBLINK) like ‘&1’;
Enter value for 1: CPI%
old 3: WHERE upper(DBLINK) like ‘&1’
new 3: WHERE upper(DBLINK) like ‘CPI%’

SUBSTR(DBLINK,1,25) Average Latency
————————- —————
CPI_L1_A.O2
CPI_L1_B.O2 546244624
CPI_L2_A.O2 546860293
CPI_L2_B.O2 546860298

Find out total number of error X’actions

SELECT   COUNT ( * )
FROM   (SELECT   DEFERRED_TRAN_ID,
ORIGIN_TRAN_DB,
DESTINATION,
TO_CHAR (START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’)
TIME_OF_ERROR,
ERROR_NUMBER,
ERROR_MSG
FROM   DEFERROR);

Locate Earliest date for the Error X’actions

SELECT   TO_CHAR (MAX (START_TIME), ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR
FROM   DEFERROR;

Locate Latest date for the Error X’actions

SELECT   TO_CHAR (MIN (START_TIME), ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR
FROM   DEFERROR;

Locate details of Error X’actions

COLUMN DBLINK HEADING ‘SERVER NAME’ FORMAT AAAAAA
COLUMN AVG_LATENCY  HEADING ‘Average Latency’ FORMAT 9999999
COLUMN DEFERRED_TRAN_ID HEADING ‘Deferred|Transaction|ID’ FORMAT A13
COLUMN ORIGIN_TRAN_DB HEADING ‘Origin|Database’ FORMAT A20
COLUMN DESTINATION HEADING ‘Destination|Database’ FORMAT A20
COLUMN TIME_OF_ERROR HEADING ‘Time of|Error’ FORMAT A12
COLUMN ERROR_NUMBER HEADING ‘Oracle|Error|Number’ FORMAT 999999
COLUMN ERROR_MSG HEADING ‘Oracle|Error|Message’ FORMAT A35
COLUMN RECEIVER HEADING ‘Receiver’ FORMAT A25

SELECT   DEFERRED_TRAN_ID,
ORIGIN_TRAN_DB,
DESTINATION,
TO_CHAR (START_TIME, ‘DD-Mon-YYYY hh24:mi:ss’) TIME_OF_ERROR,
ERROR_NUMBER,
ERROR_MSG
FROM   DEFERROR
ORDER BY   START_TIME;

Deferred                                                              Oracle Oracle
Transaction   Origin               Destination          Time of        Error Error
ID            Database             Database             Error         Number Message
————- ——————– ——————– ———— ——- ———————————–
7.26.5257664  CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found
04:39:37

4.9.5280783   CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found
04:39:46

4.19.5280577  CPI_L1_B.O2          CPI_L1_A.O2          30-Jul-2008     1403 ORA-01403: no data found

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;

• Listing General Information About the Purge Job
• Checking the Definition of the Purge Job
• Determining the Amount of Time Since the Last Purge
• Determining the Total Number of Purged Transactions

1. Listing General Information About the Purge Job

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN PRIV_USER HEADING ‘Privilege|Schema’ FORMAT A10
COLUMN BROKEN HEADING ‘Broken?’ FORMAT A7
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A25

SELECT JOB,
PRIV_USER,
BROKEN,
TO_CHAR(NEXT_DATE, ‘DD.MM.YYYY:HH:MI:SS AM’) next_start,
INTERVAL
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_defer_sys.purge%’
ORDER BY 1;

2. Checking the Definition of the Purge Job

SELECT WHAT “Definition of the Purge Job”
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_defer_sys.purge%’ ORDER BY 1;

3. Determining the Amount of Time Since the Last Purge

SELECT ((SYSDATE – LAST_PURGE_TIME) / 60) “Minutes Since Last Purge”
FROM V$REPLQUEUE;

4. Determining the Total Number of Purged Transactions

SELECT TXNS_PURGED “Transactions Purged”
FROM V$REPLQUEUE;

• Listing the Number of Deferred Transactions for Each Destination Master Site
• Determining the Next Start Time and Interval for the Push Jobs
• Determining the Total Number of Transactions Queued for Propagation

1. Listing the Number of Deferred Transactions for Each Destination Master Site

CONNECT repadmin/repadmin@repA

COLUMN DEST HEADING ‘Destination’ FORMAT A45
COLUMN TRANS HEADING ‘Def Trans’ FORMAT 9999

SELECT DBLINK DEST, COUNT(*) TRANS
FROM DEFTRANDEST D
GROUP BY DBLINK;

Destination Def Trans
——————————————— ———
repA 1

2. Determining the Next Start Time and Interval for the Push Jobs

CONNECT repadmin/repadmin@repA.WORLD

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN DBLINK HEADING ‘Destination’ FORMAT A22
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A25

SELECT JOB,
DBLINK,
TO_CHAR(NEXT_DATE, ‘DD-MON-YYYY HH:MI:SS AM’) next_start,
INTERVAL
FROM DEFSCHEDULE
WHERE DBLINK != (SELECT GLOBAL_NAME FROM GLOBAL_NAME)
AND JOB IS NOT NULL
ORDER BY 1;

3. Determining the Total Number of Transactions Queued for Propagation

Run the following query to display the total number of transactions in the deferred transaction queue that are waiting to be propagated:

repA>sqlplus repadmin/repadmin@repA

SELECT COUNT(DISTINCT DEFERRED_TRAN_ID) “Transactions Queued”
FROM DEFTRANDEST;

1. Listing General Information About Administrative Requests at the Master
2. Determining the Cause of Administrative Request Errors at the Master
3. Listing Information About the Job that Executes Administrative Requests at the Master

Listing General Information About Administrative Requests at the Master

COLUMN ID HEADING ‘Admin|Request|ID’ FORMAT 999999
COLUMN REQUEST HEADING ‘Request’ FORMAT A25
COLUMN STATUS HEADING ‘Status’ FORMAT A15
COLUMN MASTER HEADING ‘Master|Site’ FORMAT A25

SELECT ID, REQUEST, STATUS, MASTER FROM DBA_REPCATLOG;

Determining the Cause of Administrative Request Errors at the Master

COLUMN ID HEADING ‘Admin|Request|ID’ FORMAT 999999
COLUMN REQUEST HEADING ‘Request’ FORMAT A30
COLUMN ERRNUM HEADING ‘Error|Number’ FORMAT 999999
COLUMN MESSAGE HEADING ‘Error|Message’ FORMAT A32

SELECT ID, REQUEST, ERRNUM, MESSAGE
FROM DBA_REPCATLOG WHERE STATUS = ‘ERROR’;

Listing Information About the Job that Executes Administrative Requests at the Master

You can query the DBA_JOBS data dictionary view to list the following information about this job.

The DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN porcedure executes the local outstanding deferred administrative procedures for the specified master group at the current master site, or (with assistance from job queues) for all master sites.

COLUMN JOB HEADING ‘Job ID’ FORMAT 999999
COLUMN PRIV_USER HEADING ‘Privilege|Schema’ FORMAT A10
COLUMN BROKEN HEADING ‘Broken?’ FORMAT A7
COLUMN next_start HEADING ‘Next Start’
COLUMN INTERVAL HEADING ‘Interval’ FORMAT A20

SELECT JOB,
PRIV_USER,
BROKEN,
TO_CHAR(NEXT_DATE,’DD.MM.YYYY:HH:MI:SS AM’) next_start,
INTERVAL
FROM DBA_JOBS
WHERE WHAT LIKE ‘%dbms_repcat.do_deferred_repcat_admin%’
ORDER BY 1;