DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: buffer question

Hybrid View

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    buffer question

    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).

    Ken

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    data is read into buffer in blocks not bytes or characters

  3. #3
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    So, it doesnt matter if u have varchar2(4000) and varchar2(1)

  4. #4
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    kris123 was that a question or a statement?

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    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.

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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-10-2005 at 11:24 PM.

  7. #7
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Nahhhh...its a tuta. its me when i was 1 month old

  8. #8
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    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.

    Oracle DBA

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  10. #10
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    kris,

    Check this out: Tuta' Special Recipes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width