Gvsbuffq (gvsbuffq.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script monitors Oracle Streams buffered queues, showing message counts, spillage, and expiration statistics. Essential for Streams performance tuning and capacity management.

The Script

set linesize 200

col INST_ID                format 99              heading 'Inst|ID'
col QUEUE_ID               format a4
col QUEUE_SCHEMA           format a4
col QUEUE_NAME             format a40
col STARTUP_TIME           format a20
col NUM_MSGS               format 999,999,999,999
col SPILL_MSGS             format 999,999,999,999
col CNUM_MSGS              format 999,999,999,999
col CSPILL_MSGS            format 999,999,999,999
col EXPIRED_MSGS           format 999,999,999,999

select INST_ID
--      ,QUEUE_ID
      ,QUEUE_SCHEMA||'.'||QUEUE_NAME             QUEUE_NAME
      ,to_char(STARTUP_TIME,'yyyy-mm-dd hh24:mi:ss') STARTUP_TIME
      ,NUM_MSGS
      ,SPILL_MSGS
      ,CNUM_MSGS
      ,CSPILL_MSGS
      ,EXPIRED_MSGS
from  gv$BUFFERED_QUEUES
;

Parameters

The script prompts for:

• None - this script requires no parameters## Usage

-- Basic usage
@gvsbuffq.sql

Sample Output

Inst
  ID QUEUE_NAME                               STARTUP_TIME                 NUM_MSGS           SPILL_MSGS            CNUM_MSGS          CSPILL_MSGS          EXPIRED_MSGS
---- ---------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------ ------------------
   1 STRMADMIN.CAPTURE_QUEUE                  2025-01-01 08:00:00             12,345                  0          1,234,567                  0                 45
   1 STRMADMIN.APPLY_QUEUE                    2025-01-01 08:00:00              5,678                123            987,654             12,345                 12
   2 STRMADMIN.PROPAGATION_QUEUE              2025-01-01 08:00:00              3,456                 50            456,789              5,678                  8
   2 HR_STREAMS.HR_QUEUE                      2025-01-02 10:30:00                890                  0             67,890                  0                  0