Re: Varchar2 use of memory

A copy of this was sent to Frank Hubeny <fhubeny_at_ntsource.com> (if that email address didn't require changing) On Mon, 27 Sep 1999 22:56:48 -0500, you wrote:

>I recently tried a product that provided hints for using plsql.
>
>One of the hints was to avoid plsql variables of the varchar2 type with
>a size larger than 500 because supposedly servers prior to Oracle 8
>allocated the full amount of memory to store the variable. That is, the
>varchar2 in plsql did not behave the way one would expect a varchar2 in
>the database to behave, by using only the memory necessary to represent
>the data.
>
>This didn't seem correct, but then again, I am not sure that I can show
>that it is incorrect.

what they meant, and they are partially correct, is that you should constrain the size of your varchar2's in plsql to be only as big as they need to be and no larger. If you make your variables really large in 7.x and before -- they are consuming the MAX ram they could grow to be. In the database, they consume only as much disk space as needed -- In RAM, they are fully allocated to their max size.

I would not avoid variables over some arbitrary limit (like 500 bytes - where did that 'magic' number come frome?) Rather -- limit the size of your variables to be the max you'll really need -- don't overallocate them (good advice for *any* programming language i think).

In 8.0 and up, they've changed this. If you have a varchar2(2000) and put 1 byte in it, it takes 1 byte (plus the overhead of being a variable and all). If you put in 200 bytes -- it takes that much (gets reallocated). It is a varying size string ramwise as well as diskwise in 8.0 and up.

>
>I created and executed the following procedures.
>
>create or replace procedure testchar
>is
> a char(32000) := 'b';
>begin
> dbms_output.put_line(substr(a,1,5));
>end;
>
>create or replace procedure testvarchar
>is
> a varchar(32000) := 'b';
>begin
> dbms_output.put_line(substr(a,1,5));
>end;
>
>When I looked at dba_object_size, I found that code_size and parsed_size
>were both under 500. And v$sqlarea had sharable_mem < 9000,
>persistent_mem < 500 and runtime_mem < 200. Neither procedure appears
>to be consuming much memory. But the fact that I could not detect a
>larger amount of memory used in the testchar procedure makes me wonder
>whether I am correctly measuring the memory necessary to execute these
>procedures.
>
>How would you determine the memory consumption of these two procedures
>and ascertain whether the hint to avoid such varchar2 references is
>valid?
>
>At the moment, I am assuming the hint is invalid and the char datatype
>in plsql, at least, consumes the same amount of memory as a varchar2.
>
>Frank Hubeny
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 29 1999 - 12:38:58 CDT