If I have a varchar2(4000) column with 5 bytes of data in one row, does oracle reserve 4k of os memory for that user session even though it only has 5 bytes of data?
Likewise, does an application program allocate the same amount?
I'm curious what resources are being freed by reducing the varchar2(4000) to varchar2(100).
data is read into buffer in blocks not bytes or characters
So, it doesnt matter if u have varchar2(4000) and varchar2(1)
kris123 was that a question or a statement?
Its 50/50 hun I'm not sure if this is what papa pando meant.
Its a guess though, and if its right then it becomes a statement,
but if not, then it is a question.
Sorry, but I'm just curious as heck.
Is that a picture of an EWOK? (uuutah, uuutah)
(a baby Pekinese?)
Last edited by KenEwald; 05-11-2005 at 12:24 AM.
Nahhhh...its a tuta. its me when i was 1 month old
This is what I know:
Being a varchar2( Variable Chararcter), the mem is dynamically allocated according to the data size,i.e., if a cloumn has the size <2000, it will allocate the max( for a varchar2(1500), 1500 bytes will be allocated irrespective of data size) while, if a column has the size >=2000, it will allocate the max column data size.
BTW: Kris You looked soooooooooooo cute
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Thank you simply_dba. Good information.
Looks like this is PL/SQL. Maybe I'm not getting the connection here. But maybe session memory and pl/sql memory are the same thing (both session mem).
Would I be correct to assume that a program allocates chunks of memory based on how much data actually comes back. Say, in chunks of 100 bytes for efficiency (guessing here) and the actual size wouldn't matter - don't go there.
After your post I found this on Google. Thanks for the key words.
continuing on varchar2 memory cost in PL/SQL,
note this found in Oracle 8i/9i PL/SQL user guide :
(Chapter 3 PL/SQL Datatypes - Character types - Varchar2)
"Small VARCHAR2 variables are optimized for performance,
and larger ones are optimized for efficient memory use.
The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes
or longer, PL/SQL dynamically allocates only enough memory
to hold the actual value. For a VARCHAR2 variable that is
shorter than 2000 bytes, PL/SQL preallocates the full declared length
of the variable. For example,
if you assign the same 500-byte value to a VARCHAR2(2000 BYTE)
variable and to a VARCHAR2(1999 BYTE) variable, the former
takes up 500 bytes and the latter takes up 1999 bytes.
Check this out: Tuta' Special Recipes
Click Here to Expand Forum to Full Width