Replication views taking longer to return results/ Truncate views

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.