-- ssc.sql - runs a "spot check" of the Streams environment in the current
--           database
--
-- Author: Brian Keating (brianpkeating@yahoo.com)

column capture_name format a30
column apply_name format a30
column state format a30
column value format a30
column bytes format 9999999999
column queue_name format a21
column num_msgs format a8
column spill_msgs format a7
set pagesize 0 heading off feedback off trimspool on
set serveroutput on size 1000000
alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss';

!clear

declare
  ins_name varchar2(20);
  cur_date date;
begin
  select instance_name into ins_name from v$instance;
  select sysdate into cur_date from dual;
  dbms_output.put_line ('********** Streams report for '||ins_name||' on '||cur_date||' **********');
end;
/

select '' from dual;

select 'Capture process  :', a.capture_name capture_name, nvl(b.state,'DOWN') state from sys.streams$_capture_process a, v$streams_capture b where a.capture_name = b.capture_name(+) order by capture_name asc;

select '' from dual;

select 'Propagate process:', a.propagation_name propagation_name, a.status state from dba_propagation a order by propagation_name asc;

select '' from dual;

select 'Apply reader     :', a.apply_name apply_name, nvl(b.state,'DOWN') state from sys.streams$_apply_process a, v$streams_apply_reader b where a.apply_name = b.apply_name(+) order by apply_name asc;

select '' from dual;

select 'Apply server     :', a.apply_name apply_name, nvl(b.state,'DOWN') state from sys.streams$_apply_process a, v$streams_apply_server b where a.apply_name = b.apply_name(+) order by apply_name asc;

select '' from dual;

column state format a13
column latency format a5

select 'Apply coordinator:', a.apply_name apply_name, nvl(b.state,'DOWN') state, 'Latency:', nvl(to_char(round((b.hwm_time - b.hwm_message_create_time) * 86400)),'N/A') latency from sys.streams$_apply_process a, v$streams_apply_coordinator b where a.apply_name = b.apply_name(+) order by apply_name asc;
select '' from dual;

select 'Buffered queue   :', queue_name, 'Messages:', to_char(num_msgs) num_msgs, 'Spill msgs:', to_char(spill_msgs) spill_msgs from v$buffered_queues;
select '' from dual;

declare
  num_rows number;
  num_bytes varchar2(20);
begin
  select count(1) into num_rows from sys.streams$_apply_spill_msgs_part;
  select sum(bytes) into num_bytes from dba_extents where segment_name = 'STREAMS$_APPLY_SPILL_MSGS_PART';
  dbms_output.put_line ('Spill table               :  Rows: '||num_rows||'  Bytes: '||num_bytes);
  dbms_output.put_line ('	');
  select count(1) into num_rows from system.logmnr_restart_ckpt$;
  select sum(bytes) into num_bytes from dba_extents where segment_name = 'LOGMNR_RESTART_CKPT$';
  dbms_output.put_line ('Checkpoint table          :  Rows: '||num_rows||'  Bytes: '||num_bytes);
end;
/

select '' from dual;

select 'Total streams pool memory :  ', value from v$parameter where lower(name) = 'streams_pool_size';
select 'Free streams pool memory  :', bytes from v$sgastat where lower(pool) = 'streams pool' and lower(name) = 'free memory';

select '' from dual;

select '*************** End of report ***************' from dual;