Chunk Setting for Secure Files

Bernd Eckenfels

According to SecureFiles documentation (11.2) and presentations the SecureFiles (BLOBs) do not use the CHUNK parameter in the same way as the BASICFiles: Oracle states that chunk size is managed dynamically, and the parameter is

for SecureFiles LOBs it is an advisory size provided for backward compatibility

Some other parameters (like PCTVERSION are "not used for SecureFiles" / "ignored". Now I wonder how the CHUNK parameter influences the storage of Secure Files. Is it something like a minimum size for chunks? Or is the decision on storage really only based on other parameters, if yes, which? Is using large ASSM UNIFROM Extends and INITIAL/NEXT Extend influencing anything? (I am using no compression, encryption or deduplication).

I could imagine the advice function of the parameter is only for DBAs or application querying it, but in that case I would have used the "ignored" term, like all the other legacy storage parameters. So there must be more to it.

(I know this is not really a "general" question, but I think this is the best fit forum, as it is not really ASM, Installation, Java or SQL specific.)

Edited by: Bernd Eckenfels on Feb 23, 2013 3:43 AM - added link

Unknown

>
Now I wonder how the CHUNK parameter influences the storage of Secure Files. Is it something like a minimum size for chunks? Or is the decision on storage really only based on other parameters, if yes, which?
>
There isn't a lot of documentation on SecureFile CHUNK but maybe this Oracle white paper will help some.
http://www.oracle.com/technetwork/database/options/compression/overview/securefiles-131281.pdf

See the section titled 'Reduced Fragmentation'

It says SecureFiles uses a dynamic CHUNK and some of the factors are:
1. The user-specified CHUNK value
2. size of the SecureFile,
3. availability of space in the segment

Bernd Eckenfels

Thank you, actually this paper was the reason I wondered about the details More details would be welcome.

Bernd Eckenfels

I did run some tests. They are single threaded inserts into a ASSM Tablespace with 8k blocksize. I inserted 100.000 blobs and calculated the average space usage of various blob sizes:

1 byte blob -> 8kb average (segmentsize/rowcount)
7kb -> 8kb
8kb ->17kb
15kb -> 17kb
16kb -> 25kb
23kb -> 25kb
24kb -> 34kb
31kb -> 34kb
33kb -> 42kb
34kb -> 42kb
40kb -> 50kb
47kb -> 50kb
49kb -> 59kb
62kb -> 67kb
63kb -> 75kb
70kb -> 75kb
71kb -> 84kb
78kb -> 84kb
79kb -> 93kb
81kb -> 93kb
110kb -> 117kb
111kb -> 122kb
118kb -> 123kb
119kb -> 131kb
125kb -> 130kb
126kb -> 138kb
128kb -> 139kb
256kb -> 267kb
10MB -> 10MB
64MB -> 66MB

So this looks like all data buffers are at least allocating 8k, for larger lobs the overhead fluctuates a bit, I guess this is related to the fact that I measured storage consumption by segment size.

But it looks indeed like there is some dynamic progression in the chunks used, which is visible because the wasted space above 16k increases.

I also tested mixing different LOB sizes in a single column to see if it choses a single chunk size for all, or if each lob has its own chunk size. I was mixing 7k and 64k lobs into a lob column with CHUNK 8k and CHUNK 32, and for both columns the average size was 42k, which matches very well 8k+75k/2.

What I also notices is, that the NEXT and PCTINCREASE parameters seems to behave a bit differently than for other segments. When I specify a NEXT of 8M, then the minimum initial growth is larger than without specifying NEXT, but in my case it started with 1MB. And even when I specified PCTINCREASE 0 it was increasing. (I guess this will only happen for LMT/ASSM and not in case of UNIFORM tablespace allocation).

Here is the extent size distribution with INITIAL 8M NEXT 8M (300 x 10MB LOBs):

(number of extends, size in blocks)
6 128
1 896
127 1024 <- 8M
1 7296
34 8192
(sum: 169 extends with 3262 MB)

And here is the extent distribution without specifying INITIAL/NEXT
1 16
58 128
121 1024
1 7168
34 8192
(sum: 215 extends with 3258 MB)

Here is the DDL used (with various CHUNK settings and various STORAGE params)

"CREATE TABLE TLOBTEST"+
" ( CLOB1 BLOB,"+
" CLOB2 BLOB)"+
" NOCOMPRESS LOGGING TABLESPACE USERS"+
" LOB (CLOB1) STORE AS SECUREFILE ("+
" TABLESPACE USERS DISABLE STORAGE IN ROW CHUNK 32k"+
" NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES "+
" STORAGE (INITIAL 8M NEXT 8M PCTINCREASE 0))"+
" LOB (CLOB2) STORE AS SECUREFILE ("+
" TABLESPACE USERS DISABLE STORAGE IN ROW CHUNK 8k"+
" NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES " +
" STORAGE (INIITAL 8M NEXT 8M PCTINCREASE 0))";