Home » SQL & PL/SQL » SQL & PL/SQL » memory allocation of associative arrays
memory allocation of associative arrays [message #297779] Sun, 03 February 2008 11:52 Go to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
What are the differences to expect (memory use?) between these two code snippets, recognizing that the only difference between them is the size of the varchar2 item at the root of each associative array?

declare
   ta is table of varchar2(2000) index by binary_integer;
   aa ta;
   rc1 sys_refcursor;
begin
   open rc1 for select object_name from dba_objects;
   fetch rc1 bulk collect into aa;
   close rc1;
end;


declare
   ta is table of varchar2(32000) index by binary_integer;
   aa ta;
   rc1 sys_refcursor;
begin
   open rc1 for select object_name from dba_objects;
   fetch rc1 bulk collect into aa;
   close rc1;
end;


I am wondering if there is benefit in defining multiple arrays in a particilar utility package I am working on, and then selecting the array that best fits the size of the data element I am working with.

OR

If one large array would be good for all purposes. The code is simpler to write with just one array definition, but not worth it if there are extreme memory requirements and/or performance changes when using arrays defined with wide string definitions.

Consider these definitions:

type ta_1 is table of varchar2(1) index by binary_integer;
type ta_3 is table of varchar2(3) index by binary_integer;
type ta_30 is table of varchar2(30) index by binary_integer;
type ta_255 is table of varchar2(255) index by binary_integer;
type ta_4000 is table of varchar2(4000) index by binary_integer;

each of the above is a common size element:

varchar2(1) because of y/n t/f m/f type indicators
varchar2(3) because many systems use three digit codes
varchar2(30) because oracle object names are generally 30 bytes wide
varchar2(255) because this used to be the limit for dbms_output 
  and is long but not too long
varchar2(4000) because it is the maximum size of a sql string value, 
  and is a generic catch all size


Given the anonymous plsql samples above, is there a difference between using the varchar2(30) array definition vs. the generic varchar2(4000) array definition, particularly when it comes to memory allocation? What are the benefits of useng 30 over 4000? I am writing a somewhat generic piece of code and would like to use 4000 all the time rather than declaring a series of arrays and selecting the "best fit" for a given item. Less work, less duplicated code etc. But I am afraid of memory costs.

Thanks, Kevin

[Updated on: Sun, 03 February 2008 12:07] by Moderator

Report message to a moderator

Re: memory allocation of associative arrays [message #297783 is a reply to message #297779] Sun, 03 February 2008 12:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there a difference between using the varchar2(30) array definition vs. the generic varchar2(4000) array definition, particularly when it comes to memory allocation?

No (not fully true as varchar2(30) only needs 1 byte for length and varchar2(4000), 2 bytes).

Quote:
What are the benefits of useng 30 over 4000?

Strong typing. Length checking.

Quote:
I am writing a somewhat generic piece of code and would like to use 4000 all the time

So you don't benefit of type length checking.
If your data should be 30 characters long, it is better that Oracle raises an error as soon as data exceed this length instead of getting a buffer overflow in your application.

Regards
Michel

[Updated on: Sun, 03 February 2008 12:13]

Report message to a moderator

Re: memory allocation of associative arrays [message #297788 is a reply to message #297779] Sun, 03 February 2008 13:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
awesome. Just what I wanted to know.

So, where do you learn these kinds of details Michel?

Kevin
Re: memory allocation of associative arrays [message #297792 is a reply to message #297788] Sun, 03 February 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
30 years of development; this creates a little bit of experience. Wink
I think I made almost the possible errors that can be made.

Regards
Michel

[Updated on: Sun, 03 February 2008 13:17]

Report message to a moderator

Re: memory allocation of associative arrays [message #298061 is a reply to message #297792] Mon, 04 February 2008 20:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I was aware of these rules for columns (except I thought the length of columns > ~250 took 2 bytes) as they are documented, but I am not so certain about PL/SQL variables.

The database is able to get away with truly varying length strings because the organisation is predicatable (organised in blocks, each block contains only one type of rows), but PL/SQL declarations are a bit more chaotic.

For example, if you look at the Pro*C model, VARCHAR datatypes are implemented as structs that contain two data items: a fixed length string and the length of its current contents. This is more accurately a "simulated" VARCHAR.

If PL/SQL implements true varying length strings, I would have thought they'd be a bit more vocal and proud of the achievement.

I'm not professing to know how PL/SQL does it, nor am I suggesting that Michel is wrong, just that I will require a bit more evidence.

Ross Leishman
Re: memory allocation of associative arrays [message #298104 is a reply to message #298061] Tue, 05 February 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Strangely Tom answered the same question yesterday at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5408706816128#650538900346236672

But wait! it is asked by Kevin, is this not our Kevin?

Regards
Michel
Re: memory allocation of associative arrays [message #298221 is a reply to message #297779] Tue, 05 February 2008 06:41 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes it is. I asked Tom then said "wait, I can ask ORAFaq too". Tom I figured would be busy for a while and I was looking to get a fast answer. Besides, sometimes getting a secod opinion is a good idea. For example, I didn't think the to check the memory allocations for myself, what a doofus I am.

Anyway, don't be upset, I wasn't checking up on your answer, I asked him first but figured OraFaq might be faster.

Kevin CT USA
Re: memory allocation of associative arrays [message #298233 is a reply to message #298221] Tue, 05 February 2008 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Anyway, don't be upset, I wasn't checking up on your answer,

I am not upset and it is better to double check. As I always say, never trust anyone... even Tom Kyte. Smile
My answer was for Ross to have a look at this thread in AskTom.

Regards
Michel
Re: memory allocation of associative arrays [message #298351 is a reply to message #298233] Tue, 05 February 2008 19:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Being curious, I tried to repeats Tom's results. I'm not sure what's in his his @mystat script - it's probably elsewhere on his site, but I didn't take a good look.

I took a stab and came up with:
select name, value
from sys.V_$SYSSTAT
where name like '%&1%'
/


I made one change: to check the PGA before the initial query so that I have a baseline against which I can compare.

: c985675 ~/sql; sqlplus tmt

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 26 22:02:13 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

TMT@ODMSP> set echo on
TMT@ODMSP> @mystat pga
TMT@ODMSP> select name, value
  2  from sys.V_$SYSSTAT
  3  where name like '%&1%'
  4  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                 33368088
session pga memory max                                             44067484

TMT@ODMSP> @aa 2000
TMT@ODMSP> declare
  2     type ta is table of varchar2(&1) index by binary_integer;
  3     aa ta;
  4     rc1 sys_refcursor;
  5  begin
  6     open rc1 for select object_name from dba_objects;
  7     fetch rc1 bulk collect into aa;
  8     close rc1;
  9  end;
 10  /

PL/SQL procedure successfully completed.

TMT@ODMSP>
TMT@ODMSP> @mystat pga
TMT@ODMSP> select name, value
  2  from sys.V_$SYSSTAT
  3  where name like '%&1%'
  4  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                 33105944
session pga memory max                                             49244828

TMT@ODMSP> @aa 32000
TMT@ODMSP> declare
  2     type ta is table of varchar2(&1) index by binary_integer;
  3     aa ta;
  4     rc1 sys_refcursor;
  5  begin
  6     open rc1 for select object_name from dba_objects;
  7     fetch rc1 bulk collect into aa;
  8     close rc1;
  9  end;
 10  /

PL/SQL procedure successfully completed.

TMT@ODMSP>
TMT@ODMSP> @mystat pga
TMT@ODMSP> select name, value
  2  from sys.V_$SYSSTAT
  3  where name like '%&1%'
  4  /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session pga memory                                                 33105944
session pga memory max                                             49310364

TMT@ODMSP>


Like Tom's example, my PGA did not increase when I used 32000 byte VARCHAR2s, but it DROPPED from the baseline value of 33368088 to 33105944.

What does this mean?

Ross Leishman
Re: memory allocation of associative arrays [message #298401 is a reply to message #298351] Wed, 06 February 2008 01:02 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just Oracle released some part of memory.

For your test, you should start with a brand new session before doing anything (take care of are doing login.sql and glogin.sql).

"mystat" script (from an original from T. Kyte, I slighty modified it but I don't remember in which part, maybe just the feedback and prompt lines):
Column value new_val V
Define S="&1"
Col name format a50
Set feedback off
Prompt
Select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%'||lower('&S')||'%'
/
Prompt
Set feedback on

It is associated with "mystat2" that compares the current value with the previous one set by "mystat" (only works if the "mystat" returned one row):
Col name format a50
Set feedback off
Prompt
Select a.name, b.value, b.value-&V diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%'||lower('&S')||'%'
/
Prompt
Set feedback on

For instance:
SQL> @mystat 'sorts (memory)'

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                           1127

SQL> select table_name from user_tables where rownum=1 order by 1;
TABLE_NAME
------------------------------
TABLE1

1 row selected.

SQL> @mystat2 'sorts (memory)'

NAME                                                    VALUE       DIFF
-------------------------------------------------- ---------- ----------
sorts (memory)                                           1140         13

Regards
Michel
Previous Topic: Yes/No in columns
Next Topic: Most common record
Goto Forum:
  


Current Time: Thu Dec 08 08:41:42 CST 2016

Total time taken to generate the page: 0.11427 seconds