Varchar2 use of memory

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.

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 Received on Mon Sep 27 1999 - 22:56:48 CDT