Home » Server Options » Data Guard » [Logical Standby] Which table/SQL caused paging-out (Oracle 10.2.0.4 Solaris 10 Sparc 64bit)
[Logical Standby] Which table/SQL caused paging-out [message #454732] Fri, 07 May 2010 02:14
nuclear0508
Messages: 2
Registered: January 2010
Junior Member
Hi,
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 82394
realtime logmining Y
apply delay 0
Log Miner session ID 1
txns delivered to client 1147717
DML txns delivered 1063179
DDL txns delivered 16
CTAS txns delivered 0
Recursive txns delivered 84522
Rolled back txns seen 24258
LCRs delivered to client 20626986
bytes of redo processed 21114913464
bytes paged out 8355049664
seconds spent in pageout 78714
bytes checkpointed 0
seconds spent in checkpoint 0
bytes rolled back 10356064
seconds spent in rollback 172
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 2816 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 8956
710 37 838 1157
623 3 706 8957
478 7 42564 8955
765 38 649 1157
42 6 415494 11586
264 35 4817 11584

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

Thank you.
Previous Topic: Graceful Failover and Failback Procedures
Next Topic: switchover from physical standby to primary fails
Goto Forum:
  


Current Time: Sun May 24 16:53:55 CDT 2020