Home » Server Options » Streams & AQ » Capture Latency (Oracle 10.2 , AIX)
Capture Latency [message #403683] Mon, 18 May 2009 01:23 Go to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
I found atleast hour delay between source database and destination database.I ran below queries to find the issue.

It shows DELAY in CAPTURE , and DELAY in ENQUEUE seems to be same. But propagation delay is just 3 seconds.Does that mean, Issue could be with CAPTURE process taking time to scan the REDO logs ?Can someone guide me what to check next etc.. ?


  1  SELECT ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,
  2         ((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,
  3         TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,
  4         TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME
  5    FROM V$STREAMS_CAPTURE
  6*   WHERE CAPTURE_NAME = '&CAPTURE'
MYDB1> /
Enter value for capture: CPON_MYDB1_TT_G1
old   6:   WHERE CAPTURE_NAME = '&CAPTURE'
new   6:   WHERE CAPTURE_NAME = 'CPON_MYDB1_TT_G1'

Latency               Current
     in Seconds Since Process           Event
Seconds   Last Status Time              Creation Time
------- ------------- ----------------- -----------------
   3538             0 01:27:51 05/18/09 00:28:53 05/18/09



COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATE_TIME HEADING 'Event Creation|Time' FORMAT A20
COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20
COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999

SELECT (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS, 
       TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,
       TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,
       ENQUEUE_MESSAGE_NUMBER
  FROM V$STREAMS_CAPTURE
  WHERE CAPTURE_NAME = '&CAPTURE';

Latency
     in Event Creation                            Message
Seconds Time                 Enqueue Time          Number
------- -------------------- -------------------- -------
   3948 00:29:05 05/18/09    01:34:53 05/18/09    #######



MYDB1> COLUMN START_DATE HEADING 'Start Date'
MYDB1> COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999
MYDB1> COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8
MYDB1> COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999
MYDB1> COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
MYDB1> COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8
MYDB1> COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99
MYDB1> 
MYDB1> SELECT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,
  2         s.PROPAGATION_WINDOW, 
  3         TO_CHAR(s.NEXT_TIME, 'HH24:MI:SS MM/DD/YY') NEXT_TIME, 
  4         s.LATENCY,
  5         DECODE(s.SCHEDULE_DISABLED,
  6                  'Y', 'Disabled',
  7                  'N', 'Enabled') SCHEDULE_DISABLED,
  8         PROCESS_NAME,
  9         FAILURES
 10    FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
 11    WHERE p.PROPAGATION_NAME = 'PRTO_MYDB2_TT_G1'
 12    AND p.DESTINATION_DBLINK = 'MYDB2'
 13    AND s.SCHEMA = 'STRMADMIN'
 14    AND s.QNAME = 'C_MYDB1_TT_G1';

                    Duration Next        Latency                   Number of
Start Date        in Seconds Time     in Seconds Status   Process   Failures
----------------- ---------- -------- ---------- -------- -------- ---------
09:13:18 04/27/09                              3 Enabled  J000             0
09:13:18 04/27/09                              3 Enabled  J000             0

Re: Capture Latency [message #403955 is a reply to message #403683] Tue, 19 May 2009 17:09 Go to previous message
varunvir
Messages: 389
Registered: November 2007
Senior Member
The issue can be with capture process at source database or
It can be with apply process at destination database.
query dba_apply to check the status and error message of apply process at target database.
Sometimes it happens due to high CPU usage too.When the
CPU utilization is high,sometimes it results in high
latency.
Regards,
Varun PUnj

[Updated on: Tue, 19 May 2009 17:10]

Report message to a moderator

Previous Topic: why should we enable Supplemental Logging in streams
Next Topic: Slow Perfomance In Oracle Streams
Goto Forum:
  


Current Time: Thu Mar 28 04:29:14 CDT 2024