Just in case

For those who don’t read Oracle-l and haven’t found Nikolay Savvinov’s blog, here’s a little note pulling together a recent question on Oracle-l and a relevant (and probably unexpected) observation from the blog. The question (paraphrased) was:

The developers/data modelers are creating all the tables with varchar2(4000) as standard by default “Just in case we need it”. What do you think of this idea?

The general answer was that it’s a bad idea (and unnecessary, anyway) and one specific threat that got mentioned was the problem of creating indexes and Oracle error ORA-01450. Coincidentally, though, Nikolay Savvinov had written about a performance-related “bug” in August this year which turned out, only last week, to be expected behaviour. You can read his articles for the details but since he used a 4KB block size to demonstrate it I thought I’d repeat the exercise using an 8KB block size.

 

rem
rem     Script:         column_length_threat.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2014
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem             11.2.0.4
rem
 
drop table t1 purge;
create table t1 (id number(6), v1 varchar(40), v2 varchar2(40), v3 varchar2(40));
create unique index t1_i1 on t1(id);

execute snap_redo.start_snap

insert into t1 
select	object_id, object_name, object_name, object_name 
from	all_objects
where	rownum <= 10000 --> comment to avoid wordpress format issue
;

execute snap_redo.end_snap


drop table t1 purge;
create table t1 (id number(6), v1 varchar(4000), v2 varchar2(4000), v3 varchar2(4000));
create unique index t1_i1 on t1(id);

execute snap_redo.start_snap

insert into t1 
select	object_id, object_name, object_name, object_name 
from	all_objects
where	rownum <= 10000 --> comment to avoid wordpress format issue
;

execute snap_redo.end_snap

I’ve dropped and created the same table twice, once with varchar2(40) columns and once with varchar2(4000) columns.

I’ve created an index on a (non-character) column – the specific results vary depending on whether the index is unique or non-unique and whether or not you have the index, and whether or not the table already holds data and the effective clustering on the index columns etc. etc. but the critical difference between the two sets of results doesn’t go away.

I’ve inserted object_name values (maximum usage 32 bytes) into the varchar2() columns, inserting 10,000 rows with a single insert statement (so we should see optimum internal array processing).

The snap_redo package is one of my simple pre/post packages that calculates changes in values in some dynamic performance view – in this case it’s looking at v$sysstat (system statistics) for statistics relating to redo generation, which means you need to run this test on an otherwise idle instance. Here are the two sets of results from an instance of 11.2.0.4:

Name                                                                     Value
----                                                                     -----
messages sent                                                               11
messages received                                                           11
calls to kcmgcs                                                            313
calls to kcmgas                                                             37
calls to get snapshot scn: kcmgss                                           74
redo entries                                                               769
redo size                                                            1,317,008
redo wastage                                                             3,888
redo writes                                                                 11
redo blocks written                                                      2,664
redo write time                                                             10
redo blocks checksummed by FG (exclusive)                                2,242
redo ordering marks                                                          1
redo subscn max counts                                                       1
redo synch time                                                              7
redo synch time (usec)                                                  88,875
redo synch time overhead (usec)                                          1,810
redo synch time overhead count (<2 msec)                                    11
redo synch writes                                                           11
redo write info find                                                        11
undo change vector size                                                261,136
rollback changes - undo records applied                                      2
IMU undo allocation size                                                17,184


Name                                                                     Value
----                                                                     -----
messages sent                                                                8
messages received                                                            8
calls to kcmgcs                                                            222
calls to kcmgas                                                             56
calls to get snapshot scn: kcmgss                                           52
redo entries                                                            20,409
redo size                                                            5,606,872
redo buffer allocation retries                                               1
redo wastage                                                             1,248
redo writes                                                                  6
redo blocks written                                                     11,324
redo write time                                                             26
redo blocks checksummed by FG (exclusive)                                  571
redo ordering marks                                                         32
redo subscn max counts                                                       1
redo synch time                                                              6
redo synch time (usec)                                                  60,230
redo synch time overhead (usec)                                            159
redo synch time overhead count (<2 msec)                                     1
redo synch writes                                                            1
redo write info find                                                         1
undo change vector size                                              1,590,520
IMU undo allocation size                                                   144

Notice, particularly, the great change in the number of redo entries and the total redo size when the character columns are defined at varchar2(4000). Note particularly that the number of redo entries is roughly “2 * number of rows inserted” – that’s one for each row itself and one for each index entry. You can check the redo log content by dump the log file, of course (and Nikolay did), or you can take my word for it that Oracle is doing the equivalent of single row processing in the varchar2(4000) case and array processing in the varchar2(40) case.

When Oracle calculates that the row length definition (not data) is larger than the block size it falls back to single row processing; this can increase your redo generation significantly (one set of redo metadata per row instead of one per table block) and since the rate at which you can pump out redo is the ultimate rate at which you can load data this could have a significant impact on your data loading times. Declaring character columns as varchar2(4000) “just in case” is a bad idea.

Addendum (Sept 2018)

One of the strangest things about my own blog is that while searching for an article on one topic I often re-discover things I had forgotten about on a completely differentI topic . Today’s little bit of serendipity was finding a comment by Christo Kutrovsky that is relevant to the “long column definitions” while searching for something I’d written about using non-unique indexes to enforce unique constraints.

Here’s the link to the comment Christo made about the way that the mechanics of nested loop joins will change if the select list is greater than 8,100 bytes. (Inevitably the example raises the question of whether the limit could be related to the block size, or whether it’s an absolute value, and whether – three years later – it still applies in the latest versions of Oracle; but it’s an interesting point about how so many aspects of Oracle can run into strange edge cases.)

Update (Mar 2019)

Another related point has just been aired on the Oracle developer forum – where a database that had been upgraded to use extended character types (i.e. 32767 bytes) has run into a problem with function based indexes. A function returning a varchar2() alway returns the maximum length string – so if you try to create an index on such a function it has to fail with ORA-01450 since the string length will be larger than the typical 8KB block size and, indeed, larger than 75% of the largest possible Oracle block size.  Workarounds are available in the ODC thread.

Update (Nov 2019)

I’ve just been prompted to re-run the test on newer versions of Oracle, and the effect is still present in every version I’ve tested up to 19.3.0.0.

I’ve also written a memo to myself to test whether this also holds true for:

• The PL/SQL “forall” array-processing construct
• Inserting across database links

There’s a little discussion in the comments to a posting on db_links that raised these questions.

Update (Nov 2023)

Just tested on 23.2.0.0 – and the behaviour hasn’t changed.

If you check Tim Halls notes on Immutable Tables you’ll see that the “V2” immutable table adds 24 columns totaling about 21KB to the table definition and the “V1” adds 10 columns totaling about 6KB.  (In both cases only 2 “short” columns of the many extra columns appear to be used for the immutable functionality at present). This means that if you do request V2 tables any array based loading of the table will turn into single-row processing, because of columns your application can’t see and Oracle isn’t using (yet).