3 Replies Latest reply: May 7, 2010 2:13 AM by 769627 RSS

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

    750288
      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.