LogMiner puzzle - CLOB datatype

From: Michael Rosenblum <mrosenblum_at_dulcian.com>
Date: Mon, 6 Feb 2012 15:27:36 -0500
Message-ID: <2673A0170447634DA7F7ABCA51E89FA9016CFB941876_at_MAIL2.dulcian.local>

Hello, everybody!
I need a bit of an advice whether the following behavior is wrong an requires SR to be initiated - or I am just missing something.


  • Oracle Enterprise Edition 64-bit on Win 2008.
  • Database is running in ARCHIVELOG mode with supplemental logging enabled

Test #1. Initial discovery of a problem
  1. Setup:
    • I created create a table MISHA_TEST that contains CLOB column create table misha_test (a number primary key, b_cl CLOB)
    • I run anonymous block that would insert into this table WITHOUT referencing CLOB column begin insert into misha_test (a) values (1); commit; end;
  2. I looked at generated logs via the LogMiner and found the following entries in V$LOGMNG_CONTENTS:

SQL_REDO set transaction read write;

insert into "AFRISSR"."MISHA_TEST"("A","B_CL") values ('1',EMPTY_CLOB());

set transaction read write;


update "AFRISSR"."MISHA_TEST" set "B_CL" = NULL where "A" = '1' and ROWID = 'AAAj90AAKAACfqnAAA';



  • why do we have two operations for a single insert - first write EMPTY_CLOB into B_CL and then update it to NULL? But I didn't even touch the column B_CL! Seems very strange - why can't we write NULL to B_CL from the very beginning?

Test #2. Quantification

  • having LOB column in the table seems to cause an overhead of generating more logs. But could it be quantified?


  • My understanding is that CLOBs defined with "storage in row enabled = true" (default) up to ~ 4k of size behave like Varchar2(4000) and only when the size goes above 4k we start using real LOB mechanisms.

Basic test:

  1. Two tables:
  2. With CLOB: create table misha_test_clob2 (a_nr number primary key, b_tx varchar2(4000), c_dt date, d_cl CLOB)
  3. With VARCHAR2 create table misha_test_clob (a_nr number primary key, b_tx varchar2(4000), c_dt date, d_cl VARCHAR2(4000))
  4. Switch logfile/Insert 1000 rows and populate only A_NR/Switch logfile

insert into misha_test_clob (a_nr)

select level

from dual

connect by level < 1001

3. Check sizes of generated logs:

  1. With CLOB - 689,664 bytes
  2. With Varchar2 - 509.440 (or about 26% reduction)


  • the overhead is real. It means that table with VARCHAR2 column is cheaper to maintain.
  • Having LOB columns in the table that has tons of INSERT operations is expensive.

So, does anybody care? Comments/suggestions are very welcome!

Thanks a lot!
Michael Rosenblum
Dulcian Inc

Received on Mon Feb 06 2012 - 14:27:36 CST

Original text of this message