This discussion is archived
3 Replies Latest reply: May 7, 2010 12:13 AM by 769627 RSS

[Logical Standby] Which table/SQL caused paging-out

750288 Newbie
Currently Being Moderated
We have a Primary-Logical DR configuration.
Recently, it has a problem with the logical: it's continuously paging out data from some transactions:

SELECT SUBSTR(name, 1, 40) AS NAME, SUBSTR(value,1,32) AS VALUE FROM GV$LOGSTDBY_STATS;
number of preparers 3
number of appliers 18
maximum SGA for LCR cache 4095
parallel servers in use 24
maximum events recorded 1000000
preserve commit order TRUE
transaction consistency FULL
record skip errors Y
record skip DDL Y
record applied DDL N
record unsupported operations Y
coordinator state IDLE
transactions ready 7
transactions applied 0
coordinator uptime 9646
realtime logmining Y
apply delay 0
Log Miner session ID 1
txns delivered to client 1068651
DML txns delivered 1017135
DDL txns delivered 15
CTAS txns delivered 0
Recursive txns delivered 51501
Rolled back txns seen 23463
LCRs delivered to client 11682189
bytes of redo processed 14475529508
bytes paged out 1482524624
seconds spent in pageout 8922
bytes checkpointed 0
seconds spent in checkpoint 0
bytes rolled back 7500032
seconds spent in rollback 90
seconds system is idle 0

SELECT SID, SERIAL#, SPID, TYPE, HIGH_SCN, STATUS_CODE, STATUS
FROM GV$LOGSTDBY_PROCESS
ORDER BY TYPE, SPID;
ANALYZER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
APPLIER 16116 ORA-16116: no work available
BUILDER 16243 ORA-16243: paging out 4752 bytes of memory to disk
COORDINATOR 16116 ORA-16116: no work available
PREPARER 16127 ORA-16127: stalled waiting for additional transactions to be applied
PREPARER 16127 ORA-16127: stalled waiting for additional transactions to be applied
PREPARER 16127 ORA-16127: stalled waiting for additional transactions to be applied
READER 16127 ORA-16127: stalled waiting for additional transactions to be applied


select xidusn, xidslt, xidsqn, count(*) from system.logmnr_spill$
group by xidusn, xidslt, xidsqn;
996 46 249 254
710 37 838 825
623 3 706 254
478 7 42564 254
765 38 649 824
42 6 415494 3729
264 35 4817 3738

How can we identify the table/SQL to skip & instantiate it later so the logical DB will not being lag far behind.

Thank you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points