Note: Tested in Oracle 12c, 19c on Linux, Solaris, AIX with DB_BLOCK_SIZE=8192 and tablespace BLOCK_SIZE=8192.
1. SQL LOB Chunksize
First we create a table containing CLOB and BLOB columns with different chunk values, and insert one row:
drop table tab_lob cascade constraints;
create table tab_lob(
id number,
clob_8k clob,
blob_8k blob,
clob_16k clob,
blob_16k blob,
clob_32k clob,
blob_32k blob
)
lob (clob_8k) store as basicfile (
enable storage in row
chunk 8192)
lob (blob_8k) store as basicfile (
enable storage in row
chunk 8192)
lob (clob_16k) store as basicfile (
enable storage in row
chunk 16384)
lob (blob_16k) store as basicfile (
enable storage in row
chunk 16384)
lob (clob_32k) store as basicfile (
enable storage in row
chunk 32768)
lob (blob_32k) store as basicfile (
enable storage in row
chunk 32768);
insert into tab_lob values (1, empty_clob(), empty_blob(), empty_clob(), empty_blob(), empty_clob(), empty_blob());
commit;
select id,
dbms_lob.getchunksize(clob_8k) clob_8k,
dbms_lob.getchunksize(blob_8k) blob_8k,
dbms_lob.getchunksize(clob_16k) clob_16k,
dbms_lob.getchunksize(blob_16k) blob_16k,
dbms_lob.getchunksize(clob_32k) clob_32k,
dbms_lob.getchunksize(blob_32k) blob_32k
from tab_lob;
ID CLOB_8K BLOB_8K CLOB_16K BLOB_16K CLOB_32K BLOB_32K
--- --------- --------- ---------- ---------- ---------- ----------
1 8132 8132 16264 16264 32528 32528
Here Oracle Docu on DBMS_LOB.GETCHUNKSIZE (19c):
DBMS_LOB.GETCHUNKSIZE Functions When creating the table, you can specify the chunking factor, a multiple of tablespace blocks in bytes. This corresponds to the chunk size used by the LOB data layer when accessing or modifying the LOB value. Part of the chunk is used to store system-related information, and the rest stores the LOB value. This function returns the amount of space used in the LOB chunk to store the LOB value.The maximum CHUNK value is 32768 (32K), which is the largest Oracle Database block size allowed as documented in 19c LOB Storage with Applications :
LOB Storage CHUNK If the tablespace block size is the same as the database block size, then CHUNK is also a multiple of the database block size. The default CHUNK size is equal to the size of one tablespace block, and the maximum value is 32K.
2. Plsql and Java LOB Chunksize and BufferSize
We will look two sources of Java LOB objects, one is created in DB and passed to OracleJVM, another is directly created in OracleJVM. Besides chunksize similar to above SQL, Java LOB object has a bufferSize.
Here the test code:
drop java source "LobChunkBufferSize";
create or replace and compile java source named "LobChunkBufferSize" as
import java.sql.Connection;
import java.io.IOException;
import java.sql.SQLException;
import oracle.jdbc.OracleDriver;
import oracle.sql.CLOB;
import oracle.sql.BLOB;
public class LobChunkBufferSize {
public static void printSizeFromDB(CLOB clob, BLOB blob) throws Exception {
System.out.println("Java CLOB ChunkSize: " + clob.getChunkSize() + ", BufferSize: " + clob.getBufferSize());
System.out.println("Java BLOB ChunkSize: " + blob.getChunkSize() + ", BufferSize: " + blob.getBufferSize());
}
public static void printSizeFromOracleJvm() throws Exception {
Connection conn = new OracleDriver().defaultConnection();
CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
System.out.println("OracleJvm CLOB ChunkSize: " + clob.getChunkSize() + ", BufferSize: " + clob.getBufferSize());
BLOB blob = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
System.out.println("OracleJvm BLOB ChunkSize: " + blob.getChunkSize() + ", BufferSize: " + blob.getBufferSize());
}
}
/
create Or replace procedure printSizeFromDB (p_clob clob, p_blob blob)
as language java name 'LobChunkBufferSize.printSizeFromDB(oracle.sql.CLOB, oracle.sql.BLOB)';
/
create Or replace procedure printSizeFromOracleJvm
as language java name 'LobChunkBufferSize.printSizeFromOracleJvm()' ;
/
set serveroutput on size 50000;
exec dbms_java.set_output(50000);
declare
l_clob clob;
l_blob blob;
procedure print (l_name varchar2, l_clob clob, l_blob blob) as
begin
dbms_output.put_line(l_name);
dbms_output.put_line('Plsql CLOB ChunkSize: '||dbms_lob.getchunksize(l_clob));
dbms_output.put_line('Plsql BLOB ChunkSize: '||dbms_lob.getchunksize(l_blob));
printSizeFromDB(l_clob, l_blob);
end;
begin
select clob_8k, blob_8k into l_clob, l_blob from tab_lob where id = 1;
print('------- 1. DB Lob 8k -------', l_clob, l_blob);
select clob_16k, blob_16k into l_clob, l_blob from tab_lob where id = 1;
print('------- 2. DB Lob 16k -------', l_clob, l_blob);
select clob_32k, blob_32k into l_clob, l_blob from tab_lob where id = 1;
print('------- 3. DB Lob 32k -------', l_clob, l_blob);
dbms_lob.createtemporary(l_clob, TRUE);
dbms_lob.createtemporary(l_blob, TRUE);
print('------- 4. Temporary Lob empty -------', l_clob, l_blob);
l_clob := 'ABCabc123';
l_blob := utl_raw.cast_to_raw(l_clob);
print('------- 5. Temporary Lob small -------', l_clob, l_blob);
dbms_output.put_line('----------**** CLOB length: '||dbms_lob.getlength(l_clob));
l_clob := lpad('ABCabc123', 32767, 'A');
l_blob := utl_raw.cast_to_raw(l_clob);
print('------- 6. Temporary Lob big -------', l_clob, l_blob);
dbms_output.put_line('----------**** CLOB length: '||dbms_lob.getlength(l_clob));
end;
/
------- 1. DB Lob 8k -------
Plsql CLOB ChunkSize: 8132
Plsql BLOB ChunkSize: 8132
Java CLOB ChunkSize: 8132, BufferSize: 32528
Java BLOB ChunkSize: 8132, BufferSize: 32528
------- 2. DB Lob 16k -------
Plsql CLOB ChunkSize: 16264
Plsql BLOB ChunkSize: 16264
Java CLOB ChunkSize: 16264, BufferSize: 32528
Java BLOB ChunkSize: 16264, BufferSize: 32528
------- 3. DB Lob 32k -------
Plsql CLOB ChunkSize: 32528
Plsql BLOB ChunkSize: 32528
Java CLOB ChunkSize: 32528, BufferSize: 32528
Java BLOB ChunkSize: 32528, BufferSize: 32528
------- 4. Temporary Lob empty -------
Plsql CLOB ChunkSize: 8132
Plsql BLOB ChunkSize: 8132
Java CLOB ChunkSize: 8132, BufferSize: 32528
Java BLOB ChunkSize: 8132, BufferSize: 32528
------- 5. Temporary Lob small -------
Plsql CLOB ChunkSize: 4000
Plsql BLOB ChunkSize: 4000
Java CLOB ChunkSize: 4000, BufferSize: 32000
Java BLOB ChunkSize: 4000, BufferSize: 32000
----------**** CLOB length: 9
------- 6. Temporary Lob big -------
Plsql CLOB ChunkSize: 4000
Plsql BLOB ChunkSize: 4000
Java CLOB ChunkSize: 4000, BufferSize: 32000
Java BLOB ChunkSize: 4000, BufferSize: 32000
----------**** CLOB length: 32767
Now we look ChunkSize and BufferSize for LOB created in OracleJVM:
SQL > exec printSizeFromOracleJvm;
OracleJvm CLOB ChunkSize: 8132, BufferSize: 32528
OracleJvm BLOB ChunkSize: 8132, BufferSize: 32528
If we open Java class BLOB, we can see that getBufferSize is computed based on getChunkSize (all calculations are Java integer arithmetic), for example, if ChunkSize is 8132, BufferSize is 32528 (4*8132). Its maximum value is hard-coded as 32768 (probably the same limit as largest allowed Oracle Database block size).
public class BLOB extends DatumWithConnection implements Blob {
public int getBufferSize() throws SQLException {
...
int size = getChunkSize();
int ret = size;
if ((size >= 32768) || (size <= 0)) {
ret = 32768;
} else {
ret = 32768 / size * size;
}
...
return ret;
}
SET LOBPREFETCH {0 | n}
