From Oracle documentation (for basicfile lobs) A chunk is one or more Oracle blocks. You can specify the chunk size for the BasicFiles LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database 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. 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. Once the value of CHUNK is chosen (when the LOB column is created), it cannot be changed. Hence, it is important that you choose a value which optimizes your storage and performance requirements.
For securefile CHUNK is an advisory size and is provided for backward compatibility purposes.
From performance perspective it is considered that accessing lobs in big chunks is more efficient. You can set CHUNK to the data size most frequently accessed or written. For example, if only one block of LOB data is accessed at a time, then set CHUNK to the size of one block. If you have big LOBs, and read or write big amounts of data, then choose a large value for CHUNK.
This post shows the result of a test case carried out to compare the performance benefits of using a large chunk size along with a tablespace with a large block size (8k vs 32k). The blob used for the test case is 800KB. The java code used for the test case is given at the end of the post. For each chunk size (8k vs 32k) the caching option was also changed (nocache vs cache) as they also have direct impact on the IO usage. The lob is stored out of row in a separate tablespace than the table.
The table creation DDL used for basicfile test is shown below (comment and uncomment each option based on the test case). The LOB32KTBS is a tablespace of 32k block size while LOB8KTBS is a tabelspace of 8k block size.
create table ses(sesid varchar2(100), sesob blob) SEGMENT CREATION IMMEDIATE TABLESPACE users
LOB
(
sesob
)
STORE AS object_lob_seg (
TABLESPACE LOB32KTBS
--TABLESPACE LOB8KTBS
DISABLE STORAGE IN ROW
CHUNK 32K
--CHUNK 8K
CACHE
--NOCACHE
PCTVERSION 0
STORAGE (MAXEXTENTS UNLIMITED)
INDEX object_lob_idx (
TABLESPACE LOB32KTBS
--TABLESPACE LOB8KTBS
STORAGE (MAXEXTENTS UNLIMITED)
)
)
/Test cases comprised of reading a lob column for a row and inserting lobs. The IO related statistics comparison for select test case is given below. Based on the graphs it could be seen that 32K chunk size on a tablespace with a block size of 32K requires less number of logical or physical reads compared to having a 8k chunk and lob segment on a 8k block size tablespace. Though not shown on the graphs, on a separate test where using a 32k chunk size and placing the lob segment on a 8K block size tablespace had the same performance characteristics of having a 8k chunk on a 8k block size tablespace. On the other hand having a chunk of 8k and placing the lob segment on a 32k block size tablespace had the same performance characteristics of having a 32k chunk on a 32k block size tablespace. This means that chunk size alone is not going to reduce the amount of IO but the tablespace block size where the lob segment is stored has an influence as well.
The next test was the inserting of lob. The results are shown on the following two graphs. Similar to the read test, having a large chunk size and tablespace block size for lob reduces the IO.
The same test was carried out for securefile lob segments. The table creation DDL is given below. Only difference to the DDL compared to basicfile is the "retention none". All other parameters/options are the same.
create table ses(sesid varchar2(100), sesob blob) SEGMENT CREATION IMMEDIATE TABLESPACE users
LOB
(
sesob
)
STORE AS securefile object_lob_seg (
TABLESPACE LOB32KTBS
--TABLESPACE LOB8KTBS
DISABLE STORAGE IN ROW
CHUNK 32K
--CHUNK 8K
CACHE
--NOCACHE
RETENTION NONE
STORAGE (MAXEXTENTS UNLIMITED)
INDEX object_lob_idx (
TABLESPACE LOB32KTBS
--TABLESPACE LOB8KTBS
STORAGE (MAXEXTENTS UNLIMITED)
)
)
/The results of the select test is shown on the graphs below. Similar to basicfile the larger chunk size advisory and tablespace block size combination out perform the smaller chunk/block size combination. In all cases the securefile out perform basicfile for the amount of logical or physical reads.
The outcome for the insert test also same as that of basicfile insert test where larger chunk/block size combination out performs the smaller chunk/block size combination. Also between basicfile and secfile the secfile out performs the basicfile.
This tests have shown that it's better to use large chunk/tablespace block sizes for larger LOBs to reduce logical/physical IO related to LOBs.
Useful White papers
SecureFile Performance
Oracle 11g: SecureFiles
Related Post
Nologging vs Logging for LOBs and enq: CF - contention
Java code used for the test. For the code of LobStat class refer the earlier post
public class LobChunkTest {
final String URL = "jdbc:oracle:thin:@192.168.0.66:1521:ent11g2";
final String USERNAME = "asanga";
final String PASSWORD = "asa";
public static void main(String[] args) {
LobChunkTest test = new LobChunkTest();
//Insert test
test.insertTest();
System.out.println("\n\n************* end of insert test **************\n\n");
//select test
test.selectTest();
}
public void insertTest() {
try {
OracleDataSource pool = new OracleDataSource();
pool.setURL(URL);
pool.setUser(USERNAME);
pool.setPassword(PASSWORD);
Connection con = pool.getConnection();
con.setAutoCommit(false);
long t1 = System.currentTimeMillis();
LobStat.displayStats(con);
byte[] x = new byte[800 * 1024];
x[1] = 10;
x[798 * 1024] = 20;
for (int i = 0; i < 100; i++) {
OraclePreparedStatement pr = (OraclePreparedStatement) con.prepareStatement("insert into ses values(?,?)");
String sesid = "abcdefghijklmnopqrstuvwxy" + Math.random();
pr.setString(1, sesid);
pr.setBytes(2, x);
pr.execute();
con.commit();
pr.close();
}
long t2 = System.currentTimeMillis();
LobStat.displayStats(con);
con.close();
System.out.println("time taken " + (t2 - t1));
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void selectTest() {
try {
OracleDataSource pool = new OracleDataSource();
pool.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");
pool.setUser("asanga");
pool.setPassword("asa");
Connection con = pool.getConnection();
con.setAutoCommit(false);
String[] sesids = new String[100];
PreparedStatement pr1 = con.prepareStatement("select sesid from ses");
ResultSet rs1 = pr1.executeQuery();
int i = 0;
while (rs1.next()) {
sesids[i] = rs1.getString(1);
i++;
}
rs1.close();
pr1.close();
con.close();
con = pool.getConnection();
LobStat.displayStats(con);
OraclePreparedStatement pr = (OraclePreparedStatement) con.prepareStatement("select SESOB from ses where sesid=?");
long t1 = System.currentTimeMillis();
for (String x : sesids) {
pr.setString(1, x);
ResultSet rs = pr.executeQuery();
while (rs.next()) {
byte[] blob = rs.getBytes(1);
}
rs.close();
}
long t2 = System.currentTimeMillis();
System.out.println("time taken " + (t2 - t1));
LobStat.displayStats(con);
pr.close();
con.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
