Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

Chunk Setting for Secure Files

Bernd Eckenfels
Bernd Eckenfels Member Posts: 90
edited Feb 24, 2013 3:04AM in General Database Discussions
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

Answers

  • >
    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
  • Thank you, actually this paper was the reason I wondered about the details :) More details would be welcome.
  • 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))";
This discussion has been closed.