This report documents the memory allocation behavior of Oracle's DBMS_OUTPUT package, tested on Oracle 23.26 Free.

Test Environment

• Oracle Version: Oracle Database 23.26 Free
• Container: container-registry.oracle.com/database/free:23.26.0.0-lite
• Test Date: December 2025

Key Findings

1. Lazy Allocation

Oracle does NOT pre-allocate buffer memory at ENABLE() time. Memory is allocated lazily as data is written.

Conclusion: Oracle grows the buffer incrementally with ~10-15% overhead.

2. Per-Line Memory Overhead

Oracle has significant per-line overhead, suggesting a pointer-based or linked-list internal structure.

Conclusion: Oracle uses approximately 50-64 bytes overhead per line. This is far more than a simple 2-byte length prefix would require.

3. Per-Line Overhead NOT Counted in Buffer Limit

The internal per-line overhead does NOT count toward the user-specified buffer size.

DBMS_OUTPUT.ENABLE(2000);  -- 2000-byte buffer

-- If overhead counted: 2000 / 64 = ~31 lines max
-- If overhead NOT counted: 2000 1-byte lines should fit

FOR i IN 1..2000 LOOP
    DBMS_OUTPUT.PUT_LINE('X');  -- All 2000 succeed!
END LOOP;

Conclusion: Oracle's buffer_size parameter limits only the content bytes, not the internal storage overhead. Actual memory used can be significantly higher than the buffer_size parameter.

4. Embedded Characters

Oracle preserves embedded special characters in line content:

• CHR(0) (null byte): Preserved in content, length correctly reported as 7 for 'AAA' || CHR(0) || 'BBB'
• CHR(10) (newline): Preserved in content, does NOT split into multiple lines

-- This creates ONE line, not two
DBMS_OUTPUT.PUT_LINE('AAA' || CHR(10) || 'BBB');
-- GET_LINE returns: 'AAA\nBBB' (length 7, single line)

5. Buffer Size Limits

Oracle's documented behavior:

• Minimum buffer size: 2,000 bytes
• Maximum buffer size: Unlimited (with ENABLE(NULL))
• Maximum line length: 32,767 bytes (fits in 16-bit signed integer)

Implications for IvorySQL Implementation

Memory Efficiency Comparison

For 1-byte lines (worst case):

IvorySQL's length-prefixed approach is ~17-21x more memory efficient than Oracle for small lines.

Design Decisions

1. Pre-allocation vs Lazy Growth: IvorySQL currently pre-allocates for simplicity. Oracle grows lazily, which is more memory efficient but adds complexity.

2. Length-Prefixed Storage: Using 2-byte length prefix + data is significantly more efficient than Oracle's apparent pointer-based structure.

3. Capacity Multiplier: With length-prefixed storage:

   • 1-byte line needs 3 bytes (2 prefix + 1 data)
   • Multiplier of 3x handles worst case
   • This is still far more efficient than Oracle's ~50-64 bytes per line

Test Scripts

Lazy Allocation Test

DECLARE
    v_mem_start NUMBER;
    v_mem NUMBER;
    v_chunk VARCHAR2(1000) := RPAD('X', 1000, 'X');
BEGIN
    SELECT value INTO v_mem_start
    FROM v$mystat m, v$statname n
    WHERE m.statistic# = n.statistic# AND n.name = 'session pga memory';

    DBMS_OUTPUT.ENABLE(1000000);
    -- Check memory at various data sizes
    FOR i IN 1..100 LOOP DBMS_OUTPUT.PUT_LINE(v_chunk); END LOOP;

    SELECT value INTO v_mem
    FROM v$mystat m, v$statname n
    WHERE m.statistic# = n.statistic# AND n.name = 'session pga memory';

    -- Report difference
    DBMS_OUTPUT.PUT_LINE('PGA diff: ' || (v_mem - v_mem_start));
END;
/

Per-Line Overhead Test

DECLARE
    v_mem_start NUMBER;
    v_mem NUMBER;
BEGIN
    SELECT value INTO v_mem_start
    FROM v$mystat m, v$statname n
    WHERE m.statistic# = n.statistic# AND n.name = 'session pga memory';

    DBMS_OUTPUT.ENABLE(1000000);

    -- Write many small lines
    FOR i IN 1..10000 LOOP
        DBMS_OUTPUT.PUT_LINE('X');
    END LOOP;

    SELECT value INTO v_mem
    FROM v$mystat m, v$statname n
    WHERE m.statistic# = n.statistic# AND n.name = 'session pga memory';

    -- Calculate per-line overhead
    -- (v_mem - v_mem_start) / 10000 = bytes per line
END;
/

Embedded Newline Test

DECLARE
    v_line VARCHAR2(32767);
    v_status INTEGER;
BEGIN
    DBMS_OUTPUT.ENABLE(10000);
    DBMS_OUTPUT.PUT_LINE('AAA' || CHR(10) || 'BBB');
    DBMS_OUTPUT.PUT_LINE('CCC');

    -- Count lines returned
    LOOP
        DBMS_OUTPUT.GET_LINE(v_line, v_status);
        EXIT WHEN v_status != 0;
        -- Reports 2 lines, not 3
    END LOOP;
END;
/

Summary

Oracle's DBMS_OUTPUT implementation:

1. Uses lazy memory allocation (grows as needed)
2. Has high per-line overhead (~50-64 bytes), suggesting pointer-based storage
3. Preserves embedded null bytes and newlines in line content
4. Line boundaries are NOT determined by newline characters

IvorySQL's implementation with length-prefixed contiguous ring buffer:

1. Pre-allocates for simplicity (buffer_size * 3)
2. Has low per-line overhead (2 bytes length prefix)
3. Correctly preserves embedded special characters
4. Is significantly more memory efficient than Oracle for small lines