Re: PL/SQL Tables - Memory Allocation Puzzle

The change from 3610 to 4618 is in 4K memory pages, so HPUX is just as bad. I've just run the same test on 8.1.5 on HP-UX 11, and the same 4Mb wastage occurs.

Interesting detail - when I did a heapdump on the process, it showed 4M as FREE (not
freeable or recreatable, actually free). I suggest you raise this as a bug with tech support.

--

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

jobrien99_at_my-deja.com wrote in message <829dl3$pm7$1_at_nnrp1.deja.com>...
>Thanks for the reply:
>
>Another way to look at this is with:
>SQL> select * from v$mystat where statistic# in (20,21);
>
>I prefer testing with Oracle with Unix instead of Oracle with Oracle
>but the sql is useful anyway. Let me move on with more information.
>
>On Aix the test was on
>Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
>With the Partitioning and Objects options
>PL/SQL Release 8.0.5.1.0 - Production
>
>On HP the test ( conducted after your post ) is:
>Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production
>With the Partitioning and Objects options
>PL/SQL Release 8.0.5.1.0 - Production
>
>I did not look at the paging for the hp process but the SZ command and
>the test results from ps -efl ( looking at SZ ) are:
>
> 1 S oracle 10299 10296 0 154 24 114859d00 [3610]
>10e29b32e 14:07:13 ? 0:00 oracleCORE (DESCRIP
>
>Above for 100 elements with a gap of 10 , 3610 K
>
> 1 S oracle 8429 8410 7 154 24 116b32700 [4618]
>116be452e 13:54:22 ? 0:00 oracleCORE (DESCRIPTION=(LOCAL=YES)(AD
>
>For 1000 , gap = 1000 4618K => diff of 1M
>
>The problem is not as bad it seems with HPUX.
>
>Your answer about the pointers makes sense to me. It also takes longer
>to populate the 1000 element x1000 gap table. If I allocate a p-l/sql
>table with two elements one at positions
>
>#define INT_MAX (2147483647)
>#define INT_MIN (-INT_MAX - 1)
>[ taken from from /usr/include/limits.h]
>
>The space is small so that null pointer allocation must not be done.
>This confuses me as the null pointer explanation is the best so far.
>
>Also BTW INT_MIN does not work, it has to be INT_MIN+1 , just another
>bone to pick.
>
>Thanks for the help so far. Anybody got access to Solaris/Aix/ or
>Digital Unix ( Or its new name ).
>
>Must get my Oracle on Linux done this weekend and try it on that.
>
>I suppose the pl/sql and select statement could be made into a package
>and run to make it easier.
>
>Regards,
>John
>
>In article <944147574.10565.0.nnrp-07.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>> 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 ?
>> >
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Sat Dec 04 1999 - 09:49:03 CST