Just a quick heads up for anyone using JSON_OBJECT_T in PL/SQL that was brought to my attention by an AskTOM member
It looks like repeated use of JSON_OBJECT_T in an associative array can consume PGA until such point as you’ll hit errors.
Here’s an example of a associative array of VARCHAR2 growing a large size and then being freed once work with it has been completed.
SQL> create or replace procedure rpt_pga(l_name varchar) as
2 l_v$process_mem varchar2(4000);
3 l_v$process_memory_mem varchar2(4000);
4 l_used_mem_mb number;
5 p_sid number; -- := sys.dbms_support.mysid;
6 begin
7 select sid into p_sid from v$mystat where rownum=1;
8 select round(pga_used_mem/1024/1024)
9 into l_used_mem_mb
10 from v$process where addr = (select paddr from v$session where sid = p_sid);
11
12 dbms_output.put_line(rpad(l_name, 20, '-')||' PGA Used(MB): '||l_used_mem_mb);
13 end;
14 /
Procedure created.
SQL>
SQL>
SQL> --test_scalar (char)
SQL> create or replace procedure test_scalar(p_cnt number, p_loop number) as
2 type t_tab is table of varchar2(32000) index by pls_integer;
3 l_tab t_tab;
4 begin
5 for i in 1..p_cnt loop
6 dbms_output.put_line(rpad('*', 30, '*')||' RUN-'||i||rpad('*', 30, '*'));
7 rpt_pga('Init');
8 for i in 1..p_loop loop
9 l_tab(i) := '{ "demo":'|| rpad('12345', 30000, '-') ||'}';
10 end loop;
11 rpt_pga('After Create');
12 l_tab.delete;
13 --l_tab := new t_tab();
14 dbms_session.FREE_UNUSED_USER_MEMORY;
15 rpt_pga('After Free');
16 dbms_output.put_line('');
17 end loop;
18 end;
19 /
Procedure created.
SQL>
SQL> set serverout on
SQL> exec test_scalar(3, 10000);
****************************** RUN-1******************************
Init---------------- PGA Used(MB): 330
After Create-------- PGA Used(MB): 640
After Free---------- PGA Used(MB): 327
****************************** RUN-2******************************
Init---------------- PGA Used(MB): 327
After Create-------- PGA Used(MB): 640
After Free---------- PGA Used(MB): 327
****************************** RUN-3******************************
Init---------------- PGA Used(MB): 327
After Create-------- PGA Used(MB): 640
After Free---------- PGA Used(MB): 327
PL/SQL procedure successfully completed.
You can see we grew our PGA by ~300MB but then got that memory back when we called the familiar FREE_UNUSED_USER_MEMORY.
However, when we perform the same operation using JSON_OBJECT_T
SQL>
SQL> --test_json (Json)
SQL> create or replace procedure test_json(p_cnt number, p_loop number) as
2 type t_tab is table of json_object_t index by pls_integer;
3 l_tab t_tab;
4 begin
5 for i in 1..p_cnt loop
6 dbms_output.put_line(rpad('*', 30, '*')||' RUN-'||i||rpad('*', 30, '*'));
7 rpt_pga('Init');
8 for i in 1..p_loop loop
9 l_tab(i) := new json_object_t('{ "demo":12345 }');
10 end loop;
11 rpt_pga('After Create');
12 l_tab.delete;
13 --l_tab := new t_tab();
14 dbms_session.free_unused_user_memory;
15 rpt_pga('After Free');
16 end loop;
17 dbms_output.put_line('');
18 end;
19 /
Procedure created.
SQL> exec test_json(3, 10000);
****************************** RUN-1******************************
Init---------------- PGA Used(MB): 327
After Create-------- PGA Used(MB): 917
After Free---------- PGA Used(MB): 915
****************************** RUN-2******************************
Init---------------- PGA Used(MB): 915
After Create-------- PGA Used(MB): 1829
After Free---------- PGA Used(MB): 1827
****************************** RUN-3******************************
Init---------------- PGA Used(MB): 1827
After Create-------- PGA Used(MB): 2725
After Free---------- PGA Used(MB): 2723
PL/SQL procedure successfully completed.
the FREE_UNUSED_USER_MEMORY seems to have little impact, and our session PGA continues to spiral.
This has been logged as Bug 36135276 – PGA MEMORY LEAK IN JSON_OBJECT FUNCTION, and the issue does not occur in 23ai, but you’ll need to take care in 19c.
Learning is not a spectator sport 
Got some thoughts? Leave a comment