Oracle varchar2索引的分配内存

你好!我已经阅读了很多有关varchar2作为pl \ sql变量和表列的分配内存的信息。
所以现在我们有一个大报告的内存问题 (分配awr报告)。你能解释一下吗:
1) 类型StringTable是varchar2(32000) 索引的表;-我是否正确理解index将分配所有32000 b的内存？
2) 类型map_port_cred是varchar2(32767) 的rec_port_cred索引表;-在这种情况下，索引将分配所有32767内存？
3) 1,2段落对内存有很大影响？

我们可以很容易地测试出来。让我们慢慢将越来越多的行放入数组中，并查看会话使用的内存。我每次都重新连接以清除会话统计信息

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.
复制

因此，我们最终在varchar2(10) plsql表中消耗了76mb的1,000,000条目。现在让我们用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>

复制

可以忽略不计的差异。因此，可以合理地得出结论，我们是根据需要使用内存，而不是预先分配。特别是，1000000 * 32k会比这更多。最后，我们可以通过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.

复制

所以我们根据需要使用内存... 但是正如你所看到的，我们仍然需要内存，所以拥有大量的plsql表是不明智的