Thursday, August 27, 2009

Does oversize of datatype VARCHAR2 causes performance problem

From the beginning of learning Oracle SQL you have possibly heard that in case of VARCHAR2 datatype it allocates space exactly what it needs. So if you allocates 4000 bytes of VARCHAR2 data type and database needs 10 bytes only then exactly 10 bytes are allocated.

That is, in case of VARCHAR2(4000) and VARCHAR2(16) columns, if we store less then 16 bytes data in these two columns then same amount of space will be allocated, and performance should be the same. But, have you ever tested it? I got a funny example http://hrivera99.blogspot.com/2008/05/why-is-varchar2-oversizing-bad.html here. There it is said performance problem but in reality there is not. In the example it is shown problem in physical reads but I don't agree with the example. In fact in the first example it is cached data and hence physical reads is reduced.

In the following section I simulate same example and see no performance differences. However there may rise, http://arjudba.blogspot.com/2008/09/ora-01450-maximum-key-length-3215.html while creating index in case of bigger VARCHAR2 length.

The most misleading example can be created by omitting
"
ALTER TABLESPACE EXAMPLE OFFLINE;

ALTER TABLESPACE EXAMPLE ONLINE;"

If you omit this step you may get different result as data become cached. And you need to take tablespace offline in order to get most accurate result as offlining a tablespace uncache of corresponding tablespace data.

Step 1)Create varchar2_length_test table with VARCHAR2(4000) and insert data into it.
SQL> create table varchar2_length_test(
2  ID                  NUMBER,
3  COL2        VARCHAR2(4000),
4  COL3        VARCHAR2(4000),
5  COL4        VARCHAR2(4000),
6  COL5        VARCHAR2(4000),
7  COL6        VARCHAR2(4000),
8  COL7        VARCHAR2(4000),
9  COL8        VARCHAR2(4000),
10  COL9        VARCHAR2(4000),
11  COL10        VARCHAR2(4000),
12  COL11        VARCHAR2(4000),
13  COL12        VARCHAR2(4000),
14  COL13        VARCHAR2(4000)) TABLESPACE EXAMPLE;

Table created.

SQL>
SQL> begin
2  for i in 1 .. 100000
3  LOOP
4  INSERT into varchar2_length_test VALUES(
5  i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12',
6  i||'Col13');
7  END LOOP;
8  END;
9  /

PL/SQL procedure successfully completed.

Step 2)Create varchar2_length_test_short table with VARCHAR2(16) and insert data into it.
SQL> create table varchar2_length_test_short(
2  ID                  NUMBER,
3  COL2        VARCHAR2(16),
4  COL3        VARCHAR2(16),
5  COL4        VARCHAR2(16),
6  COL5        VARCHAR2(16),
7  COL6        VARCHAR2(16),
8  COL7        VARCHAR2(16),
9  COL8        VARCHAR2(16),
10  COL9        VARCHAR2(16),
11  COL10        VARCHAR2(16),
12  COL11        VARCHAR2(16),
13  COL12        VARCHAR2(16),
14  COL13        VARCHAR2(16)) TABLESPACE EXAMPLE;

Table created.

SQL> begin
2  for i in 1 .. 100000
3  LOOP
4  INSERT into varchar2_length_test_short VALUES(
5  i, i||'Col2',i||'Col3',i||'Col4',i||'Col5',i||'Col6',i||'Col7',i||'Col8',i||'Col9',i||'Col10',i||'Col11',i||'Col12',
6  i||'Col13');
7  END LOOP;
8  END;
9  /

PL/SQL procedure successfully completed.

Step 3)Clear caching in the tablespace.
SQL> ALTER TABLESPACE EXAMPLE OFFLINE;
Tablespace altered.

SQL> ALTER TABLESPACE EXAMPLE ONLINE;
Tablespace altered.


Step 4)Enable tracing and look at statistics
SQL> SET AUTOT TRACE
SQL> select count(*) from varchar2_length_test;

1 row selected.


Execution Plan
----------------------------------------------------------                                                                                 
Plan hash value: 1500664439                                                                                                                

-----------------------------------------------------------------------------------                                                        
| Id  | Operation          | Name                 | Rows  | Cost (%CPU)| Time     |                                                        
-----------------------------------------------------------------------------------                                                        
|   0 | SELECT STATEMENT   |                      |     1 |   418   (2)| 00:00:06 |                                                        
|   1 |  SORT AGGREGATE    |                      |     1 |            |          |                                                        
|   2 |   TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST | 88364 |   418   (2)| 00:00:06 |                                                        
-----------------------------------------------------------------------------------                                                        

Note                                                                                                                                       
-----                                                                                                                                      
- dynamic sampling used for this statement                                                                                              


Statistics
----------------------------------------------------------                                                                                 
29  recursive calls                                                                                                               
1  db block gets                                                                                                                 
1980  consistent gets                                                                                                               
1912  physical reads                                                                                                                
176  redo size                                                                                                                     
411  bytes sent via SQL*Net to client                                                                                              
396  bytes received via SQL*Net from client                                                                                        
2  SQL*Net roundtrips to/from client                                                                                             
0  sorts (memory)                                                                                                                
0  sorts (disk)                                                                                                                  
1  rows processed                                                                                                                

SQL> ALTER TABLESPACE EXAMPLE OFFLINE;

Tablespace altered.

SQL>
SQL> ALTER TABLESPACE EXAMPLE ONLINE;

Tablespace altered.

SQL> select count(*) from varchar2_length_test_short;

1 row selected.


Execution Plan
----------------------------------------------------------                                                                                 
Plan hash value: 161270611                                                                                                                 

-----------------------------------------------------------------------------------------                                                  
| Id  | Operation          | Name                       | Rows  | Cost (%CPU)| Time     |                                                  
-----------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT   |                            |     1 |   418   (2)| 00:00:06 |                                                  
|   1 |  SORT AGGREGATE    |                            |     1 |            |          |                                                  
|   2 |   TABLE ACCESS FULL| VARCHAR2_LENGTH_TEST_SHORT |   109K|   418   (2)| 00:00:06 |                                                  
-----------------------------------------------------------------------------------------                                                  

Note                                                                                                                                       
-----                                                                                                                                      
- dynamic sampling used for this statement                                                                                              


Statistics
----------------------------------------------------------                                                                                 
29  recursive calls                                                                                                               
1  db block gets                                                                                                                 
1993  consistent gets                                                                                                               
1912  physical reads                                                                                                                
176  redo size                                                                                                                     
411  bytes sent via SQL*Net to client                                                                                              
396  bytes received via SQL*Net from client                                                                                        
2  SQL*Net roundtrips to/from client                                                                                             
0  sorts (memory)                                                                                                                
0  sorts (disk)                                                                                                                  
1  rows processed                                                                                                                



So we see in both VARCHAR2(4000) and VARCHAR2(16) almost same consistent gets and physical reads. So oversize of varchar2 does not cause performance problem issue but lead to other problems.

Related Documents

ORA-01450: maximum key length (3215) exceeded

1 comment:

Hemant K Chitale said...

Instead of taking the Tablespace Offline and Online, can you do a COUNT(*) on the table twice -- and do the autotrace on the second or third execution of the COUNT(*), ignoring the first execution ?


Hemant K Chitale
http://hemantoracledba.blogspot.com