深入SecureFile—新一代LOB揭秘

carlos。zh

顶顶顶

Yong Huang

> 新版本咋不直接将BasicFile 去掉呢？是否意味着basicfile与securefile对比还有可取的地方呢？

Good question. In 12c, SecureFiles is the default. I guess Oracle just wants to slowly change these features to be cautious.

There's one little but important difference. BasicFiles does not support advanced features such as compression or deduplication. These features require a separate license. If Oracle dropped BasicFiles, and if you don't have the license, you would have to be careful not to specify the clause to use these features when you create a LOB column. But I guess it's a very small risk that could only be caused by a low probability accident.

By the way, Wing Hong continued the research on this topic almost three years ago. Somehow I missed it. A late Kudo to his excellent work!

carlos。zh

Yong Huang 发表于 2015-1-29 02:30
> 新版本咋不直接将BasicFile 去掉呢？是否意味着basicfile与securefile对比还有可取的地方呢？

Good qu ...

谢谢，我有个疑问，我是在11.2.0.3上测试导入lob数据
但效果反而反了，耗时长空间开销大，是不是跟我的lob的具体数据有关呢？
我刚刚导入了个800M LOB字段测试了下
导入如果目标表lob为basicfile 需要83s   空间仍然需要800M
测试都是用的默认参数
LOB (PHOTO) STORE AS (
  TABLESPACE  LOBS
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  PCTVERSION  10
  NOCACHE
  LOGGING

可当导入目标表lob为securefile，却需要98s  空间需要880M存储（不是说chunk大小可变，这样更易节省空间吗？）
LOB (PHOTO) STORE AS SECUREFILE (
  TABLESPACE  LOBS
  ENABLE      STORAGE IN ROW
  CHUNK       8192   ----建空表的时候没有指定chunk大小，系统默认
  NOCACHE
  LOGGING

Yong Huang

Better performance of SecureFiles LOBs is probably observed during actual usage of the LOB, not during its creation. Try testing the performance of a query that reads the LOB.

Space usage may not be smaller for SecureFiles LOB unless you enable compression, which is a feature not available for BasicFiles LOB.

carlos。zh

Yong Huang 发表于 2015-2-3 04:48
Better performance of SecureFiles LOBs is probably observed during actual usage of the LOB, not duri ...

谢谢 ~~ 已经测试 ~~

Yong Huang

And the test result is?

carlos。zh

Yong Huang 发表于 2015-2-6 00:08
And the test result is?

在  nocache+logging 下insert 400M的文本
securefile 速度是basicfile 的3倍
但空间需要多花80M左右

Yong Huang

Thanks for your test. The performance advantage is expected. I didn't know SecureFile LOB would take so much more space than BasicFile LOB. If you also test SecureFile with compression, please let us know the result. Thanks.

carlos。zh

Yong Huang 发表于 2015-2-7 23:05
Thanks for your test. The performance advantage is expected. I didn't know SecureFile LOB would take ...

我在测试环境下 securefile 使用了压缩
450M 使用medium压缩参数  段大小变为18M左右
如果使用high压缩参数 段大小变为15M左右
另外值得一提的是
比如我插入了200M LOB文件属性为securefile，在数据库中查看段大小约为450M
如果我此时将该段移动一次表空间（相当于重新整理）大小变为400M左右，此时的空间开销和basicfile差不多

Yong Huang

Thanks for the test. Compressed Securefile LOB is impressive in space saving. I hope performance doesn't significantly suffer due to compression.

If you use alter table move to re-org the Securefile LOB segment, why not also do the same to Basicfile LOB? Every time you alter table move (or alter index rebuild), the segment size usually goes down. So for the comparison to be fair, the LOB re-org should be done to both types of LOB.