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 的一个优势。
Comment