Oracle DBMS_OUTPUT Memory Allocation Analysis #29

Activity

1. rophy

   added

   bugSomething isn't workingSomething isn't working

   on Dec 25, 2025
2. rophy commented on Dec 28, 2025

   

   rophy

   on Dec 28, 2025

   Last edited by rophy

   OwnerAuthor

   More actions

   Oracle DBMS_OUTPUT Memory Deallocation Testing

   Follow-up testing on Oracle 23.26 Free to investigate memory deallocation behavior.

   Test Environment

   • Oracle Database 23.26 Free (container)
   • Memory config: sga_max_size=1536MB, pga_aggregate_limit=2048MB

   Testing Methodology

   How memory was measured:

   PGA memory was tracked using Oracle's v$mystat view:

   SELECT value/1024/1024 AS pga_mb
   FROM v$mystat m, v$statname n
   WHERE m.statistic# = n.statistic# AND n.name = 'session pga memory';

   How tests were performed:

   1. Created a temporary table to store results (since using DBMS_OUTPUT for reporting would interfere with measurements)
   2. Recorded baseline PGA before any DBMS_OUTPUT operations
   3. Called DBMS_OUTPUT.ENABLE(NULL) for unlimited buffer
   4. Wrote data using PUT_LINE in batches (e.g., 5120 lines × 10KB = ~51MB per batch)
   5. Recorded PGA after each batch
   6. Read all lines using GET_LINE in a loop or GET_LINES with batch size
   7. Recorded PGA after reading
   8. Called DISABLE and recorded PGA again

   Example test script (512MB write, then read all):

   DECLARE
       v_mem NUMBER;
       v_line VARCHAR2(32767);
       v_status INTEGER;
       v_chunk VARCHAR2(10000) := RPAD('X', 10000, 'X');  -- 10KB per line
   BEGIN
       -- Record baseline
       SELECT value/1024/1024 INTO v_mem FROM v$mystat m, v$statname n
       WHERE m.statistic# = n.statistic# AND n.name = 'session pga memory';
       INSERT INTO mem_results VALUES ('baseline', v_mem);
   
       DBMS_OUTPUT.ENABLE(NULL);  -- Unlimited buffer
   
       -- Write ~512MB (51200 lines × 10KB)
       FOR i IN 1..51200 LOOP
           DBMS_OUTPUT.PUT_LINE(v_chunk);
       END LOOP;
       -- Record PGA after write...
   
       -- Read ALL lines
       LOOP
           DBMS_OUTPUT.GET_LINE(v_line, v_status);
           EXIT WHEN v_status != 0;
       END LOOP;
       -- Record PGA after read...
   
       DBMS_OUTPUT.DISABLE;
       -- Record PGA after disable...
   END;

   Key Finding: Oracle Does NOT Deallocate Memory

   Operation	PGA Memory Change
   ENABLE(NULL)	No change (lazy allocation)
   Write 512MB content	+815 MB
   GET_LINE (all)	No change
   GET_LINES (all)	+315 MB (allocates return array!)
   DISABLE	No change
   Re-ENABLE	No change
   Session end	Memory freed

   Memory Reuse Across Repeated Cycles

   Tested 5 cycles of: write 512MB → read all via GET_LINES:

   Cycle	Step	PGA (MB)	Delta
   0	baseline	3	-
   1	after_write	818	+815
   1	after_read	1133	+315
   2	after_write	1133	0
   2	after_read	1133	0
   3-5	all steps	1133	0

   Key insight: Oracle allocates memory on first use (~1.1 GB for 512MB content + GET_LINES return array), then reuses that same memory for subsequent cycles. Memory doesn't grow unbounded, but it's never returned to the system until session ends.

   This is typical memory pool behavior: allocate once, reuse forever, free on session end.

   Memory Growth at Scale

   Tested writing up to 2.5GB of content:

   Content Written	PGA Allocated	Notes
   500 MB	818 MB	~1.6x overhead
   1000 MB	1633 MB	Exceeded pga_aggregate_target (512MB)
   1500 MB	2433 MB	Exceeded pga_aggregate_limit (2048MB)
   2000 MB	3233 MB	No error
   2500 MB	4033 MB	Completed successfully

   Oracle exceeded its own pga_aggregate_limit of 2GB, reaching 4GB+ PGA for DBMS_OUTPUT!

   Buffer Space Recycling vs Memory Deallocation

   Important distinction:

   • Buffer space recycling: ✅ GET_LINE frees buffer space for reuse (allows more writes)
   • Memory deallocation: ❌ PGA memory is never returned to the system until session ends

   Test showing recycling works but memory stays allocated:

   ENABLE(2000)           -- 2000 byte limit
   Write 40 lines         -- 2000 bytes, buffer full
   Read 20 lines          -- Frees 1000 bytes of buffer space
   Write 40 MORE lines    -- Succeeds! (recycled space)
   -- Buffer now has 60 lines (3000 bytes physical content)
   -- But PGA memory unchanged
   

   Sessions Hold Memory Until Killed

   After our tests, Oracle sessions retained gigabytes of PGA:

   SID 21:  8035 MB PGA
   SID 175: 1202 MB PGA
   Container: 9.08 GB total
   

   After ALTER SYSTEM KILL SESSION:

   Container: 1.21 GB total
   

   Implications for IvorySQL

   The PR reviewer suggested "linked list for prompt deallocation" - but Oracle doesn't do this either. Oracle's actual behavior:

   1. Never deallocates DBMS_OUTPUT memory during session
   2. Reuses allocated memory for subsequent write/read cycles
   3. Ignores PGA limits for DBMS_OUTPUT buffers
   4. Only frees memory when session ends

   IvorySQL's ring buffer approach (pre-allocate, reuse, never deallocate) actually matches Oracle's memory retention behavior.
3. rophy commented on Dec 29, 2025

   

   rophy

   on Dec 29, 2025

   Last edited by rophy

   OwnerAuthor

   More actions

   IvorySQL Linked List Implementation - Memory Test Results

   Background

   Based on the PR review feedback, I refactored the DBMS_OUTPUT implementation from ring buffer to linked list in commit 02b9582.

   Key changes:

   • Replaced contiguous ring buffer with singly-linked list of nodes
   • Each line stored as DbmsOutputLine struct with flexible array member
   • Memory freed via pfree() on GET_LINE (AllocSet handles recycling)
   • No buffer expansion/packing overhead for large buffers
   • 152 fewer lines of code

   Test Method

   Used pg_backend_memory_contexts to track the "DBMS_OUTPUT buffer" memory context:

   SELECT name,
          round(total_bytes/1024.0/1024.0, 2) as total_mb,
          round(used_bytes/1024.0/1024.0, 2) as used_mb
   FROM pg_backend_memory_contexts
   WHERE name = 'DBMS_OUTPUT buffer';

   Test script:

   -- Write test
   CALL dbms_output.enable(NULL);
   DECLARE
       v_line VARCHAR2(1000) := RPAD('X', 1000, 'X');
   BEGIN
       FOR i IN 1..100000 LOOP
           dbms_output.put_line(v_line);
       END LOOP;
   END;
   /
   
   -- Read test
   DECLARE
       v_line VARCHAR2(32767);
       v_status INTEGER;
   BEGIN
       LOOP
           dbms_output.get_line(v_line, v_status);
           EXIT WHEN v_status != 0;
       END LOOP;
   END;
   /

   Results

   Small Scale (10MB content: 10,000 × 1KB lines)

   Operation	total_mb	used_mb	Notes
   After ENABLE(NULL)	0.01	0.00	Initial allocation
   After writing 10MB	16.00	9.92	~10MB for 10MB content
   After reading 5000 lines	16.00	4.96	Used drops by ~5MB
   After reading all	16.00	0.00	Used drops to 0

   Large Scale (100MB and 500MB)

   Test	Content	total_mb	used_mb	Time	Overhead
   Write 100MB	100K × 1KB	104.00	99.18	675ms	~4%
   Read all 100K	0	104.00	0.00	1.3s	-
   Write 500MB	500K × 1KB	496.00	495.91	3.4s	<1%
   Read 250K (half)	250MB	496.00	247.96	3.2s	-
   Read remaining	0	496.00	0.00	3.3s	-
   Re-ENABLE	0	0.01	0.00	65ms	Full cleanup

   Session Cleanup Test

   Event	DBMS_OUTPUT buffer
   Session 1: Write 10MB	16.7MB allocated
   Session 1: Disconnect	Backend exits → memory freed
   Session 2: New connection	No buffer (0 rows)

   ✅ Memory is fully freed when session closes.

   Evaluation Summary

   Per-line Overhead Comparison

   Implementation	Per-line Overhead
   Oracle	50-64 bytes
   IvorySQL (linked list)	~40-45 bytes

   Memory Behavior Comparison

   Aspect	Oracle	IvorySQL
   Memory overhead	10-15%	<1-4%
   GET_LINE frees used_bytes	Yes (recycled)	✅ Yes (recycled)
   GET_LINE frees total_bytes	No	No (AllocSet retains)
   Re-ENABLE frees memory	No (preserves buffer)	✅ Yes (full cleanup)
   Session end frees	Yes	✅ Yes

   Performance

   • Write throughput: ~147K lines/second
   • Read throughput: ~154K lines/second
   • Re-ENABLE cleanup of 496MB: 65ms

   Conclusion

   The linked list implementation:

   1. ✅ Scales to 500MB+ with minimal overhead (<1-4%)
   2. ✅ Lower per-line overhead than Oracle (40-45 vs 50-64 bytes)
   3. ✅ Memory recycling works via AllocSet free lists
   4. ✅ Re-ENABLE provides full memory cleanup (improvement over Oracle)
   5. ✅ No O(n) buffer expansion overhead for large buffers
   6. ✅ All existing regression tests pass