Home » RDBMS Server » Performance Tuning » how to examine the impact of a too long varchar2 field (Oracle Database 10g R2)
icon5.gif  how to examine the impact of a too long varchar2 field [message #570219] Tue, 06 November 2012 19:00 Go to next message
beingman
Messages: 19
Registered: August 2005
Junior Member

Hi All,


I have been used to the consciousness that we should use the minimum length for varchar2 field that can store the data we need manipulate. But recently I was told that it has little impact on performance if we assign a much longer size. I feel there should be some impact,but no evidences.

Can anybody give me some example to show some impact on performace?

Thanks in advance!

[Updated on: Tue, 06 November 2012 19:01]

Report message to a moderator

Re: how to examine the impact of a too long varchar2 field [message #570222 is a reply to message #570219] Tue, 06 November 2012 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I was told that it has little impact on performance if we assign a much longer size.


Explain in details. Quote, link, reference...

Regards
Michel

[Updated on: Tue, 06 November 2012 23:31]

Report message to a moderator

Re: how to examine the impact of a too long varchar2 field [message #570235 is a reply to message #570219] Wed, 07 November 2012 03:06 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
You could run a few simple benchmarks, such as
drop table t1;
create table t1(c1 varchar2(1));
alter system flush buffer_cache;
alter system flush shared_pool;
alter system switch logfile;
set timing on;
insert into t1 (select 'a' from dual connect by level < 1000000);
commit;
update t1 set c1='b';
commit;
delete from t1;
commit;
and repeat with differing varchar lengths. It's difficult to ensure repeatable circumstances. Oracle provides the (licensable) database replay option which is wonderful for this sort of thing.

Re: how to examine the impact of a too long varchar2 field [message #570280 is a reply to message #570219] Wed, 07 November 2012 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
beingman wrote on Wed, 07 November 2012 01:00
Hi All,


I have been used to the consciousness that we should use the minimum length for varchar2 field that can store the data we need manipulate. But recently I was told that it has little impact on performance if we assign a much longer size. I feel there should be some impact,but no evidences.

Can anybody give me some example to show some impact on performace?

Thanks in advance!


Even if it's true you shouldn't use long columns. Performance isn't the only consideration.
It isn't even the main consideration.
Accurate data is the main consideration.
Then data presentation - lots of GUI tools will size columns automatically based on column width. If you set all your columns to varchar2(4000) you're going to be doing a lot of scrolling.

With that in mind, who cares what the performance impact is?
Re: how to examine the impact of a too long varchar2 field [message #570303 is a reply to message #570222] Wed, 07 November 2012 18:35 Go to previous messageGo to next message
beingman
Messages: 19
Registered: August 2005
Junior Member



Actually, I have do this, further more, I also gathered the sql trace information for the same data on different sizes to see any difference on CPU cost, and autotrace in SQL*Plus to see any difference on network transfer amount, but no findings.
Re: how to examine the impact of a too long varchar2 field [message #570304 is a reply to message #570235] Wed, 07 November 2012 18:50 Go to previous messageGo to next message
beingman
Messages: 19
Registered: August 2005
Junior Member




As I know from the oracle Production document, varchar2 field is stored in the format of data length plus real data, so it will not waste storage space, and it will not waste buffer cache either since the basic I/O unit is a block, and buffer size is the same.

So I wonder why you suggest such test, what's your key assumption behind this, anything related with PGA?
Re: how to examine the impact of a too long varchar2 field [message #570305 is a reply to message #570303] Wed, 07 November 2012 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
>Actually, I have do this, further more, I also gathered the sql trace information for the same data on different sizes to see any difference on CPU cost,
http://en.wikipedia.org/wiki/Snipe_hunt

>but no findings.
There were findings, but perhaps not what you expected them to be.
Re: how to examine the impact of a too long varchar2 field [message #570306 is a reply to message #570280] Wed, 07 November 2012 19:09 Go to previous messageGo to next message
beingman
Messages: 19
Registered: August 2005
Junior Member



Thank you for your remind, most of the time, application developers cann't tell precise size, they just want more length to fit for more situations, and they say java GUI can auto ajust to the acctual length of the data, so I want to know any performance issues exist.Thomas Kyte mentions this topics a little in his book <<Effect Oracle by Design>>, but no examples, so I wonder maybe someone can state some.
Re: how to examine the impact of a too long varchar2 field [message #570307 is a reply to message #570305] Wed, 07 November 2012 19:15 Go to previous messageGo to next message
beingman
Messages: 19
Registered: August 2005
Junior Member


What does the hyperlink stuff mean, Can you explain?

So you have some findings, please post here.

[Updated on: Wed, 07 November 2012 19:16]

Report message to a moderator

Re: how to examine the impact of a too long varchar2 field [message #570308 is a reply to message #570306] Wed, 07 November 2012 19:17 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
It is your application, database, & hardware; so you are free to (ab)use it any way you deem to be appropriate.
Re: how to examine the impact of a too long varchar2 field [message #570326 is a reply to message #570304] Thu, 08 November 2012 01:14 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
Quote:
So I wonder why you suggest such test, what's your key assumption behind this, anything related with PGA?
The reason is that I am a scientest: I follow the Scientific Method. You produced the hypothesis that oversized varchar2 columns are bad for performance. I suggested that you test thre hypothesis. You did, and found that there is no effect on performance. This is known as "falsification", and is the heart of the Method. You must study the Method if you intend to work as a DBA. You must also learn to say "thank you" to those who try to assist you.
Re: how to examine the impact of a too long varchar2 field [message #570343 is a reply to message #570326] Thu, 08 November 2012 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This is known as "falsification",


In Carl Popper's meaning of science but, as my father often told me, there are other definitions for science (althought I fail to see them as science). Wink

Regards
Michel

[Updated on: Thu, 08 November 2012 02:01]

Report message to a moderator

Re: how to examine the impact of a too long varchar2 field [message #570364 is a reply to message #570326] Thu, 08 November 2012 05:09 Go to previous messageGo to next message
beingman
Messages: 19
Registered: August 2005
Junior Member

John Watson wrote on Thu, 08 November 2012 01:14
Quote:
So I wonder why you suggest such test, what's your key assumption behind this, anything related with PGA?
The reason is that I am a scientest: I follow the Scientific Method. You produced the hypothesis that oversized varchar2 columns are bad for performance. I suggested that you test thre hypothesis. You did, and found that there is no effect on performance. This is known as "falsification", and is the heart of the Method. You must study the Method if you intend to work as a DBA. You must also learn to say "thank you" to those who try to assist you.



OK. Thank you, scientest.
Re: how to examine the impact of a too long varchar2 field [message #570606 is a reply to message #570364] Mon, 12 November 2012 12:56 Go to previous message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
VARCHAR2 sizes may impact other things you are not testing. I can think of a few ways they might make a difference.

1) how much memory will Oracle allocate for an associative array?
2) how much memory will must Oracle allocate to do a HASH JOIN?
3) what optimizations does Oracle do with varchar2 variables?
4) how much memory will your gui language reserve to host a rowset or array?

Consider these code examples:

create table tab_beingman_varchar2_size_1
(
    pk_id integer
  , name varchar2(4000)
)
/

create table tab_beingman_varchar2_size_2
(
    pk_id integer
  , name varchar2(4)
)
/


create or replace package pkg_beingman_varchar2_size is

   type t_name_array_4000 is record (name tab_beingman_varchar2_size_1.name%type);
   type a_name_array_4000 is table of t_name_array_4000 index by binary_integer;

   type t_name_array_4 is record (name tab_beingman_varchar2_size_2.name%type);
   type a_name_array_4 is table of t_name_array_4 index by binary_integer;

   procedure p1;

end;
/

create or replace package body pkg_beingman_varchar2_size is

   procedure p1 is
      a_name_array_4000_v a_name_array_4000;
      a_name_array_4_v a_name_array_4;
   begin
      for r1 in (
                  select pk_id i,name
                  from tab_beingman_varchar2_size_1
                  where rownum <= 1000
                ) loop
         a_name_array_4000_v(r1.i).name := r1.name;
      end loop;

      for r2 in (
                  select pk_id i,name
                  from tab_beingman_varchar2_size_2
                  where rownum <= 1000
                ) loop
         a_name_array_4_v(r2.i).name := r2.name;
      end loop;
   end;

end;
/



13:37:58 SQL> delete from plan_table;

31 rows deleted.

Elapsed: 00:00:00.06
13:38:43 SQL> explain plan for
13:38:43   2  select /*+ ordered */ *
13:38:43   3  from tab_beingman_varchar2_size_1 a
13:38:43   4      ,tab_beingman_varchar2_size_2 b
13:38:43   5  where a.pk_id = b.pk_id
13:38:43   6  /

Explained.

Elapsed: 00:00:00.06
13:38:43 SQL> @showplan9i

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation            |  Name                         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                               |   164 |   325K|     7 |
|*  1 |  HASH JOIN           |                               |   164 |   325K|     7 |
|   2 |   TABLE ACCESS FULL  | TAB_BEINGMAN_VARCHAR2_SIZE_1  |   164 |   322K|     2 |
|   3 |   TABLE ACCESS FULL  | TAB_BEINGMAN_VARCHAR2_SIZE_2  |   164 |  2788 |     2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."PK_ID"="B"."PK_ID")

Note: cpu costing is off

16 rows selected.

Elapsed: 00:00:00.20
13:38:43 SQL> delete from plan_table;

4 rows deleted.

Elapsed: 00:00:00.06
13:38:50 SQL> explain plan for
13:38:50   2  select /*+ ordered */ *
13:38:50   3  from tab_beingman_varchar2_size_2 b
13:38:50   4      ,tab_beingman_varchar2_size_1 a
13:38:50   5  where a.pk_id = b.pk_id
13:38:50   6  /

Explained.

Elapsed: 00:00:00.06
13:38:50 SQL> @showplan9i

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
| Id  | Operation            |  Name                         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                               |   164 |   325K|     5 |
|*  1 |  HASH JOIN           |                               |   164 |   325K|     5 |
|   2 |   TABLE ACCESS FULL  | TAB_BEINGMAN_VARCHAR2_SIZE_2  |   164 |  2788 |     2 |
|   3 |   TABLE ACCESS FULL  | TAB_BEINGMAN_VARCHAR2_SIZE_1  |   164 |   322K|     2 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("A"."PK_ID"="B"."PK_ID")

Note: cpu costing is off

16 rows selected.


Questions:

1) how big are the associative arrays allocated by each procedure as it loads them? Correct me if I am wrong but I think Oracle must allocate the maximum size for each row.

2) how much memory is required to do the different hash joins? (what if they had 164 million rows instead of 164 rows?). Correct me if I am wrong but I think first hash join need 300 times more memory than the second one?

Also, how much space is needed for each of these variables?

v1 varchar2(30) := 'abc';

v1 varchar2(255) := 'abc';

v1 varchar2(257) := 'abc';


Last I knew (maybe I always had it wrong), the first is 30 bytes, the second is 255 bytes and the last is 3 bytes. I seem to recall reading this somewhere (TOM KYTE maybe?). Correct me if I am wrong but I think the rule is allocate defined amount up to 256 bytes and only do variable size management for variables > 256.

Finally, same questions, but with some other 3gl doing the allocation of memory; Oracle forms for example, or .net or java.

OK, so I am sticking my neck out a little bit on this one as I am going from memory on all this and sometimes my memory makes things up, so some confirmation from the rest of you would be helpful here.

Kevin
Previous Topic: Long running query
Next Topic: Tuning SELECT-WHERE-BETWEEN
Goto Forum:
  


Current Time: Tue Sep 16 19:25:54 CDT 2014

Total time taken to generate the page: 0.06758 seconds