Re: Why is VARCHAR2(4000) bad ?

From: Andrew M <andrew.markiewicz_at_gmail.com>
Date: Tue, 29 Jan 2008 15:43:54 -0800 (PST)
Message-ID: <62c82312-504b-4659-ad21-ff9776e8d5a9@s12g2000prg.googlegroups.com>

On Jan 28, 7:55 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> DECLARE
> myvar mytable.mycolumn%TYPE;
> BEGIN
> myvar := 'A';
> END;
> /
>
> is best practice.
>
> Want to reconsider?
> --

Not really. My original post still stands. Your reference to slide 50 of that presentation does not support anything you stated. It is about bind variable mismatch due to sizes of the bind variables. It has nothing to do with the sizing of a column.

As for plsql memory, the following test shows the uga and pga memory for separate sessions given a varchar2(1) variable and a varchar2(4000) variable.
The memory use is identical. Oracle seemingly allocates 32k per shadow as an initial allocation.

SESSION 1 :TEST varchar2(1)
sql->select *
  2 from stats
  3 where name like '%pga%'
  4 or name like '%uga%'
  5 /

NAME
VALUE

---

---

STAT...session uga

memory                                                   912664
STAT...session uga memory
max                                              1003416
STAT...session pga
memory                                                  1246544
STAT...session pga memory
max                                              1246544

Elapsed: 00:00:00.02
sql->declare
  2 x1 varchar2(1) := 'a';
  3 begin
  4 null;
  5 end;
  6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
sql->select *
  2 from stats
  3 where name like '%pga%'
  4 or name like '%uga%'
  5 /

NAME
VALUE

---

---

STAT...session uga

memory                                                   937648
STAT...session uga memory
max                                              1028400
STAT...session pga
memory                                                  1279888
STAT...session pga memory
max                                              1279888

Elapsed: 00:00:00.01

SESSION2: TEST varchar2(4000)

sql->
sql->select *
  2 from stats
  3 where name like '%pga%'
  4 or name like '%uga%'
  5 /

NAME
VALUE

---

---

STAT...session uga

memory                                                   912664
STAT...session uga memory
max                                              1003416
STAT...session pga
memory                                                  1246544
STAT...session pga memory
max                                              1246544

Elapsed: 00:00:00.02
sql->declare
  2 x1 varchar2(4000) := 'a';
  3 begin
  4 null;
  5 end;
  6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
sql->select *
  2 from stats
  3 where name like '%pga%'
  4 or name like '%uga%'
  5 /

NAME
VALUE

---

---

STAT...session uga

memory                                                   937648
STAT...session uga memory
max                                              1028400
STAT...session pga
memory                                                  1279888
STAT...session pga memory
max                                              1279888

I expanded the test to use 1000 variables of each (varchar2(1) and varchar2(4000)).
The varchar2(4000) does use more memory than the varchar2(1) (500k vs 125k) , but not as much as you would think if it were allocating 4000 bytes per variable. 4000 x 1000 ~= 4M.
But now that would not be best practices either. A collection would be a much more efficient use of memory.

So no, I don't buy it that a varchar2(4000) is bad for plsql memory. Too many will have a price, but that would be a bad program. Also try out the test with 4001. The memory management is even more efficient. 4001 to 32k seems to be dynamically allocated to be the same as if they were varchar2(1).

As for design, I agree that the design specifications should determine the limit of the column. But the column is seemingly a free-form comment, so I do not see that a varchar2(4000) is that bad.

Andrew Received on Tue Jan 29 2008 - 17:43:54 CST