[20140731]关于LOB字段存储特性4.txt

[20140731]关于LOB字段存储特性4.txt

--12c开始建立表如果包含lob，缺省建立的是securefile，securefile支持一些新特性，比如： Deduplication, Compression and Encryption.

--在11g下测试securefile看看。

SCOTT@test> @ver

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

CREATE TABLE "SCOTT"."T1"
   (    "ID" NUMBER,
        "IDX" NUMBER,
        "COL1" CLOB,
        "COL2" CLOB,
        "COL3" CLOB
   )
LOB ("COL1") STORE AS SECUREFILE
LOB ("COL2") STORE AS SECUREFILE
LOB ("COL3") STORE AS SECUREFILE (disable storage in row);

create unique index i_t1_id on t1(id);
insert into t1 values (1,1,lpad('a',100,'a'),lpad('b',4000,'b'),lpad('c',4000,'c'));
commit ;
execute dbms_stats.gather_table_stats(user,'t1',cascade=>true,method_opt=>'for all columns size 1',no_invalidate=>false);

--安装的语言选择NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK.这样即使1个英文字符也占用2个字节.
--这样都是插入8000字节,lob的信息保存在块外（我的数据库是8k的).
--题外话题:最好使用blob类型,这样可以原样保存,特别保存的文本是英文的情况下,空间浪费比较严重,对于中文字符集.
--col1=lpad('a',100,'a'),应该在块内。
--col2=lpad('b',4000,'b'),长度超出3964，应该在块外。
--col3=lpad('c',4000,'c'),使用DISABLE STORAGE IN ROW，无论如何都在块外。

SCOTT@test> SELECT DBMS_METADATA.get_ddl ('TABLE','T1') from dual ;
DBMS_METADATA.GET_DDL('TABLE','T1')
----------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T1"
   (    "ID" NUMBER,
        "IDX" NUMBER,
        "COL1" CLOB,
        "COL2" CLOB,
        "COL3" CLOB
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  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 "USERS"
LOB ("COL1") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("COL2") STORE AS SECUREFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
LOB ("COL3") STORE AS SECUREFILE (
  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

SCOTT@test> set autot trace
SCOTT@test> select id,idx from t1 where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
--注意如果出现recursive calls0,可以执行多次。可以发现仅仅2个逻辑读。

SCOTT@test> select id,col1 from t1 where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
--col1的信息长度

SCOTT@test> select id,col2 from t1 where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size

--如果col2是ENABLE STORAGE IN ROW,由于长度大于3964,保存在块外,导致 consistent gets增加到4,同时存在
--2个physical reads,无论执行多少次.

SCOTT@test> select id,col3 from t1 where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size

--对比发现，ENABLE/DISABLE STORAGE IN ROW两者的逻辑读一样对于securefile，都是4个逻辑读，而且每次都是2个物理读。
--在块内字段col1外，执行的语句select id,idx from t1 where id=1 与select id,col1 from t1 where id=1，两者的逻辑读一样。

先做一个转储：

SCOTT@test> select rowid,id,idx from t1 where id=1;
ROWID                      ID        IDX
------------------ ---------- ----------
AABGjbAAEAAAAWmAAA          1          1

SCOTT@test> @lookup_rowid   AABGjbAAEAAAAWmAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    288987          4       1446          0 4,1446               alter system dump datafile 4 block 1446

SCOTT@test> alter system dump datafile 4 block 1446;
System altered.

Block header dump:  0x010005a6
Object id on Block? Y
seg/obj: 0x468db  csc: 0x02.a6127dc2  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10005a0 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.000.00006a25  0x00c002fb.2321.1e  --U-    1  fsc 0x0000.a6127ee0
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x010005a6
data_block_dump,data header at 0x2a9751e264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x2a9751e264
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1e5a
avsp=0x1e46
tosp=0x1e46
0xe:pti[0]  nrow=1  offs=0
0x12:pri[0] offs=0x1e5a
block_row_dump:
tab 0, row 0, @0x1e5a
tl: 318 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 02
col  1: [ 2]  c1 02
col  2: [230]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 cd 00 d2 48 90 00
cc 00 00 c8 01 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00
61 00 61 00 61
LOB
Locator:
  Length:        84(230)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.02.35.33.cd
  Flags[ 0x02 0x0c 0x80 0x80 ]:
    Type: CLOB
    Storage: SecureFile
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  SecureFile Header:
    Length:   210
    Old Flag: 0x48 [ DataInRow SecureFile ]
    Flag 0:   0x90 [ INODE Valid ]
    Layers:
      Lengths Array: INODE:204
      INODE:
        00 00 c8 01 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61
00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61 00 61        00 61 00 61
col  3: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 ce 00 12 40 90 00
0c 21 00 1f 40 01 00 01 01 00 02 a3 01
LOB
Locator:
  Length:        84(38)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.02.35.33.ce
  Flags[ 0x02 0x0c 0x80 0x80 ]:
    Type: CLOB
    Storage: SecureFile
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  SecureFile Header:
    Length:   18
    Old Flag: 0x40 [ SecureFile ]
    Flag 0:   0x90 [ INODE Valid ]
    Layers:
      Lengths Array: INODE:12
      INODE:
        21 00 1f 40 01 00 01 01 00 02 a3 01
col  4: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 cf 00 12 40 90 00
0c 21 00 1f 40 01 00 01 01 00 02 bb 01
LOB
Locator:
  Length:        84(38)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.02.35.33.cf
  Flags[ 0x02 0x0c 0x80 0x80 ]:
    Type: CLOB
    Storage: SecureFile
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  SecureFile Header:
    Length:   18
    Old Flag: 0x40 [ SecureFile ]
    Flag 0:   0x90 [ INODE Valid ]
    Layers:
      Lengths Array: INODE:12
      INODE:
        21 00 1f 40 01 00 01 01 00 02 bb 01
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1446 maxblk 1446

-- 对比col  3: [38]与col  4: [38]，块内的长度都是一样的。

col  3: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 ce 00 12 40 90 00
0c 21 00 1f 40 01 00 01 01 00 02 a3 01

col  4: [38]
00 54 00 01 02 0c 80 80 00 02 00 00 00 01 00 00 02 35 33 cf 00 12 40 90 00
                               ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~lobid           
0c 21 00 1f 40 01 00 01 01 00 02 bb 01
                         ~~~~~~~~~~~
如果参考URL http://juliandyke.com/Presentations/LOBInternals.ppt，后面的
01 00 02 bb 表示 DBA of first block in extent
01          表示 # Blocks in extent

SCOTT@test> @dfb 010002a3
    RFILE#     BLOCK#
---------- ----------
         4        675

TEXT
----------------------------------------
alter system dump datafile 4 block 675 ;

SCOTT@test> @dfb  010002bb
    RFILE#     BLOCK#
---------- ----------
         4        699

TEXT
----------------------------------------
alter system dump datafile 4 block 699 ;

--转储对应的块：
alter system dump datafile 4 block 675 ;

Block header dump:  0x010002a3
Object id on Block? Y
seg/obj: 0x468de  csc: 0x02.a6127d14  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.000.00006a25  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x010002a3]
kdlich  [0x2a9751e24c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0002.a6127d14
  lid   000000010000023533ce
  rid   0x00000000.0000
kdlidh  [0x2a9751e264 24]
  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
  flg3  0x00
  pskip 0
  sskip 0
  hash  0000000000000000000000000000000000000000
  hwm   8000
  spr   0
  data  [0x2a9751e280 52 8060]
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62
....
00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 62 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
--- end of lob block dump ---
End dump data blocks tsn: 4 file#: 4 minblk 675 maxblk 675

alter system dump datafile 4 block 699 ;

Block header dump:  0x010002bb
Object id on Block? Y
seg/obj: 0x468e0  csc: 0x02.a6127dc0  itc: 1  flg: E  typ: 5 - LOCAL LOBS
     fsl: 0  fnx: 0xffffffff ver: 0x01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.000.00006a25  0x00000000.0000.00  -B--    0  fsc 0x0000.00000000
========
bdba    [0x010002bb]
kdlich  [0x2a9751e24c 56]
  flg0  0x20 [ver=0 typ=data lock=n]
  flg1  0x00
  scn   0x0002.a6127dc0
  lid   000000010000023533cf
  rid   0x00000000.0000
kdlidh  [0x2a9751e264 24]
  flg2  0x00 [ver=0 lid=short-rowid hash=n cmap=n pfill=n]
  flg3  0x00
  pskip 0
  sskip 0
  hash  0000000000000000000000000000000000000000
  hwm   8000
  spr   0
  data  [0x2a9751e280 52 8060]
00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63
00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63
...
00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 63 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
--- end of lob block dump ---
End dump data blocks tsn: 4 file#: 4 minblk 699 maxblk 699
                                                                   
--从这些可以看出对于使用securefile，无论使用ENABLE/DISABLE STORAGE IN ROW,在块内保存有chunk的信息,不需要通过lob index来定位,能获得很好的性能。
--不知道什么情况下通过lib index来定位????

SCOTT@test> alter table t1 modify lob (col2) (CACHE) modify lob (col3) (cache);
Table altered.

SCOTT@test> select id,col2 from t1 where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size

SCOTT@test> select id,col3 from t1 where id=1;
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size

--可以发现设置cache后，多次执行physical reads=0.

--看来以后设置lob为securefile比较好一些。

时间： 2025-01-28 02:41:47

[20140731]关于LOB字段存储特性4.txt的相关文章

[20140729]关于LOB字段存储特性2.txt

[20140729]关于LOB字段存储特性2.txt --前面提到查询字段是DISABLE STORAGE IN ROW的读会增加,从4->11. 多扫描lob index 来定位信息,但是增加也太多. --有必要看看lob index 的上数据结构. SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 1

[20140729]关于LOB字段存储特性3.txt

[20140729]关于LOB字段存储特性3.txt --前面我们看到只要lob信息在块外,扫描执行读到这些信息都存在物理读. SCOTT@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production crea

[20140729]关于LOB字段存储特性1.txt

[20140729]关于LOB字段存储特性1.txt CLOB/BLOB字段的存储可以使用参数ENABLE/DISABLE STORAGE IN ROW,默认是ENABLE STORAGE IN ROW,这种情况下,如果 保存长度小于等于3964(主要前面占用36字节),实际上总和是4000字节. 参考: http://blog.itpub.net/267265/viewspace-755269/ [20130301]clob字段的empty_clob与NULL.txt 在保存的clob信息有两

Oracle Lob类型存储浅析

  在Oracle中,为数据表字段column和PL/SQL语言,分别提供了多种数据类型,以应对实际开发中的多种类型.Lob类型是Oracle推出一种保存大对象的数据类型.当我们考虑将信息文件(十进制.二进制).图像甚至音频信息采用数据库作为保存载体时,就需要使用lob类型数据.   目前Oracle支持的Lob类型具体包括四个子类型(subtype),分别为CLOB.BLOB.NLOB和BFILE.其中,CLOB.BLOB和NLOB都是将数据保存在数据库内部,而BFILE类型保存的核心是文件指

如何利用HTML5的离线功能和本地持久存储特性

在本文中,您将了解如何利用http://www.aliyun.com/zixun/aggregation/79228.html"> HTML5 的离线功能和本地持久存储特性.示例应用程序说明了如何避免常见的问题. HTML 版本 5 (HTML5) 并不期望在 2012 年之前获得万维网联盟 (W3C) 推荐.尽管 HTML5 不是官方标准,但 Web 浏览器供应商正在增加和营销 HTML5 特性.HTML5 正在为 Internet 网站和业务线 (LOB) 应用程序扩展 Web 体验.

在Oracle 8i的SQL*Plus中如何利用LOB字段存取操作系统二进制文件

oracle|二进制    在Oracle 8i的SQL*Plus中如何利用LOB字段存取操作系统二进制文件                                                        广东省岭澳核电有限公司工程控制处管理信息科(518124)  黄福同           Oracle 8i数据库系统功能比前面版本更加完善,尤其是出现了BLOB,CLOB,NCLOB,BFILE这些LOB(大型对象)类型来取代功能有限的LONG.LONGRAW类型.BLOB字段

J2EE程序中使用oracle数据库LOB字段的总结(elathen)

j2ee|oracle|程序|数据|数据库 最近在J2EE的项目中需要使用LOB字段保存文本信息以及图片和文件,到网上搜拉一下,还不少,仔细看拉一下,但都不是很全有的还有错误,经过几天的实践,把问题都解决拉,顺便总结一下,希望对需要的朋友有点参考 LOB中我们用的比较多的主要有两种CLOB和BLOB,我们对两种类型分别讨论 1.CLOB是字符型LOB,主要存储文本信息,,最长为4G.,在J2EE程序中,比如网页的textarea中的字符信息比较长,Varchar2字段类型不能满足时,我们就得用C

ORACLE中LOB字段的使用和维护

oracle 摘要:本文通过实例介绍了在ORACLE数据库中通过DBMS_LOB包使用和维护LOB数据类型的基本方法. 关键词:ORACLE DBMS_LOB LOB 维护 中图分类号:TP31 1.引言 随着社会的发展,在现代信息系统的开发中,需要存储的已不仅仅是简单的文字信息,同时还包括一些图片和音像资料或者是超长的文本.比如开发一套旅游信息系统,每一个景点都有丰富的图片.音像资料和大量的文字介绍.这就要求后台数据库要有存储这些数据的能力.ORACLE公司在其Oracle8i中通过提供LOB

开启SQL Server Denali之旅：字段存储索引

SQL Server的最近三个版本都贴上了非官方的标签,每一个版本都是为那些大 多数新特性所针对的用户群量身定做的.例如,SQL Server 2005被认为是一个面 向开发者版本,而SQL Server 2008则被看作是一个面向数据管理员的版本.具备 Microsoft PowerPivot数据分析工具并对SQL Server Analysis Services和 Reporting Services功能都进行了改进的SQL Server 2008 R2则是一个众所周知 的商业智能(BI)版