Thanks for the question, Peter.

Asked: September 11, 2017 - 4:46 am UTC

Last updated: October 20, 2017 - 1:01 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello! I have read much information about allocation memory for varchar2 as pl\sql variable and column of table.
So now we have memory problem (assign awr report) with a big report. Can u explain please:
1) type StringTable is table of varchar2(32000) index by varchar2; - Do I understand correctly that for index will allocate memory all 32000 b?
2) type map_port_cred is table of rec_port_cred index by varchar2(32767); - in this case for index will be allocate all 32767 memory?
3) 1,2 paragraph does have big influens on memory?

and Connor said...

We can test that out easily enough. Let's slowly put more and more rows into an array and take a look at the memory use by the session. I reconnect each time to clear out the session stats

SQL>   conn scott/tiger
Connected.

SQL>
SQL> create or replace
  2  procedure stats is
  3  begin
  4  for i in (
  5  select s.name, st.value
  6  from v$statname s, v$mystat st
  7  where st.statistic# = s.statistic#
  8  and s.name like 'session pga memory max'
  9  )
 10  loop
 11    dbms_output.put_line(rpad(i.name,40)||lpad(i.value,10));
 12  end loop;
 13  end;
 14  /

Procedure created.

SQL>
SQL> set serverout on
SQL> declare
  2    type tlist is table of varchar2(10) index by pls_integer;
  3    r tlist;
  4  begin
  5    for i in 0 .. 10 loop
  6      for j in 1 .. 100000 loop
  7         r(i*100000+j) := 'xxxxxxxxxx';
  8      end loop;
  9      stats;
 10   end loop;
 11  end;
 12  /
session pga memory max                     9212968
session pga memory max                    16290856
session pga memory max                    22975528
session pga memory max                    29660200
session pga memory max                    36344872
session pga memory max                    43029544
session pga memory max                    49714216
session pga memory max                    56398888
session pga memory max                    63149096
session pga memory max                    69833768
session pga memory max                    76518440

PL/SQL procedure successfully completed.

So we ended up with 76MB consumed for our 1,000,000 entries in a varchar2(10) plsql table. Let's now repeat that with varchar2(32767)

SQL>
SQL>   conn scott/tiger
Connected.

SQL>
SQL> set serverout on
SQL> declare
  2    type tlist is table of varchar2(32767) index by pls_integer;
  3    r tlist;
  4  begin
  5    for i in 0 .. 10 loop
  6      for j in 1 .. 100000 loop
  7         r(i*100000+j) := 'xxxxxxxxxx';
  8      end loop;
  9      stats;
 10   end loop;
 11  end;
 12  /
session pga memory max                     9147432
session pga memory max                    15373352
session pga memory max                    22058024
session pga memory max                    28742696
session pga memory max                    35492904
session pga memory max                    42177576
session pga memory max                    48862248
session pga memory max                    55546920
session pga memory max                    62231592
session pga memory max                    68916264
session pga memory max                    75600936

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Negligible difference. So it's reasonable to conclude that we're using memory as required rather than pre-allocating. In particular, 1000000 * 32k would have been way way more than that. And finally we can look at index by varchar2

SQL> set serverout on
SQL> declare
  2    type tlist is table of int index by varchar2(32767);
  3    r tlist;
  4  begin
  5    for i in 0 .. 10 loop
  6      for j in 1 .. 100000 loop
  7         r(i*100000+j) := 1;
  8      end loop;
  9      stats;
 10   end loop;
 11  end;
 12  /
session pga memory max                    12948520
session pga memory max                    23827496
session pga memory max                    34772008
session pga memory max                    45716520
session pga memory max                    56595496
session pga memory max                    67540008
session pga memory max                    78484520
session pga memory max                    89363496
session pga memory max                   100308008
session pga memory max                   111252520
session pga memory max                   122131496

PL/SQL procedure successfully completed.

SQL> set serverout on
SQL> declare
  2    type tlist is table of int index by varchar2(20);
  3    r tlist;
  4  begin
  5    for i in 0 .. 10 loop
  6      for j in 1 .. 100000 loop
  7         r(i*100000+j) := 1;
  8      end loop;
  9      stats;
 10   end loop;
 11  end;
 12  /
session pga memory max                    12948520
session pga memory max                    23827496
session pga memory max                    34772008
session pga memory max                    45716520
session pga memory max                    56595496
session pga memory max                    67540008
session pga memory max                    78484520
session pga memory max                    89363496
session pga memory max                   100308008
session pga memory max                   111252520
session pga memory max                   122131496

PL/SQL procedure successfully completed.

So we use memory as required...but as you can see, we *still* do need memory so its unwise to have MASSIVE plsql tables

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Different results

Peter Shch, October 19, 2017 - 7:44 am UTC

Hi again! I checkec your examples, but i have different results. Exactly copy your code, or use my it doesn't matter:
1)
declare
procedure test1(p_size pls_integer)
is
begin
execute immediate
'
declare
type tlist is table of varchar2('||p_size||') index by pls_integer;
r tlist;
p number;

function GetPGA return number is
i number;
begin
select ROUND(SUM(b1.VALUE) / 1024)
into i
from V$MYSTAT b1, V$STATNAME a1
where a1.STATISTIC# = b1.STATISTIC#
and a1.NAME = ''session pga memory'';
return i;
end;
begin
dbms_session.free_unused_user_memory;
p := GetPGA;
for x in 1 .. 1000000 loop
r(x) := ''xxxxxxxxx'';
end loop;
dbms_output.put_line(''PGA varchar2('||p_size||'): '' || (GetPGA - p) || '' Kb'');
dbms_session.free_unused_user_memory;
end;
';
end;
begin
test1(10);
test1(64);
test1(65);
test1(32767);
end;

PGA varchar2(10): 36224 Kb
PGA varchar2(64): 98240 Kb
PGA varchar2(65): 65344 Kb
PGA varchar2(32767): 65344 Kb

As you can see, size > 65 has auto allocation memory, but it takes more memory and size < 65 has no auto allocation memory.

2)

declare
procedure test1(p_size pls_integer)
is
begin
execute immediate
'
declare
type tp_rec is record(v varchar2(4), v2 varchar2('||p_size||'));
type tp_tab is table of tp_rec index by binary_integer;
tTab tp_tab;
p number;

function GetPGA return number is
i number;
begin
select ROUND(SUM(b1.VALUE) / 1024)
into i
from V$MYSTAT b1, V$STATNAME a1
where a1.STATISTIC# = b1.STATISTIC#
and a1.NAME = ''session pga memory'';
return i;
end;
begin
dbms_session.free_unused_user_memory;
p := GetPGA;
for x in 1 .. 1000000 loop
tTab(x).v := ''text'';
end loop;
dbms_output.put_line(''PGA varchar2('||p_size||'): '' || (GetPGA - p) || '' Kb'');
tTab.delete;
dbms_session.free_unused_user_memory;
end;
';
end;
begin
test1(10);
test1(256);
test1(257);
test1(32767);
end;

If we use varchar2 in record it has auto allocation memory only if size > 256.
How can u explain this situation? Thank you!

October 20, 2017 - 1:01 am UTC

Well... you're asking a different question. The original question was: will a varchar2(32k) alloc all 32k... the answer is no.

I would say its reasonable to assume we have some boundary points when it comes to allocating memory. We do it in similar fashion for SQL, eg

https://asktom.oracle.com/pls/asktom/asktom.search?tag=why-multiple-versions-what-would-be-the-cause-of-bind-mismatch

Thanks!

Peter Shch, October 24, 2017 - 6:35 am UTC

Thanks!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Different results

Thanks!

More to Explore

PL/SQL demos

PL/SQL docs

Allocation memory for varchar2 index

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Different results

Thanks!

More to Explore

PL/SQL demos

PL/SQL docs