Monitoring Oracle Streams

Monitoring Oracle Streams is an important task in replication environment. As discussed in my other posts, Oracle Streams environment consists basically of Capture Process, Propagation, Apply Process and Queues. Thus breaking down into these components and looking at their statistics, performance or errors can help in saving lot of efforts in long term.

First of all, information about Streams environment can be obtained with help of DBMS_STREAMS_ADVISOR_ADM package. This can gather information about the Oracle Streams topology and performance. To view this information below views can be queried:

• DBA_STREAMS_TP_COMPONENT contains information about each Oracle Streams component at each database.
  
• DBA_STREAMS_TP_COMPONENT_LINK contains information about how messages flow between Oracle Streams components.
  
• DBA_STREAMS_TP_COMPONENT_STAT contains statistics about each Oracle Streams component.
  
• DBA_STREAMS_TP_DATABASE contains information about each database that contains Oracle Streams components.
  
• DBA_STREAMS_TP_PATH_BOTTLENECK contains information about Oracle Streams components that might be slowing down the flow of a stream.
  
• DBA_STREAMS_TP_PATH_STAT contains statistics about each stream path that exists in the Oracle Streams topology.

Tracking LCRs:

1. Connect as Stream Administrator

2. Begin message tracking

BEGIN
  DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING(
    tracking_label => 'TRACK_LCRS',
    actions        => DBMS_STREAMS_ADM.ACTION_MEMORY);
END;
/

Confirm using:

SET SERVEROUTPUT ON SIZE 4000
DECLARE
  tracking_label  VARCHAR2(4000);
BEGIN
  tracking_label := DBMS_STREAMS_ADM.GET_MESSAGE_TRACKING();
  DBMS_OUTPUT.PUT_LINE('Tracking Label: ' || tracking_label);
END;
/

3. Using below query, all LCRs with label TRACK_LCRS can be queries:

COLUMN COMPONENT_NAME HEADING 'Component|Name' FORMAT A10
COLUMN COMPONENT_TYPE HEADING 'Component|Type' FORMAT A12
COLUMN ACTION HEADING 'Action' FORMAT A11
COLUMN SOURCE_DATABASE_NAME HEADING 'Source|Database' FORMAT A10
COLUMN OBJECT_OWNER HEADING 'Object|Owner' FORMAT A6
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
COLUMN COMMAND_TYPE HEADING 'Command|Type' FORMAT A7
 
SELECT COMPONENT_NAME,
       COMPONENT_TYPE,
       ACTION,
       SOURCE_DATABASE_NAME,
       OBJECT_OWNER,
       OBJECT_NAME,
       COMMAND_TYPE
   FROM V$STREAMS_MESSAGE_TRACKING
   WHERE TRACKING_LABEL='TRACK_LCRS';

4. Stop message tracking

BEGIN
  DBMS_STREAMS_ADM.SET_MESSAGE_TRACKING(
    tracking_label => NULL,
    actions        => DBMS_STREAMS_ADM.ACTION_MEMORY);
END;
/

Useful Queries:

-- Display ANYDATA Queue
COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN NAME HEADING 'Queue Name' FORMAT A28
COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22
COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT
  FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
  WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND
        q.QUEUE_TABLE = t.QUEUE_TABLE AND
        q.OWNER       = t.OWNER;
       
       
-- Number of messages in each buffered queue

COLUMN QUEUE_SCHEMA HEADING 'Queue Owner' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN MEM_MSG HEADING 'Messages|in Memory' FORMAT 99999999
COLUMN SPILL_MSGS HEADING 'Messages|Spilled' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Total Messages|in Buffered Queue' FORMAT 99999999

SELECT QUEUE_SCHEMA,
       QUEUE_NAME,
       (NUM_MSGS - SPILL_MSGS) MEM_MSG,
       SPILL_MSGS,
       NUM_MSGS
  FROM V$BUFFERED_QUEUES;
 
-- View CAPTURE processes
COLUMN SENDER_NAME HEADING 'Capture|Process' FORMAT A13
COLUMN SENDER_ADDRESS HEADING 'Sender Queue' FORMAT A27
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN CNUM_MSGS HEADING 'Number|of LCRs|Enqueued' FORMAT 99999999
COLUMN LAST_ENQUEUED_MSG HEADING 'Last|Enqueued|LCR' FORMAT 9999999

SELECT SENDER_NAME,
       SENDER_ADDRESS,
       QUEUE_NAME,       
       CNUM_MSGS,
       LAST_ENQUEUED_MSG
  FROM V$BUFFERED_PUBLISHERS;

-- Performance Statistics of Propagations

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A13
COLUMN DBLINK HEADING 'Database|Link' FORMAT A9
COLUMN ELAPSED_DEQUEUE_TIME HEADING 'Dequeue|Time' FORMAT 99999999.99
COLUMN ELAPSED_PICKLE_TIME HEADING 'Pickle|Time' FORMAT 99999999.99
COLUMN ELAPSED_PROPAGATION_TIME HEADING 'Propagation|Time' FORMAT 99999999.99

SELECT p.PROPAGATION_NAME,
       s.QUEUE_NAME,
       s.DBLINK,
       (s.ELAPSED_DEQUEUE_TIME / 100) ELAPSED_DEQUEUE_TIME,
       (s.ELAPSED_PICKLE_TIME / 100) ELAPSED_PICKLE_TIME,
       (s.ELAPSED_PROPAGATION_TIME / 100) ELAPSED_PROPAGATION_TIME
  FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
  WHERE p.SOURCE_QUEUE_OWNER      = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME       = s.QUEUE_NAME AND
        p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
        p.DESTINATION_QUEUE_NAME  = s.DST_QUEUE_NAME;

-- Apply Errors
SET PAGES 1000 LINES 150
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A10
COLUMN SOURCE_DB HEADING 'Source|Database' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A50
COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999

SELECT APPLY_NAME,
       substr(SOURCE_DATABASE,1,6) SOURCE_DB,
       LOCAL_TRANSACTION_ID,
       ERROR_NUMBER,
       ERROR_MESSAGE,
       MESSAGE_COUNT
FROM DBA_APPLY_ERROR;