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”
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;
select count(*) from deftran;
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.
DEFERRORCOUNT Contains the count of errors for each destination. Queries SYSTEM.DEF$_ERROR.