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.
Leave a Reply
You must be logged in to post a comment.