--advice from SR SR 3-28241408751 : High cpu, long latency with Xstream Out
--created 2022-02-02 sgere

select sysdate from dual;

-- If the value returned is.90 or greater, then consider increasing the Streams pool size.
SELECT TOTAL_MEMORY_ALLOCATED/CURRENT_SIZE FROM V$STREAMS_POOL_STATISTICS;

--streams_pool_advice
SELECT streams_pool_size_for_estimate, streams_pool_size_factor, estd_spill_count, to_char(sysdate,'dd-mon-yyyy HH24:MI:SS') "Current Time" FROM V$STREAMS_POOL_ADVICE;

select * from v$sgastat where pool like '%streams%' and BYTES > 1000 order by BYTES desc;

col MEMORY_ALLOCATED_KNLASG HEADING 'Used MB'
col STRMPOOL_SIZE_KNLASG HEADING 'Total Allocated MB'
select MEMORY_ALLOCATED_KNLASG/1024/1024 as MEMORY_ALLOCATED_KNLASG , STRMPOOL_SIZE_KNLASG/1024/1024 as STRMPOOL_SIZE_KNLASG from x$knlasg;


--Regarding outbound server capture & apply, use the max_sga_size capture and apply parameter to control the amount of SGA memory allocated specifically to the process. The streams pool will need to be sized to support both processes.
--If USED value is equal to or almost equal to ALLOCATED value, increase max_sga_size for the process and streams pool size accordingly.

SELECT APPLY_NAME AS APP,
SGA_USED/(1024*1024) AS USED,
SGA_ALLOCATED/(1024*1024) AS ALLOCATED,
TOTAL_MESSAGES_DEQUEUED AS DEQUEUED,
TOTAL_MESSAGES_SPILLED AS SPILLED
FROM V$XSTREAM_APPLY_READER;

SELECT CAPTURE_NAME AS CAP,
SGA_USED/(1024*1024) AS USED,
SGA_ALLOCATED/(1024*1024) AS ALLOCATED,
TOTAL_MESSAGES_CAPTURED AS CAPTURED,
TOTAL_MESSAGES_ENQUEUED AS ENQUEUED
FROM V$XSTREAM_CAPTURE;

prompt @xstream_streams_pool.sql
