securefile allocation chunks

11g中对于LOB对象引入了securefile特性，相对应的对于securefile的统计信息也被大量加入，例如对于旧的oldfile LOB大对象的CHUNK分配是没有具体的STATISTICS来统计的（到11.2.0.3都没有这样的STATISTICS来统计传统LOB的CHUNK分配、回收等等操作)，而对于SECUREFILE则有很详尽的STATISTICS:    

  1* select name,value from v$sysstat where upper(name) like '%CHUNK%' or upper(NAME) LIKE '%SECUREFILE%'
SQL> /

NAME                                                    VALUE
-------------------------------------------------- ----------
segment chunks allocation from disepnser                    0
segment total chunk allocation                              0
securefile allocation bytes                                 0
securefile allocation chunks                                0
securefile direct read bytes                                0
securefile direct write bytes                               0
securefile direct read ops                                  0
securefile direct write ops                                 0
securefile inode read time                                  0
securefile inode write time                                 0
securefile inode ioreap time                                0
securefile bytes non-transformed                            0
securefile number of non-transformed flushes                0
securefile bytes encrypted                                  0
securefile bytes cleartext                                  0
securefile compressed bytes                                 0
securefile uncompressed bytes                               0
securefile bytes deduplicated                               0
securefile create dedup set                                 0
securefile destroy dedup set                                0
securefile add dedupd lob to set                            0
securefile rmv from dedup set                               0
securefile reject deduplication                             0
securefile dedup prefix hash match                          0
securefile number of flushes                                0
securefile dedup flush too low                              0
securefile dedup callback oper final                        0
securefile dedup hash collision                             0
securefile dedup fits inline                                0
securefile dedup wapp cache miss                            0

30 rows selected.

SQL> CREATE TABLE t1 ( a CLOB)
  2      LOB(a) STORE AS SECUREFILE  (cache);
CREATE TABLE t1 ( a CLOB)
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "SYSTEM"

SQL> create table t1 ( a clob)  lob(a) store  as securefile(cache) tablespace users;

Table created.

SQL> insert into t1 values(rpad('d',99999,'Z'));

1 row created.

SQL> commit;

Commit complete.

SQL> select name,value from v$sysstat where upper(name) like '%CHUNK%' or upper(NAME) LIKE '%SECUREFILE%';

NAME                                                    VALUE
-------------------------------------------------- ----------
segment chunks allocation from disepnser                    1
segment total chunk allocation                             34
securefile allocation bytes                              8192
securefile allocation chunks                                1
securefile direct read bytes                                0
securefile direct write bytes                               0
securefile direct read ops                                  0
securefile direct write ops                                 0
securefile inode read time                                  0
securefile inode write time                                 0
securefile inode ioreap time                                0
securefile bytes non-transformed                         8000
securefile number of non-transformed flushes                1
securefile bytes encrypted                                  0
securefile bytes cleartext                                  0
securefile compressed bytes                                 0
securefile uncompressed bytes                               0
securefile bytes deduplicated                               0
securefile create dedup set                                 0
securefile destroy dedup set                                0
securefile add dedupd lob to set                            0
securefile rmv from dedup set                               0
securefile reject deduplication                             0
securefile dedup prefix hash match                          0
securefile number of flushes                                0
securefile dedup flush too low                              0
securefile dedup callback oper final                        0
securefile dedup hash collision                             0
securefile dedup fits inline                                0
securefile dedup wapp cache miss                            0

30 rows selected.





SQL>  create table t2 ( a clob) tablespace users;

Table created.

SQL>  insert into t2 values(rpad('d',99999,'Z'));

1 row created.

SQL> commit;

Commit complete.

SQL> set linesize 200 pagesize 2000
SQL> col name for a50
SQL> select name,value from v$sysstat where upper(name) like '%CHUNK%' or upper(NAME) LIKE '%SECUREFILE%';

NAME                                                    VALUE
-------------------------------------------------- ----------
segment chunks allocation from disepnser                    1
segment total chunk allocation                             34
securefile allocation bytes                              8192
securefile allocation chunks                                1
securefile direct read bytes                                0
securefile direct write bytes                               0
securefile direct read ops                                  0
securefile direct write ops                                 0
securefile inode read time                                  0
securefile inode write time                                 0
securefile inode ioreap time                                0
securefile bytes non-transformed                         8000
securefile number of non-transformed flushes                1
securefile bytes encrypted                                  0
securefile bytes cleartext                                  0
securefile compressed bytes                                 0
securefile uncompressed bytes                               0
securefile bytes deduplicated                               0
securefile create dedup set                                 0
securefile destroy dedup set                                0
securefile add dedupd lob to set                            0
securefile rmv from dedup set                               0
securefile reject deduplication                             0
securefile dedup prefix hash match                          0
securefile number of flushes                                0
securefile dedup flush too low                              0
securefile dedup callback oper final                        0
securefile dedup hash collision                             0
securefile dedup fits inline                                0
securefile dedup wapp cache miss                            0

如以上演示 传统的oldfile的LOB大对象不会导致securefile allocation chunks的变化， securefile 拥有更详细的STATISTICS统计，这让深入分析优化成为可能，也是securefile 的一个优势。