eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS._DBA_STREAMS_COMPONENT_STAT

Source

View Text - Preformatted


SELECT v.COMPONENT_NAME,
       global_name COMPONENT_DB,
       v.COMPONENT_TYPE,
       sysdate STAT_TIME,
       v.COUNT1,
       v.COUNT2,
       v.COUNT3,
       0 COUNT4,
       v.LATENCY,
  decode(v.STATUS, 'ENABLED',      0,
                 'ABORTED',      1,
                 'DISABLED',     2,
                 'FLOW CONTROL', 3,
                 'VALID',        0,
                 'INVALID',      1,
                 'N/A',          2, 0),
       0, 0, NULL, to_date(NULL, '')
FROM global_name,
 ( -- CAPTURE
   SELECT
     c.capture_name                       as COMPONENT_NAME,
     1                                    as COMPONENT_TYPE,
     -- COUNT1: TOTAL MESSAGES CAPTURED
     nvl(vcap.total_messages_captured, 0) as COUNT1,
     -- COUNT2: TOTAL MESSAGES ENQUEUED
     nvl(vcap.total_messages_enqueued, 0) as COUNT2,
     -- COUNT3: TOTAL BYTES SENT
     nvl(vcap.bytessent, 0)               as COUNT3,
     -- LATENCY: SECONDS
     nvl((vcap.AVAILABLE_MESSAGE_CREATE_TIME-vcap.CAPTURE_MESSAGE_CREATE_TIME)
         * 86400, -1)                     as LATENCY,
     CASE WHEN (vcap.state = 'PAUSED FOR FLOW CONTROL')
          THEN 'FLOW CONTROL'
          ELSE decode(c.status, 1, 'DISABLED',
                                2, 'ENABLED',
                                4, 'ABORTED', 'UNKNOWN')
     END                                  as STATUS
     -- OPTIMIZE: Replace dba_capture with sys.streams$_capture_process
     FROM sys.streams$_capture_process c,
      ( SELECT cap.capture_name,
               cap.total_messages_captured,
               cap.total_messages_enqueued,
               cap.available_message_create_time,
               cap.capture_message_create_time,
               cap.state,
               sess.value as bytessent
        FROM sys."_GV$SXGG_CAPTURE" cap, gv$statname stat, gv$sesstat sess
        WHERE cap.sid = sess.sid AND sess.statistic# = stat.statistic# AND
              stat.name = 'bytes sent via SQL*Net to dblink') vcap
   WHERE c.capture_name = vcap.capture_name (+)
   UNION
   -- APPLY
   SELECT a.apply_name                    as COMPONENT_NAME,
          4                               as COMPONENT_TYPE,
          -- COUNT1: TOTAL MESSAGES APPLIED
          nvl(aps.COUNT1, 0)              as COUNT1,
          -- COUNT2: TOTAL TRANSACTIONS APPLIED
          nvl(apc.total_applied, 0)       as COUNT2,
          0                               as COUNT3,
          -- LATENCY: SECONDS
          CASE WHEN aps.state != 'IDLE' THEN
                 nvl((aps.apply_time - aps.create_time)*86400, -1)
               WHEN apc.state != 'IDLE' THEN
                 nvl((apc.apply_time - apc.create_time)*86400, -1)
               WHEN apr.state != 'IDLE' THEN
                 nvl((apr.apply_time - apr.create_time)*86400, -1)
               ELSE 0
          END                             as LATENCY,
          decode(a.status, 1, 'DISABLED',
                           2, 'ENABLED',
                           4, 'ABORTED', 'UNKNOWN')
                                          as STATUS
   -- OPTIMIZE: Replace dba_apply with sys.streams$_apply_process
   -- Calculate apply latency in order of SERVER, COORDINATOR, READER
   FROM sys.streams$_apply_process a,
        ( SELECT apply_name,
                 state,
                 apply_time,
                 max_create_time as create_time,
                 count1
          FROM ( SELECT apply_name,
                        state,
                        apply_time,
                        applied_message_create_time,
                        MAX(applied_message_create_time)
                          OVER (PARTITION BY apply_name)
                          as max_create_time,
                        SUM(total_messages_applied)
                          OVER (PARTITION BY apply_name)
                          as count1
                 FROM sys."_GV$SXGG_APPLY_SERVER")
                 -- There may be many apply slaves, pick the first one for
                 -- apply_time, apply_message_create_time
          WHERE ROWNUM <= 1) aps,
          ( SELECT c.apply_name,
                 state,
                 -- If XOut use hwm_time else use lwm_time
                 CASE WHEN (bitand(p.flags, 256)) = 256
                      THEN c.hwm_time
                      ELSE c.lwm_time
                 END                          as apply_time,
                 CASE WHEN (bitand(p.flags, 256)) = 256
                      THEN hwm_message_create_time
                      ELSE lwm_message_create_time
                 END                          as create_time,
                 total_applied
          FROM sys."_GV$SXGG_APPLY_COORDINATOR" c,
               sys.streams$_apply_process p
          WHERE p.apply_name = c.apply_name) apc,
        ( SELECT apply_name,
                 state,
                 dequeue_time                 as apply_time,
                 dequeued_message_create_time as create_time
          FROM sys."_GV$SXGG_APPLY_READER" ) apr
   WHERE a.apply_name = apc.apply_name (+) AND
         apc.apply_name = apr.apply_name (+) AND
         apr.apply_name = aps.apply_name (+) AND
         a.apply_name not in
              (select 'OGG$'||extract_name from gv$goldengate_capture)
   UNION
   -- EXTRACT
   SELECT ggc.extract_name AS COMPONENT_NAME,
          6                    AS COMPONENT_TYPE,
          nvl(ggc.total_messages_sent, 0) as COUNT1,
          nvl(ggc.bytes_sent, 0)          as COUNT2,
          0                               as COUNT3,
          nvl((ggc.available_message_create_time-ggc.last_sent_message_create_time)
               * 86400, -1)               as LATENCY,
          decode(p.status, 1, 'DISABLED',
                           2, 'ENABLED',
                           4, 'ABORTED', 'UNKNOWN')
                                          as STATUS
       FROM gv$goldengate_capture ggc,
            sys.streams$_apply_process p
       WHERE p.apply_name = 'OGG$'||ggc.extract_name
   UNION
   -- REPLICAT
   SELECT LTRIM(ggrec.apply_name, 'OGG$')   as COMPONENT_NAME,
          7                               as COMPONENT_TYPE,
          nvl(ggrec.total_messages_received, 0) as COUNT1,
          nvl(ggar.bytes_received, 0)     as COUNT2,
          0                               as COUNT3,
          0                               as LATENCY,
          decode(p.status, 1, 'DISABLED',
                           2, 'ENABLED',
                           4, 'ABORTED', 'UNKNOWN')
                                          as STATUS
      FROM sys.streams$_apply_process p, gv$gg_apply_receiver ggrec,
           gv$gg_apply_reader ggar
      WHERE p.apply_name = ggar.apply_name and
            ggar.apply_name = ggrec.apply_name
   UNION
   -- QUEUE
   SELECT ('"'||q.queue_schema||'"."'||q.queue_name||'"')
                                          as COMPONENT_NAME,
          5                               as COMPONENT_TYPE,
          -- COUNT1: CUMULATIVE MESSAGES ENQUEUED
          q.cnum_msgs                     as COUNT1,
          -- COUNT2: CUMULATIVE MESSAGES SPILLED
          q.cspill_msgs                   as COUNT2,
          -- COUNT3: CURRENT QUEUE SIZE
          q.num_msgs                      as COUNT3,
          0                               as LATENCY,
          decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID')
                                          as STATUS
   -- OPTIMIZE: Replace dba_objects with sys.obj$ and sys.user$
   FROM gv$buffered_queues q, sys.obj$ o, sys.user$ u
   WHERE q.queue_schema = u.name AND
         q.queue_id = o.obj# AND
         o.owner# = u.user#
   UNION
   -- PROPAGATION SENDER
   SELECT ('"'||ps.queue_schema||'"."'||ps.queue_name||'"=>'||
           CASE WHEN ps.dblink IS NOT NULL AND
                     (ps.dst_queue_schema IS NULL OR ps.dst_queue_name IS NULL)
                THEN ps.dblink
                ELSE ('"'||ps.dst_queue_schema||'"."'||ps.dst_queue_name||
                      '"@'||ps.dst_database_name)
           END)                           as COMPONENT_NAME,
          2                               as COMPONENT_TYPE,
          -- COUNT1: TOTAL MESSAGES SENT
          ps.total_msgs                   as COUNT1,
          -- COUNT1: TOTAL BYTES SENT
          ps.total_bytes                  as COUNT2,
          0                               as COUNT3,
          -- LATENCY: SECONDS
          ps.last_lcr_latency             as LATENCY,
          CASE WHEN (regexp_instr(s.last_error_msg,
                    '.*flow control.*', 1, 1, 0, 'i') > 0)
               -- ORA-25307: Enqueue rate too high, flow control enabled
               -- Subscribers could not keep pace with the enqueue rate,
               -- propagation is in flow control
                    THEN 'FLOW CONTROL'
               WHEN (ps.schedule_status = 'SCHEDULE DISABLED')
                    THEN 'DISABLED'
               WHEN (ps.schedule_status = 'PROPAGATION UNSCHEDULED')
                    THEN 'ABORTED'
               WHEN (j.enabled != 'TRUE' AND j.retry_count >= 16)
                 -- (dqs.schedule_disabled = 'Y' AND dqs.failures >= 16)
                    THEN 'ABORTED'
               ELSE ps.schedule_status
          END                             as STATUS
   FROM gv$propagation_sender ps,
        -- OPTIMIZE: Replace DBA_QUEUE_SCHEDULES dqs with base tables q, s, j
        system.aq$_queues q, sys.aq$_schedules s, sys.dba_scheduler_jobs j
   WHERE ps.dst_database_name IS NOT NULL AND
         ps.queue_id = q.eventid AND ps.queue_name = q.name AND
         q.oid = s.oid (+) AND s.job_name = j.job_name (+) ) v

View Text - HTML Formatted

SELECT V.COMPONENT_NAME
, GLOBAL_NAME COMPONENT_DB
, V.COMPONENT_TYPE
, SYSDATE STAT_TIME
, V.COUNT1
, V.COUNT2
, V.COUNT3
, 0 COUNT4
, V.LATENCY
, DECODE(V.STATUS
, 'ENABLED'
, 0
, 'ABORTED'
, 1
, 'DISABLED'
, 2
, 'FLOW CONTROL'
, 3
, 'VALID'
, 0
, 'INVALID'
, 1
, 'N/A'
, 2
, 0)
, 0
, 0
, NULL
, TO_DATE(NULL
, '') FROM GLOBAL_NAME
, ( -- CAPTURE SELECT C.CAPTURE_NAME AS COMPONENT_NAME
, 1 AS COMPONENT_TYPE
, -- COUNT1: TOTAL MESSAGES CAPTURED NVL(VCAP.TOTAL_MESSAGES_CAPTURED
, 0) AS COUNT1
, -- COUNT2: TOTAL MESSAGES ENQUEUED NVL(VCAP.TOTAL_MESSAGES_ENQUEUED
, 0) AS COUNT2
, -- COUNT3: TOTAL BYTES SENT NVL(VCAP.BYTESSENT
, 0) AS COUNT3
, -- LATENCY: SECONDS NVL((VCAP.AVAILABLE_MESSAGE_CREATE_TIME-VCAP.CAPTURE_MESSAGE_CREATE_TIME) * 86400
, -1) AS LATENCY
, CASE WHEN (VCAP.STATE = 'PAUSED FOR FLOW CONTROL') THEN 'FLOW CONTROL' ELSE DECODE(C.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 4
, 'ABORTED'
, 'UNKNOWN') END AS STATUS -- OPTIMIZE: REPLACE DBA_CAPTURE WITH SYS.STREAMS$_CAPTURE_PROCESS
FROM SYS.STREAMS$_CAPTURE_PROCESS C
, ( SELECT CAP.CAPTURE_NAME
, CAP.TOTAL_MESSAGES_CAPTURED
, CAP.TOTAL_MESSAGES_ENQUEUED
, CAP.AVAILABLE_MESSAGE_CREATE_TIME
, CAP.CAPTURE_MESSAGE_CREATE_TIME
, CAP.STATE
, SESS.VALUE AS BYTESSENT
FROM SYS."_GV$SXGG_CAPTURE" CAP
, GV$STATNAME STAT
, GV$SESSTAT SESS
WHERE CAP.SID = SESS.SID
AND SESS.STATISTIC# = STAT.STATISTIC# AND STAT.NAME = 'BYTES SENT VIA SQL*NET TO DBLINK') VCAP
WHERE C.CAPTURE_NAME = VCAP.CAPTURE_NAME (+) UNION -- APPLY SELECT A.APPLY_NAME AS COMPONENT_NAME
, 4 AS COMPONENT_TYPE
, -- COUNT1: TOTAL MESSAGES APPLIED NVL(APS.COUNT1
, 0) AS COUNT1
, -- COUNT2: TOTAL TRANSACTIONS APPLIED NVL(APC.TOTAL_APPLIED
, 0) AS COUNT2
, 0 AS COUNT3
, -- LATENCY: SECONDS CASE WHEN APS.STATE != 'IDLE' THEN NVL((APS.APPLY_TIME - APS.CREATE_TIME)*86400
, -1) WHEN APC.STATE != 'IDLE' THEN NVL((APC.APPLY_TIME - APC.CREATE_TIME)*86400
, -1) WHEN APR.STATE != 'IDLE' THEN NVL((APR.APPLY_TIME - APR.CREATE_TIME)*86400
, -1) ELSE 0 END AS LATENCY
, DECODE(A.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 4
, 'ABORTED'
, 'UNKNOWN') AS STATUS -- OPTIMIZE: REPLACE DBA_APPLY WITH SYS.STREAMS$_APPLY_PROCESS -- CALCULATE APPLY LATENCY IN ORDER OF SERVER
, COORDINATOR
, READER
FROM SYS.STREAMS$_APPLY_PROCESS A
, ( SELECT APPLY_NAME
, STATE
, APPLY_TIME
, MAX_CREATE_TIME AS CREATE_TIME
, COUNT1
FROM ( SELECT APPLY_NAME
, STATE
, APPLY_TIME
, APPLIED_MESSAGE_CREATE_TIME
, MAX(APPLIED_MESSAGE_CREATE_TIME) OVER (PARTITION BY APPLY_NAME) AS MAX_CREATE_TIME
, SUM(TOTAL_MESSAGES_APPLIED) OVER (PARTITION BY APPLY_NAME) AS COUNT1
FROM SYS."_GV$SXGG_APPLY_SERVER") -- THERE MAY BE MANY APPLY SLAVES
, PICK THE FIRST ONE FOR -- APPLY_TIME
, APPLY_MESSAGE_CREATE_TIME
WHERE ROWNUM <= 1) APS
, ( SELECT C.APPLY_NAME
, STATE
, -- IF XOUT USE HWM_TIME ELSE USE LWM_TIME CASE WHEN (BITAND(P.FLAGS
, 256)) = 256 THEN C.HWM_TIME ELSE C.LWM_TIME END AS APPLY_TIME
, CASE WHEN (BITAND(P.FLAGS
, 256)) = 256 THEN HWM_MESSAGE_CREATE_TIME ELSE LWM_MESSAGE_CREATE_TIME END AS CREATE_TIME
, TOTAL_APPLIED
FROM SYS."_GV$SXGG_APPLY_COORDINATOR" C
, SYS.STREAMS$_APPLY_PROCESS P
WHERE P.APPLY_NAME = C.APPLY_NAME) APC
, ( SELECT APPLY_NAME
, STATE
, DEQUEUE_TIME AS APPLY_TIME
, DEQUEUED_MESSAGE_CREATE_TIME AS CREATE_TIME
FROM SYS."_GV$SXGG_APPLY_READER" ) APR
WHERE A.APPLY_NAME = APC.APPLY_NAME (+) AND APC.APPLY_NAME = APR.APPLY_NAME (+) AND APR.APPLY_NAME = APS.APPLY_NAME (+) AND A.APPLY_NAME NOT IN (SELECT 'OGG$'||EXTRACT_NAME
FROM GV$GOLDENGATE_CAPTURE) UNION -- EXTRACT SELECT GGC.EXTRACT_NAME AS COMPONENT_NAME
, 6 AS COMPONENT_TYPE
, NVL(GGC.TOTAL_MESSAGES_SENT
, 0) AS COUNT1
, NVL(GGC.BYTES_SENT
, 0) AS COUNT2
, 0 AS COUNT3
, NVL((GGC.AVAILABLE_MESSAGE_CREATE_TIME-GGC.LAST_SENT_MESSAGE_CREATE_TIME) * 86400
, -1) AS LATENCY
, DECODE(P.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 4
, 'ABORTED'
, 'UNKNOWN') AS STATUS
FROM GV$GOLDENGATE_CAPTURE GGC
, SYS.STREAMS$_APPLY_PROCESS P
WHERE P.APPLY_NAME = 'OGG$'||GGC.EXTRACT_NAME UNION -- REPLICAT SELECT LTRIM(GGREC.APPLY_NAME
, 'OGG$') AS COMPONENT_NAME
, 7 AS COMPONENT_TYPE
, NVL(GGREC.TOTAL_MESSAGES_RECEIVED
, 0) AS COUNT1
, NVL(GGAR.BYTES_RECEIVED
, 0) AS COUNT2
, 0 AS COUNT3
, 0 AS LATENCY
, DECODE(P.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 4
, 'ABORTED'
, 'UNKNOWN') AS STATUS
FROM SYS.STREAMS$_APPLY_PROCESS P
, GV$GG_APPLY_RECEIVER GGREC
, GV$GG_APPLY_READER GGAR
WHERE P.APPLY_NAME = GGAR.APPLY_NAME AND GGAR.APPLY_NAME = GGREC.APPLY_NAME UNION -- QUEUE SELECT ('"'||Q.QUEUE_SCHEMA||'"."'||Q.QUEUE_NAME||'"') AS COMPONENT_NAME
, 5 AS COMPONENT_TYPE
, -- COUNT1: CUMULATIVE MESSAGES ENQUEUED Q.CNUM_MSGS AS COUNT1
, -- COUNT2: CUMULATIVE MESSAGES SPILLED Q.CSPILL_MSGS AS COUNT2
, -- COUNT3: CURRENT QUEUE SIZE Q.NUM_MSGS AS COUNT3
, 0 AS LATENCY
, DECODE(O.STATUS
, 0
, 'N/A'
, 1
, 'VALID'
, 'INVALID') AS STATUS -- OPTIMIZE: REPLACE DBA_OBJECTS WITH SYS.OBJ$
AND SYS.USER$
FROM GV$BUFFERED_QUEUES Q
, SYS.OBJ$ O
, SYS.USER$ U
WHERE Q.QUEUE_SCHEMA = U.NAME AND Q.QUEUE_ID = O.OBJ# AND O.OWNER# = U.USER# UNION -- PROPAGATION SENDER SELECT ('"'||PS.QUEUE_SCHEMA||'"."'||PS.QUEUE_NAME||'"=>'|| CASE WHEN PS.DBLINK IS NOT NULL AND (PS.DST_QUEUE_SCHEMA IS NULL OR PS.DST_QUEUE_NAME IS NULL) THEN PS.DBLINK ELSE ('"'||PS.DST_QUEUE_SCHEMA||'"."'||PS.DST_QUEUE_NAME|| '"@'||PS.DST_DATABASE_NAME) END) AS COMPONENT_NAME
, 2 AS COMPONENT_TYPE
, -- COUNT1: TOTAL MESSAGES SENT PS.TOTAL_MSGS AS COUNT1
, -- COUNT1: TOTAL BYTES SENT PS.TOTAL_BYTES AS COUNT2
, 0 AS COUNT3
, -- LATENCY: SECONDS PS.LAST_LCR_LATENCY AS LATENCY
, CASE WHEN (REGEXP_INSTR(S.LAST_ERROR_MSG
, '.*FLOW CONTROL.*'
, 1
, 1
, 0
, 'I') > 0) -- ORA-25307: ENQUEUE RATE TOO HIGH
, FLOW CONTROL ENABLED -- SUBSCRIBERS COULD NOT KEEP PACE WITH THE ENQUEUE RATE
, -- PROPAGATION IS IN FLOW CONTROL THEN 'FLOW CONTROL' WHEN (PS.SCHEDULE_STATUS = 'SCHEDULE DISABLED') THEN 'DISABLED' WHEN (PS.SCHEDULE_STATUS = 'PROPAGATION UNSCHEDULED') THEN 'ABORTED' WHEN (J.ENABLED != 'TRUE'
AND J.RETRY_COUNT >= 16) -- (DQS.SCHEDULE_DISABLED = 'Y'
AND DQS.FAILURES >= 16) THEN 'ABORTED' ELSE PS.SCHEDULE_STATUS END AS STATUS
FROM GV$PROPAGATION_SENDER PS
, -- OPTIMIZE: REPLACE DBA_QUEUE_SCHEDULES DQS WITH BASE TABLES Q
, S
, J SYSTEM.AQ$_QUEUES Q
, SYS.AQ$_SCHEDULES S
, SYS.DBA_SCHEDULER_JOBS J
WHERE PS.DST_DATABASE_NAME IS NOT NULL AND PS.QUEUE_ID = Q.EVENTID
AND PS.QUEUE_NAME = Q.NAME AND Q.OID = S.OID (+)
AND S.JOB_NAME = J.JOB_NAME (+) ) V