11g大对象数据新技术

2019独角兽企业重金招聘Python工程师标准>>>

Oracle Acs资深顾问罗敏 老罗技术核心感悟： 11g大对象数据新技术

2016/02/15 by Maclean Liu Leave a Comment

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

本文永久地址：http://www.askmaclean.com/?p=16572

IT系统不仅需要存储和处理大量的传统结构化数据，而且对各类半结构化，例如XML文档、Word文档等，以及非结构化的图片、图像、视频等信息的处理需求也日益增长。Oracle自8i开始就推出了大对象（LOB）技术，用于存储半结构化和非结构化的数据。

本章将首先介绍传统LOB技术的运用，并总结传统LOB技术的不足，然后将介绍Oracle 11g新一代的大对象处理技术：SecureFiles，以及将传统LOB向SecureFiles进行迁移的相关技术，最后介绍相关案例和进一步的参考资料。

传统LOB技术的运用

LOB字段分为存储二进制的BLOB字段、存储字符类型的CLOB、存储国家字符集的NCLOB，以及存储外部文件的BFILE等类型。LOB字段的设计和使用并不复杂，例如，以下就是创建一个包含LOB字段表的语句：

 

 

CREATE TABLE print_media
    ( product_id        NUMBER(6)
    , ad_id             NUMBER(6)
    , ad_composite      BLOB
    , ad_sourcetext     CLOB
    , ad_finaltext      CLOB
    , ad_fltextn        NCLOB
    , ad_textdocs_ntab  textdoc_tab
    , ad_photo          BLOB
    , ad_graphic        BFILE);

LOB表物理设计基本原则

LOB字段在物理设计和应用开发中，都具有独特的技术特征。以下是Oracle顾问根据国内外不同项目实施LOB字段的经验，提出的LOB表物理设计基本原则。这些原则的贯彻，将有效提高LOB字段的可管理型和性能：

 

• LOB表空间设计

为方便管理和处理的高性能，建议所有LOB字段单独建立表空间，并为每个LOB数据段单独命名。LOB表空间和数据段命名规则如下：

类型	命名
LOB表空间	TS_<基表名>_<LOB字段名>
LOB数据段	SEG_<基表名>_<LOB字段名>

如果在LOB字段建立索引，Oracle将LOB索引与LOB数据共同存储在LOB表空间。因此不需要建立LOB索引表空间。

另外建议LOB表空间的物理参数设计如下：

 

管理方式	分配方式	Uniform Size(M)	段空间管理
LOCAL	UNIFORM	256	AUTO
LOCAL	UNIFORM	256	AUTO

 

• ENABLE/DISABLE STORAGE IN ROW的确定

通过ENABLE/DISABLE STORAGE IN ROW的设置，当LOB字段值（包括控制信息）小于4K时，Oracle可将LOB字段值直接保存在基表或LOB段。

ENABLE STORAGE IN ROW为缺省值，其优点如下：

• 当大量LOB字段值小于4K时，则直接读取基表的记录，就可读取到LOB字段，减少了I/O次数，而且空间利用率较高。
• 当LOB字段值大于4K时，虽然LOB字段值被存储在LOB段，但LOB的控制信息依然存储在基表记录中，能加速LOB字段的访问。

DISABLE STORAGE IN ROW的优点如下：

• 当对基表的操作较多时，例如大量SELECT、UPDATE等，全表扫描、按范围扫描（range scans）时，基表不包括LOB字段值，处理效率更高。
• 基表和LOB字段值完全分开存储，有利于数据的管理。

基于上述特点分析，我们通常建议所有LOB表均采用DISABLE STORAGE IN ROW技术，即基表记录和LOB字段值完全分开存储，代价是空间消耗较大。

• CHUNK的确定

CHUNK值表示通过OCI或PL/SQL等访问LOB字段时，一次读取或写入LOB的数据量，缺省为一个数据块。CHUNK一般是数据块的倍数。

在某系统中，我们建议将CHUNK设定每个LOB字段的平均长度或频率最高的长度。即数据包的平均长度，例如，1M，2M等。

• PCTVERSION的确定

当LOB字段值（文稿）被修改时，Oracle将在LOB数据段保存原来的LOB值版本，以提供读取的一致性。

PCTVERSION表示了老版本LOB的消耗空间比例，缺省值为10%。PCTVERSION值主要取决于如下两个因素：

• LOB字段的修改频率？
• 被修改LOB字段的读取频率？

根据上述原理，我们为该系统的LOB字段的PCTVERSION值的确定，制定如下设置原则：

 

LOB值修改频率	读被修改的LOB频率	PCTVERSION
高	高	20
高	低	10
低	高	10
低	低	5
没有	没有	0

 

• CACHE/NOCACHE/CACHE READS的确定

Oracle在对LOB字段的处理时，可提供如下三种内存处理的方式：

• CACHE：将LOB字段值存储在内存中。
• NOCACHE：不将LOB字段值存储在内存中。或者存储在LRU列表的尾端，从而会容易被淘汰出内存。
• CACHE READS：只有在对LOB读取处理时，才存储在内存。

根据上述原理，我们在该系统建立如下原则：

CACHE方式	读频度	写频度
CACHE	高	高
NOCACHE	低	低
CACHE READS	高	低

 

• LOGGING/NOLOGGING的确定

同普通表的处理类似，LOB表日志方式的设计决定了日志文件的产生量和数据的可恢复性。以下是该系统LOB表日志文件的设计原则建议：

• 一般正常处理情况下，所有LOB表均设置为LOGGING方式。
• 在批量装载和插入LOB数据之前，建议将LOB表设置为NOCACHE NOLOGGING方式，从而降低LOG的产生量，从而保障数据物理备份的可行性。

传统LOB字段设计举例

根据客户提供的LOB表结构设计和处理特性分析，遵循上述LOB字段的设计原则，我们对该系统的LOB表字段设计如下：

 

表名称	LOB字段	LOB表空间名	LOB数据段名	大小（G）	CHUNK	PCTVERSION	CACHE
TB_URLCONTENT	F_CONTENTS CLOB	TS_TB_URLCONTENT_F_CONTENTS	SEG_TB_URLCONTENT_F_CONTENTS	23	4096	5	CACHE READS
	F_SNAPSHOT CLOB	TS_TB_URLCONTENT_F_SNAPSHOT	SEG_TB_URLCONTENT_F_SNAPSHOT	23	4096
TB_MATERIAL	F_CONTENTS CLOB	TS_TB_MATERIAL_F_CONTENTS	SEG_TB_MATERIAL_F_CONTENTS	16	4096	20	CACHE
TB_URLIMAGE	F_PICS BLOB	TS_TB_URLIMAGE_F_PICS	SEG_TB_URLIMAGE_F_PICS	92	16384	10	CACHE READS
…	…	…	…	…	…	…	…

 

以下就是LOB表的创建语句样本：

 

CREATE TABLE TB_URLCONTENT (
... ...
F_CONTENTS	CLOB,
F_SNAPSHOT	CLOB,
... ...
)
LOB (F_CONTENTS) STORE AS SEG_TB_URLCONTENT_F_CONTENTS (
TABLESPACE TS_TB_URLCONTENT_F_CONTENTS 
CHUNK 4096
PCTVERSION 5
CACHE READS
LOGGING
STORAGE(MAXEXTENTS UNLIMITED)
DISABLE STORAGE IN ROW);

传统LOB技术的不足

通过上述传统LOB技术的使用，可见欲做到充分满足LOB字段的可管理性和性能需求，还是需要下一番功夫的，而且传统LOB技术本身的如下局限，已很难满足高速增长的需求。

• 首先，传统LOB字段技术缺乏去重、压缩等功能，导致空间消耗过大。首发于askmaclean.com

例如，我们在分析某系统过程中发现，该系统数据库总容量640GB，其中LOB字段共消耗了600GB，最主要的两个LOB字段就消耗了550GB，占了数据库总量的85%以上，而且还在高速增长中。实际上，该系统的LOB字段存在大量重复数据和可压缩空间。

• LOB字段的大量物理属性难于设计和管理

传统LOB字段有CHUNK、LOGGING/NOLOGGING、PCTVERSION、CACHE/NOCACHE/CACHE READS、ENABLE/DISABLE STORAGE IN ROW等大量物理属性。根据每个LOB字段访问特征的不同，合理地设计这些物理属性，才能在存储效率、访问性能等方面达到良好的效果。但国内大部分采用LOB字段的IT系统，几乎缺乏这些物理属性的定制化设计，而是采用Oracle的缺省物理属性，这种状况很难满足需求。例如，CACH参数缺省值为NO，即Oracle在缺省情况下，读取LOB字段时没有进行缓存，显然不能满足LOB字段读写频度较高的需求。

• LOB字段存在一些局限

传统LOB字段存在一些明显的局限。例如存储数据容量有限，通常是几兆数据；CHUNK属性不仅为定长，而且有上限（32K），容易导致大量碎片；LOB字段对OLTP应用支持不好；在 RAC环境下，LOB字段会导致应用扩展性不好，等等。

 

新一代大对象处理技术: SecureFiles

为有效解决现有LOB字段技术存在的上述问题，Oracle 公司在11g版本中推出了针对 LOB字段处理的新技术：SecureFiles。该技术在性能、可管理性、易用性等方面，具有如下具体特点和优势：

• 提供数据去重、压缩和透明加密功能

SecureFiles不仅可以有效降低LOB字段存储空间消耗，提高了访问效率，而且提高了LOB字段的数据安全性。

以上述某系统为例，我们将其中一个100GB的LOB字段转换为SecureFiles，并采用压缩技术之后，最终只消耗30GB空间，大大压缩了存储空间。

• 新的网络协议

SecureFiles提供一种新的Client/Server方式的内部读写机制，有效提高了大量数据传输的效率。

• 简化物理属性设计和管理

SecureFiles提供了大量自动化的物理属性机制，免去了大量物理属性设计和管理工作。例如：CHUNK属性为可变长，最大能支持到64M；Oracle能自动进行碎片整理；SecureFiles还自动进行redo和undo的管理，避免大量不必要的redo和 undo信息的产生。

由于SecureFiles技术的推出，Oracle从11g开始将传统LOB技术称之为BASICFILE。

 

SecureFiles相关技术细节

• 启用SecureFiles技术

通过新的初始化参数DB_SECUREFILE，DBA可控制如何使用SecureFiles。DB_SECUREFILE的取值和含义如下：

• PERMITTED（缺省值）：允许使用SecureFiles技术。
• ALWAYS：将所有LOB字段缺省创建为SecureFiles。但如果该表所在表空间不是ASSM（Automatic Segment Space Management）表空间,则将LOB字段创建为BASICFILE。
• NERVER：禁止使用SecureFiles技术。所有定义为SecureFiles的LOB字段，将被创建为BASICFILE。如果定义了与SecureFiles相关的选项（例如：去重、压缩、加密等），将导致异常。
• IGNORE：忽略SecureFiles关键字和所有相关的选项。
• 创建表举例

以下语句创建一个包含LOB字段的表，并且具有去重功能，另外具有缓存、不保留日志的功能。

 

 

CREATE TABLE func_spec(
 id number, doc CLOB) 
 LOB(doc) STORE AS SECUREFILE 
 (DEDUPLICATE LOB CACHE NOLOGGING);

 

以下语句创建一个包含LOB字段的表，并且具有高密度压缩、但不去重功能，另外具有缓存、不保留日志的功能。

 

CREATE TABLE test_spec (
 id number, doc  CLOB) 
 LOB(doc) STORE AS SECUREFILE 
 (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING);

以下语句创建一个包含LOB字段的表，并且具有透明加密功能。

CREATE TABLE design_spec (id number, doc  CLOB) 
 LOB(doc) STORE AS SECUREFILE (ENCRYPT);

• 修改表举例

以下语句关闭去重功能：

ALTER TABLE t1 MODIFY LOB(a) ( KEEP_DUPLICATES );

以下语句开启去重功能：

ALTER TABLE t1 MODIFY LOB(a) ( DEDUPLICATE LOB );

以下语句关闭压缩功能：

ALTER TABLE t1 MODIFY LOB(a) ( NOCOMPRESS );

以下语句针对某个分区开启压缩功能：

ALTER TABLE t1 MODIFY PARTITION p1 LOB(a) ( COMPRESS HIGH );

• SecureFiles技术的监控

通过查询*_SEGMENTS、*_LOBS、*_LOB_PARTITIONS、*_PART_LOBS等视图，可了解LOB字段的相关信息。例如，如下语句将查询SECF_TBS2表空间中的LOB字段信息：

 

 

SQL> SELECT segment_name, segment_type, segment_subtype 
  2  FROM dba_segments 
  3  WHERE tablespace_name = 'SECF_TBS2'
  4  AND segment_type = 'LOBSEGMENT'
  5  /
 
SEGMENT_NAME                 SEGMENT_TYPE        SEGMENT_SU
---------------------------- ------------------  ----------
SYS_LOB0000071583C00004$$    LOBSEGMENT          SECUREFILE

另外，通过DBMS_LOB.GETOPTIONS可查询SecureFiles类型的LOB字段的相关选项，例如是否去重、压缩或加密。通过DBMS_LOB.SETOPTIONS可进行相应的设置工作。通过DBMS_SPACE.SPACE_USAGE可查询SecureFiles类型的LOB字段的空间消耗情况。

上述操作的详细语句略，详细情况请见Oracle 11g联机文档的《Oracle® Database PL/SQL Packages and Types Reference》有关DBMS_LOB包的说明。

SecureFiles迁移方法

通过普通技术，例如CTAS/ITAS、Export/Import等可以将传统LOB字段转换为SecureFiles，但这些技术可能导致业务停顿时间过长。下面介绍两种更有效的技术：

• 在线重定义技术

通过在线重定义技术(Online Redefinition)，可在业务不停顿的情况下，将传统LOB字段转换为SecureFiles。例如：

 

REM Grant privileges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;
REM Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;
CONNECT pm
DROP TABLE cust;
CREATE TABLE cust(c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
-- Creating Interim Table
-- There is no need to specify constraints because they are
-- copied over from the original table.
CREATE TABLE cust_int(c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
) LOB(c_lob) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);
DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'c_id c_id , '||
'c_zip c_zip , '||
'c_name c_name, '||
'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int',
    1, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');
-- Drop the interim table
DROP TABLE cust_int;
DESC cust;
-- The following insert statement fails. This illustrates
-- that the primary key constraint on the c_id column is
-- preserved after migration.
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
SELECT * FROM cust;

虽然在线重定义技术需要额外的空间，但仍然建议优先考虑该技术的运用。
 分区交换技术
通过分区交换技术，也可在尽量减少业务不停顿，将传统LOB字段转换为SecureFiles。该技术具有如下特点：
 与分区技术结合，需要该表最大分区的额外空间。
 在分区交换时，可同步进行本地化索引的维护。
 按分区进行操作，缩短维护窗口。
详细操作过程略。

 

 

SecureFiles实施案例

我们在上述曾介绍过，某系统数据库总容量640GB，其中使用传统LOB技术的字段共消耗了600GB，最主要的两个LOB字段就消耗了550GB，占了数据库总量的85%以上，而且还在高速增长中。实际上，该系统的LOB字段存在大量重复数据和可压缩空间。

于是，我们将这两个表采用传统的CTAS技术，将传统LOB字段转换为SecureFiles字段，结果是这两个表空间又550GB下降为160GB，空间几乎下降了70%！

以下就是详细脚本：

 

 

CREATE TABLE FORESTFIREEW.T_FORECAST_THEMATIC_NEW 
   (	THEMATIC_ID VARCHAR2(36) NOT NULL ENABLE, 
	RESULT_ID VARCHAR2(36) NOT NULL ENABLE, 
	THEMATIC_TYPE VARCHAR2(10) NOT NULL ENABLE, 
	DATA_NO NUMBER(10,0), 
	DATA_DATE CHAR(8), 
	DATA_HOUR CHAR(2), 
	DATA_TYPE VARCHAR2(10), 
	DATA_VALUE NUMBER(8,2), 
	DATA_LEVEL NUMBER(2,0), 
	GEOMETRY CLOB, 
	 CONSTRAINT PK_FORECAST_THEMATIC_NEW PRIMARY KEY (THEMATIC_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)  
  PARTITION BY RANGE (DATA_DATE) 
 (PARTITION SYS_P27  VALUES LESS THAN ('20120101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2011 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)  , 
 PARTITION SYS_P28  VALUES LESS THAN ('20130101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2012 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)  , 
 PARTITION SYS_P29  VALUES LESS THAN ('20140101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2013 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) , 
 PARTITION SYS_P30  VALUES LESS THAN ('20150101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2014 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) , 
 PARTITION SYS_P31  VALUES LESS THAN ('20160101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2015 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) , 
 PARTITION SYS_P32  VALUES LESS THAN (MAXVALUE) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2016 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) ); 
 
 
 insert /*+ append */into T_FORECAST_THEMATIC_NEW nologging select * from T_FORECAST_THEMATIC;
 
   CREATE TABLE FORESTFIREEW.T_LIVE_THEMATIC_NEW
   (	THEMATIC_ID VARCHAR2(36) NOT NULL ENABLE, 
	RESULT_ID VARCHAR2(36) NOT NULL ENABLE, 
	THEMATIC_TYPE VARCHAR2(10) NOT NULL ENABLE, 
	DATA_NO NUMBER(10,0), 
	DATA_DATE CHAR(8), 
	DATA_HOUR CHAR(2), 
	DATA_VALUE NUMBER(8,2), 
	DATA_LEVEL NUMBER(2,0), 
	GEOMETRY CLOB, 
	 CONSTRAINT PK_LIVE_THEMATIC_NEW PRIMARY KEY (THEMATIC_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) 
  PARTITION BY RANGE (DATA_DATE) 
 (PARTITION SYS_P51  VALUES LESS THAN ('20120101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2011 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P52  VALUES LESS THAN ('20130101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2012 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P53  VALUES LESS THAN ('20140101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2013 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P54  VALUES LESS THAN ('20150101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2014 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P55  VALUES LESS THAN ('20160101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2015 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P56  VALUES LESS THAN (MAXVALUE) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2016 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)); 

 insert /*+ append */into T_LIVE_THEMATIC_NEW nologging select * from T_LIVE_THEMATIC;

本章参考资料及进一步读物

本章参考资料及进一步读物：

序号	资料类别	资料名称	资料概述
1.	Oracle 11g R2联机文档	《Oracle® Database SecureFiles and Large Objects Developer’s Guide》	这是Oracle 11g联机文档中专门介绍SecureFiles和大对象开发的专著，从事大对象开发的人员必读之物。
2.	Oracle大学教材	《Oracle® Database 11g New Features》的第10课	这是Oracle大学教材。欲看到图文并茂的该文档，只能报名参加该课程的培训了。
3.	My Oracle Support	《Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)》	欲全面了解传统的BasicFiles大对象和新一代SecureFiles大对象，这篇文档就是主目录。
4.	My Oracle Support	《Summary Note Index for BasicFiles(LOB’s/BLOB’s/CLOB’s/NCLOB’s,BFILES) and SecureFiles (Doc ID 198160.1)》	又一个介绍传统的BasicFiles大对象和新一代SecureFiles大对象的文档集结地。
5.	My Oracle Support	《LOB Performance Guideline (Doc ID 268476.1)》	这篇文档针对传统BasicFiles大对象的性能问题进行了全面分析。既有设计问题，也有Oracle产品Bug问题，好好看看吧。其实更好的办法就是升级到11g，并采用SecureFiles技术。
6.	My Oracle Support	《LOBS – Storage, Redo and Performance Issues (Doc ID 66431.1)》	这篇文档介绍了传统BasicFiles大对象的存储特性、Redo等内部信息，以及可能存在的一些性能问题。重复一下：别纠结这些问题了，还是升级到11g，并采用SecureFiles技术吧。
7.	My Oracle Support	《POOR PERFORMANCE WITH LOB INSERTS (Doc ID 978045.1)》	LOB字段插入比较慢，什么原因？这篇文章给出了一个原因，那就是Recylebin！解决办法：关掉Recylebin！
8.	My Oracle Support	《Troubleshooting Guide (TSG) – Large Objects (LOBs) (Doc ID 846562.1)》	这篇文档介绍了如何诊断分析LOB字段故障的思路和方法。例如，如何确定问题？如何收集故障信息？如何研究和分析问题？如何提供解决方案？如何验证解决效果？这可是LOB字段故障诊断的宝典！

 

 

Filed Under: Oracle, Oracle LOB大对象

利用44951 event解决LOB SPACE enq HW – contention等待争用

2011/11/07 by Maclean Liu 2 Comments

对存有LOB大对象的表的并发插入、更新引起的LOB Segment High Water Mark是常见的LOb并发争用； 特别是在ASSM(Auto Segment Space Management)的表空间上这种LOB的HWM可能比MSSM(Manual Segment Space Management)更为严重，其原因是在MSSM下LOB的HWM bump一次会获取128个chunk， 而在ASSM下默认只获取必要的chunk(default 1) (This is worse in ASSM than MSSM because ASSM only gets the amount of space requested while MSSM gets 128 chunks at a time.)。

 

针对该LOB HWM高水位争用问题，常见的一种解决方法是为LOB segment预分配空间，因为预分配了空间所以出现高水位争用的机会少了，则 enq HW – contention争用出现的概率也随之降低，为LOB  allocate extent的语法如下：

 

ALTER TABLE <lob_table>
MODIFY LOB (<column_name>) (allocate extent (size <extent size>));

 

使用该方法手动为LOB SEGMENT预分配空间时需要注意，存在<NOTE 1229669.1 Bug 8198906 – Segment header corruption if extent allocation operation is interrupted>。

 

另一种缓解该LOB HW争用的有效方式是从 10.2.0.3上Bug 6376915引入的44951 event，该事件在10.2.0.4和11.1.0.7之后也都被引入。在10.2.0.3中使用该44951 event则需要优先apply Bug 6376915的one-off patch。

44951 event的LEVEL参数定义了在ASSM下当LOB segment的HWM上升时一次获取的chunks数目，一般推荐设置为1024，即一次get 1024个chunk，由于单次跃升的HWM更高了，这让进入enq HW – contention等待的机会变少了。 需要注意的是该44951 event仅仅对ASSM表空间上的LOB SEGMENT有效。

设置方法如下：

 

alter system set events ‘44951 trace name context forever, level 1024’;

 

 

以下是在11.2.0.3 中的实际测试，通过大量并发插入到LOB表模拟enq HW，并通过AWR报告中的性能信息比对设置该44951 event先后的区别：

 

 

 

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter event

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
event                                string
xml_db_events                        string      enable
SQL> 

conn maclean/oracle

 CREATE TABLE "MACLEAN_LOB" ( "T1" VARCHAR2(200) NOT NULL , "T2" CLOB, "T3" CLOB)  tablespace users
    LOB ("T2") 
    STORE AS  ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE ) 
    LOB ("T3") 
    STORE AS  ( TABLESPACE "USERS" CHUNK 16K PCTVERSION 50 CACHE );

SQL> select segment_space_management from dba_tablespaces where tablespace_name='USERS';

SEGMEN
------
AUTO

exec dbms_workload_repository.create_snapshot;

开3个进程并发插入LOB表

begin
for i in 1..10000 loop
 insert into maclean.maclean_lob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
 end loop;
 commit;
 end;
 /

 exec dbms_workload_repository.create_snapshot;

SQL>   select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');

BYTES/1024 SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
    490496 SYS_LOB0000076982C00003$$
    482304 SYS_LOB0000076982C00002$$

	SQL> truncate table maclean.maclean_lob;

Table truncated.

SQL> SQL> 
SQL> 
SQL>  select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');

BYTES/1024 SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
        64 SYS_LOB0000076982C00003$$
        64 SYS_LOB0000076982C00002$$

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

alter system set events '44951 trace name context forever, level 1024';

		 exec dbms_workload_repository.create_snapshot;

	开3个进程并发插入LOB表

begin
for i in 1..10000 loop
 insert into maclean.maclean_lob values ('ABC',rpad('Z',32000,'L'),rpad('Z',32000,'L'));
 end loop;
 commit;
 end;
 /	

 		 exec dbms_workload_repository.create_snapshot;

 select bytes/1024,segment_name from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='MACLEAN_LOB' and owner='MACLEAN');

BYTES/1024 SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
    483328 SYS_LOB0000076982C00003$$
    483328 SYS_LOB0000076982C00002$$

 

 

以上可以看到虽然设置了44951 level 1024，但并不会因为单次bump hwm的chunks数增加而导致大量空间的浪费。

对比AWR可以发现设置44961 level 1024后 enq HW – contention消耗的DB TIME明显减少：

 

 

 

 

此外在10.2.0.3之前还有一种方案即设置LOB的PCTVERSION 为0/100，但是该方案会导致LOB占用的SPACE大幅上升，所以不推荐，你有大量的理由至少升级DB到10.2.0.5.9。

 

 

Filed Under: Oracle, Oracle Internal Research内部原理研究, Oracle LOB大对象

[repost]Oracle RDBMS:Generic Large Object (LOB) Performance Guidelines

2011/09/11 by Maclean Liu Leave a Comment

Oracle的Giri Mandalika给我们介绍了LOB大型对象的一些调优注意事项，之前我一直对_shared_io_pool_size这个undocumented参数不甚了解，以为它是一个shared pool相关的参数，根本原因是甚至没有任何一个公开的Mos Note介绍了这几个隐藏参数。

而这篇文章给出了比较好的解释，这里引用一下：

This blog post is generic in nature and based on my recent experience with a content management system where securefile BLOBs are critical in storing and retrieving the checked in content. It is stro ngly suggested to check the official documentation in addition to these brief guidelines. In general, Oracle Database SecureFiles and Large Objects Developer’s Guide 11g Release 2 (11.2) is a good starting point when creating tables involving SecureFiles and LOBs.

Guidelines

• Tablespace: create the LOB in a different tablespace isolated from the rest of the database
• Block size: consider larger block size (default 8 KB) if the expected size of the LOB is big
• Chunk size: consider larger chunk size (default 8 KB) if larger LOBs are expected to be stored and retrieved
• Inline or Out-of-line: choose “DISABLE STORAGE IN ROW” (out-of-line) if the average LOB size is expected to be > 4 KB. The default inlining is fine for smaller LOBs
• CACHE or NOCACHE: consider bypassing the database buffer cache (NOCACHE) if large number of LOBs are stored and not expected to be retrieved frequently
• COMPRESS or NOCOMPRESS: choose COMPRESS option if storage capacity is a concern and a constraint. It saves disk space at the expense of some performance overhead. In a RAC database environment, it is recommended to compress the LOBs to reduce the interconnect traffic
• De-duplication: by default, duplicate LOBs are stored as a separate copy in the database. Choosing DEDUPLICATE option enables sharing the same data blocks for similar files thus reducing storage overhead and simplifying storage management
• Partitioning: consider partitioning the parent table to maximize application performance. Hash partitioning is one of the options if there is no potential partition key in the table
• Zero-Copy I/O protocol: turned on by default. Turning it off in a RAC database environment could be beneficial. Set the initialization parameter _use_zero_copy_io=FALSE to turn o ff the Zero-Copy I/O protocol
• Shared I/O pool: database uses the shared I/O pool to perform large I/O operations on securefile LOBs. The shared I/O pool uses shared memory segments. If this pool is not large enough or if there is not enough memory available in this pool for a securefile LOB I/O operation, Oracle uses a portion of PGA until there is sufficient memory available in the shared I/O pool. Hence it is recommen ded to size the shared I/O pool appropriately by monitoring the database during the peak activity. Relevant initialization parameters: _shared_io_pool_size and _shared_iop_max_size

Filed Under: Oracle, Oracle LOB大对象 Tagged With: hidden parameter, Oracle LOB大对象, PERFORMANCE, pool_size, _shared_iop_max_size, _use_zero_copy_io

转载于:https://my.oschina.net/rootliu/blog/1623496