Performance with CLOB vs VARCHAR2

Performance with CLOB vs VARCHAR2

Oracle has introduce extended data type from 12c onwards, which means one can have more text data in varchar2 and raw data type.

Before 12c varchar2 and raw data type size,
VARCHAR2 : 4000 bytes
NVARCHAR2 : 4000 bytes
RAW : 2000 bytes

12c onwards varchar2 and raw data type size,,
VARCHAR2 : 32767 bytes
NVARCHAR2 : 32767 bytes
RAW : 32767 bytes

By default extended data type is not enable. This can be enable by setting initialize parameter MAX_STRING_SIZE using below command,

ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;

Above command should require database should be in upgrade mode and require restart after setting this parameter.

Check out detailed steps to enable extended data type here,
http://tanwanichandan.blogspot.com/2018/09/extended-data-type-in-oracle-12c.html

Now, let's compare the performance of CLOB and VARCHAR2
Here I have tested performance of Insert, Update and Select statements as below,

Performance of Insert Statements. CLOB vs VARCHAR2

CLOB Insert Trace




VARCHAR2 Insert Trace

Performance of Update Statements. CLOB vs VARCHAR2

CLOB Update Trace




VARCHAR2 Update Trace

Performance of Select Statements. CLOB vs VARCHAR2

CLOB Select Trace






VARCHAR2 Select Trace

Summary 

Operations	VARCHAR2(32767) (Trace Details)	CLOB (Trace Details)	Remarks
Insert	10  recursive calls 1170  db block gets 31  consistent gets 0  physical reads 436540  redo size	12  recursive calls 1254  db block gets 47  consistent gets 0  physical reads 552836  redo size	VARCHAR2 performance seems faster as compared with CLOB during Insert.
Update	CPU Cost 9% 5  recursive calls 2464  db block gets 259  consistent gets 0  physical reads 888472  redo size	CPU Cost 16% 6  recursive calls 2800  db block gets 352  consistent gets 0  physical reads 1176784  redo size	VARCHAR2 Less CPU, Less db block gets, Less consistent gets, Less redo size
Select	CPU Cost 9% 7  recursive calls 0  db block gets 129  consistent gets 0  physical reads 0  redo size 18522  bytes sent via SQL*Net to client 1350  bytes received via SQL*Net from client 68  SQL*Net roundtrips to/from client	CPU Cost 16% 8  recursive calls 0  db block gets 2263  consistent gets 0  physical reads 0  redo size 751491  bytes sent via SQL*Net to client 296624  bytes received via SQL*Net from client 2002  SQL*Net roundtrips to/from client	VARCHAR2 Less CPU, Less consistent gets, Less Recursive Calls, Less memory, Less Roundtrips to get data.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert