binary/textual data insert into BLOB/CLOB columns - use 50% less database (IO) resources

Introduction

There are applications that store binary or textual information in BLOB or CLOB columns.

Often I see that new row with new BLOB/CLOB data is inserted into database using one INSERT statement.

It is not optimal from performance point of view. You could reduce the IO work done by database about 50% if you could use the pattern described below.

Example code below is written in Java but the same logic applies to PL/SQL as well.

Test table structure

CREATE TABLE TESTFILE (ID NUMBER, FILE BLOB)
LOB (FILE) STORE AS SECUREFILE FILE_BLOB (DISABLE STORAGE IN ROW NOCACHE);

Option 1 - regular insert

private void insertWriteBlob(long pkValue, Connection c) throws Exception {
  // prepare blob (NB! this is temporary blob and data is written to temporary space)
  Blob blob = this.c.createBlob();
  writeBlobData(blob);
  // insert row using prepared blob (NB! temporary blob is copied to be permanent by insert)
  PreparedStatement ps = this.c.prepareStatement("insert into testfile values (?, ?)");
  ps.setLong(1, pkValue);
  ps.setBlob(2, blob);
  ps.executeUpdate();
  ps.close();
}
 

Option 2 - 50% cheaper way - write LOB after insert

private void selectForUpdateWriteBlob(long pkValue, Connection c) throws Exception {
  // insert row using empty temporary blob, insert makes it permanent
  PreparedStatement psIns = c.prepareStatement("insert into testfile values (?, ?)");
  psIns.setLong(1, pkValue);
  psIns.setBlob(2, c.createBlob());
  psIns.executeUpdate();
  // select empty permanent blob reference
  PreparedStatement psSel = c.prepareStatement("select file from testfile where id = ? for update");
  psSel.setLong(1, pkValue);
  ResultSet rs = psSel.executeQuery();
  rs.next();
  Blob blob = rs.getBlob(1);
  // write blob data directly to permanent blob
  writeBlobData(blob);
  psIns.close();
  psSel.close();
}

Explanation

Option 1:

1. First, BLOB object is created before INSERT statement is executed.
   Therefore, the row does not exist at the time of creating BLOB object.
   Therefore, BLOB object is created as temporary, it can not be related to table or row yet.
2. Then, row is INSERTed.
3. During execution of INSERT the "temporary BLOB" is copied to be part of table (BLOB becomes permanent).
   NB! avoiding that copy operation is the key!

Option 2:

1. First, row is inserted using empty temporary BLOB. Empty blob is created using createBlob() (java) or empty_blob() (pl/sql).
2. Then, permanent BLOB pointer is selected.
3. Then, BLOB data is written directly to permanent BLOB.
   NB! copy is avoided.

v$sesstat based measurement

10 files were inserted, 100 Mb each.

VALUE_1 is measured for option 1.

VALUE_2 is measured for option 2.

NAME	VALUE_1	VALUE_2	diff %
consistent changes	296993	1404	0
dirty buffers inspected	77135	0	0
hot buffers moved to head of LRU	50516	0	0
free buffer inspected	87386	14	0
write clones created in foreground	12	0	0
physical reads cache prefetch	133	0	0
prefetched blocks aged out before use	5	0	0
redo log space wait time	309	0	0
temp space allocated (bytes)	953155584	1048576	0
rollback changes - undo records applied	1	0	0
lob reads	116064	0	0
lob writes	29016	0	0
parse time cpu	1	0	0
parse time elapsed	1	0	0
shared hash latch upgrades - no wait	232813	1404	1
physical read bytes	2473984	24576	1
physical reads cache	302	3	1
physical read total bytes	2473984	24576	1
physical reads	302	3	1
physical read IO requests	169	3	2
physical read total IO requests	169	3	2
db block gets	1501542	58538	4
db block gets from cache	1501542	58538	4
free buffer requested	119422	4188	4
session logical reads	1624877	98528	6
redo log space requests	32	2	6
logical read bytes from cache	13310992384	807141376	6
db block changes	314127	20136	6
consistent gets pin (fastpath)	122604	9692	8
consistent gets pin	122704	9699	8
change write time	9	1	11
messages sent	356	61	17
consistent gets	123335	39990	32
consistent gets from cache	123335	39990	32
enqueue requests	4118	1445	35
enqueue releases	4116	1443	35
recursive calls	1660	773	47
session pga memory	7778408	3715176	48
session pga memory max	7778408	3715176	48
redo buffer allocation retries	2	1	50
non-idle wait count	117896	60207	51
non-idle wait time	1486	886	60
recursive cpu usage	8	5	63
enqueue waits	11	7	64
DB time	2287	1560	68
KTFB apply time (ms)	3051	2482	81
redo synch time (usec)	4759	3866	81
KTFB free time (ms)	561	472	84
Heap Segment Array Updates	7	6	86
CPU used when call started	919	811	88
CPU used by this session	919	811	88
KTFB commit time (ms)	352	312	89
segment chunks allocation from disepnser	718	678	94
segment dispenser allocations	254	247	97
cell physical IO interconnect bytes	979189760	964395008	98
physical write total multi block requests	1148	1126	98
securefile allocation chunks	771	753	98
physical write total bytes	976715776	964370432	99
physical write total IO requests	1462	1447	99
KTFB free req	189	188	99
securefile allocation bytes	865017856	863985664	100
table scan disk non-IMC rows gotten	11299	11345	100
KTFB free space (block)	130344	130216	100
table scan rows gotten	11299	11345	100
session connect time	1429909755	1429910011	100
process last non-idle time	1429909755	1429910011	100
bytes received via SQL*Net from client	946124986	947405272	100
securefile bytes non-transformed	943832448	943890480	100
physical write IO requests	1425	1434	101
index fetch by key	90	91	101
user calls	29071	29341	101
Requests to/from client	29057	29327	101
SQL*Net roundtrips to/from client	29058	29328	101
index scans kdiixs1	96	97	101
calls to get snapshot scn: kcmgss	175576	177339	101
securefile direct write ops	1425	1434	101
segment prealloc tasks	425	435	102
commit cleanouts	56	57	102
commit cleanouts successfully completed	56	57	102
segment cfs allocations	118	122	103
securefile number of non-transformed flushes	234	243	104
immediate (CURRENT) block cleanout applications	23	24	104
KTFB apply req	202	211	104
redo synch time overhead (usec)	3081	3227	105
execute count	217	229	106
parse count (total)	179	191	107
redo write info find	13	14	108
redo synch writes	13	14	108
redo synch time overhead count ( 2ms)	13	14	108
user I/O wait time	413	444	108
opened cursors cumulative	191	207	108
session cursor cache hits	92	99	108
file io wait time	3996654	4353931	109
redo entries	14448	15912	110
KTFB alloc time (ms)	1249	1404	112
redo size	27542172	31738136	115
undo change vector size	12159500	14241556	117
db block gets from cache (fastpath)	480	568	118
no work - consistent read gets	256	307	120
calls to kcmgcs	16208	20456	126
KTFB alloc space (block)	20185088	25821184	128
table scan blocks gotten	162	212	131
segment dispenser load tasks	189	253	134
enqueue conversions	25	34	136
table scans (short tables)	23	33	143
KTFB alloc req	22	33	150
sql area evicted	2	3	150
bytes sent via SQL*Net to client	2335870	3722091	159
switch current to new buffer	30	51	170
segment dispenser load empty	7	12	171
opened cursors current	5	9	180
sorts (memory)	11	20	182
calls to kcmgas	1099	2179	198
commit batch/immediate performed	1	2	200
commit immediate requested	1	2	200
commit batch/immediate requested	1	2	200
transaction rollbacks	1	2	200
cursor authentications	2	4	200
commit immediate performed	1	2	200
CCursor + sql area evicted	3	6	200
parse count (hard)	6	13	217
CR blocks created	512	1405	274
redo subscn max counts	19	54	284
sorts (rows)	4	13	325
application wait time	3	12	400
pinned cursors current	1	5	500
workarea executions - optimal	2	11	550
HSC Heap Segment Block Changes	53	296	558
IMU undo allocation size	792	4824	609
buffer is pinned count	16	259	1619
shared io pool buffer get success	72	1224	1700
consistent gets examination	631	30291	4800
consistent gets examination (fastpath)	587	30291	5160
buffer is not pinned count	255	58865	23084
table fetch by rowid	22	29326	133300
rows fetched via callback	19	29323	154332

Database version

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 

References 

Open this Oracle doc: "Working with LOBs and BFILEs"

and search for section: "Creating and Populating a BLOB, CLOB or NCLOB Column"

-- end --