Re: PL/SQL Tables - Memory Allocation Puzzle

You don't say which version of PL/SQL
you are using. I have run your test on Oracle 7.3.4 on HP-UX 11.00, and the
memory page count:

    starts at 1948 when connecting to SQL*Plus     goes up to 1960 after running the first loop (i*10)     stays at 1960 after running the second loop (i*1000)

I believe early versions of PL/SQL did not use sparse array techniques, and the extra 4Mb would be consistent with needing a million null pointers of 4B each to reserve space for the 1m empty array items that you implicitly create with the second loop.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

jobrien99_at_my-deja.com wrote in message <824dj8$77m$1_at_nnrp1.deja.com>...
>Can anyone explain this:
>
>Run:
> declare
> type t_tab is table of varchar2(30) index by binary_integer;
> l_tab t_tab;
> begin
> for i in 1 .. 1000 loop
> l_tab(i*10) := 'AAA';
> end loop;
> end;
>
>Check memory ( ps v on aix )
>then run
>
> declare
> type t_tab is table of varchar2(30) index by binary_integer;
> l_tab t_tab;
> begin
> for i in 1 .. 1000 loop
> l_tab(i*1000) := 'AAA'; -- KEY CHANGE
> end loop;
> end;
>
>Check memory ( ps v on aix )
>
>and the memory used for each process varies greatly even though the
>number of elements is the same , just the number of empty elements
>betweenn them varies. PL/SQL tables are sparse structures ?
>
>Some output ( Platform AIX - ps v | grep pid , Oracle 8.0 ):
>
> PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM
>COMMAND
> 28048 - A 0:00 1 936 9224 xx 15969 8220 0.0 0.0
>oracleRDP
>
>Above the data is 936 K used for a process that has 1000 elements qith
>a gap of 10.
>
>
> PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM
>COMMAND
> 28048 - A 0:00 1 5000 13288 xx 15969 8220 0.0 1.0
>oracleRDP
>
>Above here is 1000 elements with 1000 gaps and the memory is 5000K
>
>Am I losing about 4M for nothing ?
>
Received on Thu Dec 02 1999 - 09:10:57 CST