Re: PL/SQL Tables - Memory Allocation Puzzle

From: <jobrien99_at_my-deja.com>
Date: Fri, 03 Dec 1999 21:45:07 GMT
Message-ID: <829dl3$pm7$1@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 Fri Dec 03 1999 - 15:45:07 CST