lob internals

1 © 2008 Julian Dyke

LOB Internals

Julian Dyke

Independent Consultant

juliandyke.com

Web Version - December 2008

2 © 2008 Julian Dyke juliandyke.com

Objectives

1. Understand how LOBs use storage in the Oracle database

2. Consider options to optimize LOB performance in terms of: Reads Writes Impact on physical I/O Impact on buffer cache


Agenda

Basic Files Secure Files


Basic Files


Basic FilesOverview Introduced in Oracle 8.0

Known as Basic Files in Oracle 11.1 and above

Intended to replace LONG columns

Can be used to store large blocks of unstructured data e.g.: text graphics video clips sound waveforms XML documents

Can store either character or binary data

Can be stored within database (internal) outside database (external)


Basic FilesLOB Types There are four types of LOB:

BLOB - Binary Large Object Stored within database Contains raw data

CLOB - Character Large Object Stored within database Supports database character set

NCLOB - NLS Character Large Object Stored within database Supports NLS character set

BFILE - Binary File Large Object Stored in a binary file outside the database Pointed to by a file locator within the database


Basic FilesInternal LOBs Stored outside of database

BLOB, CLOB or NCLOB

Maximum size is dependent on version and chunk size

Support features such as: concurrency redo logging and recovery transactions with commits and rollbacks

For each row with an internal LOB column, the LOB can: Be NULL Point to an empty BLOB, CLOB or NCLOB Point to a BLOB, CLOB or NCLOB value


Basic FilesLOB columns versus LONG columns LOBs are intended to replace LONG columns:

Oracle recommends converting LONG columns to LOB columns LONG columns still used by many applications LONG columns still used in Oracle 11.1 data dictionary tables including:

COL$, TABPART$, INDPART$, TRIGGER$, VIEW$ and CDEF$ Therefore it is unlikely LONG columns will be desupported in the near

future

LONGs Single column per table Maximum size 2GB Data always stored in-line Cannot be an object attribute Cannot be partitioned Cannot be used in IOTs Cannot be replicated Access is sequential Partial PL/SQL support

LOBs Multiple columns per table Maximum size 4GB or more Data stored in-line or out-of-line Can be an object attribute Can be partitioned Can be used in IOTs Can be replicated Access can be is random Full PL/SQL support


Basic FilesDBMS_LOB Package BLOB and CLOB Subroutines

GETOPTIONS (11.1+) GET_STORAGE_LIMIT INSTR ISSECUREFILE ISTEMPORARY ISOPEN LOADFROMFILE LOADBLOBFROMFILE LOADCLOBFROMFILE OPEN READ SETOPTIONS (11.1+) SUBSTR TRIM WRITE WRITEAPPEND

APPEND CLOSE COMPARE CONVERTTOBLOB CONVERTTOCLOB COPY CREATETEMPORARY ERASE FRAGMENT_DELETE (11.1+) FRAGMENT_INSERT (11.1+) FRAGMENT_MOVE (11.1+) FRAGMENT_REPLACE (11.1+) FREETEMPORARY GETCHUNKSIZE GET_DEDUPLICATE_REGIONS (11.1+) GETLENGTH

10

© 2008 Julian Dyke juliandyke.com

Basic FilesDBMS_LOB Package BFILE Subroutines

CLOSE COMPARE FILECLOSE FILECLOSEALL FILEEXISTS FILEGETNAME FILEISOPEN FILEOPEN GETLENGTH INSTR ISOPEN OPEN READ SUBSTR


Basic FilesDBA_LOBS View

Column Name Data Type Comments

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

SEGMENT_NAME VARCHAR2(30)

TABLESPACE_NAME VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

CHUNK NUMBER

PCTVERSION NUMBER

RETENTION NUMBER

FREEPOOLS NUMBER

CACHE VARCHAR2(10)

LOGGING VARCHAR2(7)

ENCRYPT VARCHAR2(4) Oracle 11.1 and above

COMPRESSION VARCHAR2(6) Oracle 11.1 and above

DEDUPLICATION VARCHAR2(15) Oracle 11.1 and above

IN_ROW VARCHAR2(3)

FORMAT VARCHAR2(15)

PARTITIONED VARCHAR2(3)

SECUREFILE VARCHAR2(3) Oracle 11.1 and above

12


Basic FilesLOB Objects If a table includes an internal LOB column, the following objects will be

created LOB index LOB data

Each object has a separate segment

For example:

CREATE TABLE t1(

c1 NUMBER,c2 CLOB,

);

LOBSYS_IL0000055604C00002$$LOB INDEX

LOBSYS_LOB0000055604C00002$$LOB DATA

LOB T1TABLE

13


Basic FilesLOB Segments Every LOB has a LOB data segment and a LOB index segment

LOB data segment is reported by DBA_OBJECTS Segment name is system generated e.g. SYS_LOB0000070479C00002$$ where:

0000070479 is the OBJECT_ID of the table 00002 is the column number of the LOB column within the table

LOB index is not reported by DBA_OBJECTS Index is still defined in SYS.OBJ$ and SYS.IND$ Index name is reported by DBA_LOBS e.g. SYS_IL0000070479C00002$$ where:

0000070479 is the OBJECT_ID of the table 00002 is the column number of the LOB column within the table

14


Basic FilesLOB Objects One LOB index segment and one LOB data segment will be created for each

LOB column

For example: CREATE TABLE t2(

c1 NUMBER,c2 CLOB,c3 CLOB

);





LOB T2TABLE

15


Basic FilesPartitioned LOBs Oracle supports partitioned and sub-partitioned LOBS in Oracle 8.1.5 and

above LOBs are supported in partitioned IOTs in Oracle 9.0.1 and above

LOB and and LOB index segments are equi-partitioned with base table

Partitioning key cannot contain a LOB column

CREATE TABLE t3(

c1 NUMBER,c2 CLOB

)PARTITION BY RANGE (c1)(

PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200));

16


Basic FilesPartitioned LOBs

SYS_LOB_P22LOB PARTITION


LOBSYS_LOB0000055612C00002$$LOB

SYS_LOB_P24INDEX PARTITION



P2TABLE PARTITION

P1TABLE PARTITION

LOB T3TABLE

17


Basic FilesSub Partitioned LOBs

CREATE TABLE t4(

c1 NUMBER,c2 NUMBER,c3 CLOB

)PARTITION BY RANGE (c1)(

PARTITION p1 VALUES LESS THAN (100) (

SUBPARTITION p1s1 VALUES LESS THAN (10),SUBPARTITION p1s2 VALUES LESS THAN (20)

),PARTITION p2 VALUES LESS THAN (200)(

SUBPARTITION p2s1 VALUES LESS THAN (10),SUBPARTITION p2s2 VALUES LESS THAN (20)

));

18


Basic FilesSub Partitioned LOBs

SYS_IL_P23 SYS_IL_P24

SYS_LOB_SUBP32LOB SUBPARTITION






LOBSYS_LOB0000055633C00003$$LOB

SYS_LOB_SUBP32INDEX SUBPARTITION

SYS_LOB_SUBP33 INDEX SUBPARTITION






P1S1TABLE SUBPARTITION


P2TABLE PARTITION

P1TABLE PARTITION

LOB T4TABLE



19


Basic FilesIn Row versus Out-of-Line Storage Values can be stored

in-line - in a row piece out-of-line - in a separate LOB segment

ENABLE STORAGE IN ROW (default) LOB value is stored within row if size is < 3964 bytes LOB value is stored out-of-line if size > 3964 bytes If in-line LOB grows to more than 3964 bytes

LOB value is moved out-of-line to LOB segment If out-of-line LOB shrinks to less than 3964 bytes

LOB value remains out-of-line in LOB segment DISABLE STORAGE IN ROW

LOB value is always stored out-of-line in LOB segment

CREATE TABLE t1 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (ENABLE STORAGE IN ROW);

CREATE TABLE t2 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (DISABLE STORAGE IN ROW);

20


Basic FilesLOB Initialization LOBs are initialized using built-in functions

EMPTY_BLOB () EMPTY_CLOB ()

Both functions create LOB locators Minimum size is 36 bytes Maximum size is 86 bytes

CREATE TABLE t1(

c1 NUMBER,c2 CLOB,

);

INSERT INTO t1 VALUES (c1,NULL);

INSERT INTO t1 VALUES (c1,EMPTY_CLOB());

C2 occupies 0 bytes

C2 occupies 37bytes

21


Basic FilesLOB Initialization LOB locator is initialized using EMPTY_CLOB() or EMPTY_BLOB() For example:

00 54 00

01 02

0c 00

00 00

01 00

00 00

01 00

00 00 00

89 3d

00 10 09

00 00

00 00

00 00

00 00

00 00

00 00

00

tab 0, row 151, @0x579 tl: 44 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 3] c2 08 53col 1: [36] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 89 3d 00 10 09 00 00 00 00 00 00 00 00 00 00 00 00 00

INSERT INTO t1 VALUES (c1,EMPTY_CLOB());

Block dump includes :

LOBIDHeader

Body Length LOB Length VersionFlags

22


Basic FilesLOB ID LOB ID is a 10 byte number identifying individual instance of a LOB

Allocated when LOB value is created including EMPTY_CLOB() etc Format is <X><Y> where

<X> is a currently unknown 4-byte number (always 1) <Y> is a 6-byte number generated from sequence SYS.IDGEN$

For example:

INSERT INTO t1 VALUES (0,EMPTY_CLOB)

SELECT sequence_owner, sequence_name, nextvalue, increment_by, cache_sizeFROM v$_sequences;

Sequence Owner Sequence Name Next Value Increment By Cache SizeSYS IDGEN1$ 37451 50 20

SELECT sequence_owner, sequence_name, nextvalue, increment_by, cache_sizeFROM v$_sequences;

Sequence Owner Sequence Name Next Value Increment By Cache SizeSYS IDGEN1$ 37401 50 20

00 00

00 01

00 00 00

00 92

19 LOB ID = (0x9219 = 37401)

23


Basic FilesLOB Inline Storage Example 1 - 12 byte LOB - ENABLE STORAGE IN ROW

00

54 00 01

02 0c

00 00

00 01

00 00

00 01

00 00

00 00

8a 7b

00

1c 09 00

00 00

00 00

00 0c

00 00

00 00

00 01

58 58

58 58

58

58 58 58

58 58

58 58

ab 0, row 0, @0x1f62tl: 54 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [48] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8a 7b 00 1c 09 00 00 00 00 00 00 0c 00 00 00 00 00 01 58 58 58 58 58 58 58 58 58 58 58 58

INSERT INTO t1 VALUES (c1, LPAD ('X',12,'X'));

0x0C = 120x1C = 28

24



00 54 00

01 02

0c 00

00 00

01 00

00 00

01 00

00 00 00

8a ad

01 a0 09

00 00

00 00

00 01

90 00

00 00

00 00

01 58 58

58 58

58 58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58 58

58 58

58 58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58 58

58 58

58 58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58 58

58 58

tab 0, row 0, @0x1ddctl: 444 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [436] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8a ad 01 a0 09 00 00 00 00 00 01 90 00 00 00 00 00 01 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58. . . . . . .

INSERT INTO t1 VALUES (c1,LPAD ('X',400,'X'));

0x0190 = 4000x01A0 = 416

25



00

54 00 01

02 0c

00 00

00 01

00 00

00 01

00 00

00 00

8c a1

0f 8c 09 00

00 00

00 00

0f 7c

00 00

00 00

00 01

58 58

58 58

58

58 58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58

58

58 58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58

58

58 58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58

58 58

tab 0, row 0, @0xff0tl: 4008 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [4000] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8c a1 0f 8c 09 00 00 00 00 00 0f 7c 00 00 00 00 00 01 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 . . . . . . .


0x0F7C = 39640x0F8C = 3980

26


Basic FilesLOB Out-of-Line Storage Example 4 - 3965 byte LOB - ENABLE STORAGE IN ROW

00 54 00 01 02

0c 00

00 00

01 00 00 00

01 00

00 00 00

8d 05

00 14 05 00 00

00 00

00 0f 7d 00 00 00

00 00

02 01 00

01 b5

tab 0, row 0, @0x1f6atl: 46 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [40] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8d 05 00 14 05 00 00 00 00 00 0f 7d 00 00 00 00 00 02 01 00 01 b5


0x0014 = 20 0x0F7D bytes Address of first chunk

0x0F7D = 3965

27


Basic FilesLOB Out-of-Line Storage Out-of-line LOB data is stored in a PAGETABLE MANAGED LOB BLOCK Each block has

a 56 byte header a 4 byte trailer

For example for a 8192 byte block

Trailer4 bytes

Header56 bytes

LOB Data8132 bytes

28


CREATE DIRECTORY dir1 AS '/tmp';

CREATE TABLE t11 (

c1 NUMBER,c2 CLOB

);

INSERT INTO t11 VALUES (0,EMPTY_CLOB());

Basic FilesOut-of-Line Storage

DECLAREl_bfile BFILE;l_clob CLOB;l_result NUMBER;

BEGINl_bfile := BFILENAME ('DIR1','dbms_stats.lst');

DBMS_LOB.OPEN (l_bfile);

SELECT c2 INTO l_clob FROM t11 WHERE c1 = 0 FOR UPDATE;

l_result := DBMS_LOB.GETLENGTH (l_bfile);

DBMS_LOB.LOADFROMFILE (l_clob,l_bfile,l_result);

DBMS_LOB.CLOSE (l_bfile);END;

29



00 54 00 01 02

0c 00

00 00

01 00 00 00

01 00

00 00 00

8d ff

00 34 05 00 00

00 00

08 10

55 00 00 00

00 00

02 01 00

02 5f

01 00 02 60 01

00 02

5c 01

00 02 5d 01

00 02

5e 01 00

02 6e

01 00 02 6f 01

00 02

70 01

00 02 6a

tl: 78 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [72] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 8d ff 00 34 05 00 00 00 00 08 10 55 00 00 00 00 00 02 01 00 02 5f 01 00 02 60 01 00 02 5c 01 00 02 5d 01 00 02 5e 01 00 02 6e 01 00 02 6f 01 00 02 70 01 00 02 6a

INSERT INTO t1 VALUES (c1,BFILE...);

8 x 8132 = 65056 0x1055 = 4181 65056 + 4181+ 69237

8 chunks0x34 = 52

DBA of LOB chunks0x1055 bytes

30



00 54 00

01 02 0c

00 00 00

01 00 00 00

01 00

00 00 00

90 25

00 40 05

00 00 00

00 0c 00

00 00 00 00

00 00

02 01 00

02 3d

01 00 02

3e 01 00

02 3f 01

00 02 40 01

00 02

3c 01 00

02 37

01 00 02

38 01 00

02 32 01

00 02 33 01

00 02

34 01 00

02 35

01 00 02

36

tab 0, row 0, @0x1f14tl: 90 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [84] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 90 25 00 40 05 00 00 00 00 0c 00 00 00 00 00 00 00 02 01 00 02 3d 01 00 02 3e 01 00 02 3f 01 00 02 40 01 00 02 3c 01 00 02 37 01 00 02 38 01 00 02 32 01 00 02 33 01 00 02 34 01 00 02 35 01 00 02 36

LOB Index exists but is empty12 chunks 0 bytes0x40 = 64


12 x 8132 = 97584

31


Basic FilesLOB Out-of-Line Storage Example 7- 97585 byte LOB - ENABLE STORAGE IN ROW

00 54 00 01 02

0c 00

00 00

01 00

00 00 01

00 00 00

00 90 89

00 40 05 00 00

00 00

0c 00

01 00

00 00 00

00 02 01

00 03 2d

01 00 03 2e 01

00 03

2f 01

00 03

30 01 00

03 2c 01

00 03 27

01 00 03 28 01

00 03

22 01

00 03

23 01 00

03 24 01

00 03 25

01 00 03 26

tab 0, row 0, @0x1f14tl: 90 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [84] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 90 89 00 40 05 00 00 00 00 0c 00 01 00 00 00 00 00 02 01 00 03 2d 01 00 03 2e 01 00 03 2f 01 00 03 30 01 00 03 2c 01 00 03 27 01 00 03 28 01 00 03 22 01 00 03 23 01 00 03 24 01 00 03 25 01 00 03 26


12 chunks 1 byte0x40 = 64LOB Index is used for 13th chunk

32


Basic FilesLOB Out-of-Line Storage Example 7 continued - 97585 byte LOB - ENABLE STORAGE IN ROW LOB Index

01 00 03 3f 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

row#0[7982] flag: ------, lock: 2, len=50, data:(32): 01 00 03 3f 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 00col 0; len 10; (10): 00 00 00 01 00 00 00 00 90 89col 1; len 4; (4): 00 00 00 0c

00 00 00 01

00 00

00 00 90

89 00

00 00 0c

Key

Data

First chunk number

LOB Index is always unique Data is always 32 bytes

LOBID

33



00 54 00

01 02 0c

00 00 00

01 00 00 00

01 00

00 00 00

90 ed

00 40 05

00 00 00

00 17 00

e4 00 00 00

00 00

02 01 00

03 35

01 00 03

36 01 00

03 37 01

00 03 38 01

00 03

34 01 00

03 5f

01 00 03

60 01 00

03 5a 01

00 03 5b 01

00 03

5c 01 00

03 5d

01 00 03

5e

tab 0, row 0, @0x1f14tl: 90 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [84] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 90 ed 00 40 05 00 00 00 00 17 00 e4 00 00 00 00 00 02 01 00 03 35 01 00 03 36 01 00 03 37 01 00 03 38 01 00 03 34 01 00 03 5f 01 00 03 60 01 00 03 5a 01 00 03 5b 01 00 03 5c 01 00 03 5d 01 00 03 5e


0x17 = 23 chunks

12 chunks in row11 chunks in index0xe4 = 228

bytes

34


Basic FilesLOB Out-of-Line Storage Example 8 continued - 187264 byte LOB - ENABLE STORAGE IN ROW Lob Index

row#0[7982] flag: ------, lock: 2, len=50, data:(32): 01 00 03 6f 01 00 03 70 01 00 03 6a 01 00 03 6b 01 00 03 6c 01 00 03 6d 01 00 03 6e 01 00 03 57col 0; len 10; (10): 00 00 00 01 00 00 00 00 90 edcol 1; len 4; (4): 00 00 00 0crow#1[7932] flag: ------, lock: 2, len=50, data:(32): 01 00 03 58 01 00 03 52 01 00 03 53 01 00 03 54 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00col 0; len 10; (10): 00 00 00 01 00 00 00 00 90 edcol 1; len 4; (4): 00 00 00 14

01 00 03 6f 01

00 03

70 01 00 03 6a 01

00 03

6b

01 00 03 6c 01

00 03

6d 01 00 03 6e 01

00 03

57

01 00 03 58 01

00 03

52 01 00 03 53 01

00 03

54

00 00 00 00 00

00 00

00 00 00 00 00 00

00 00

00

00 00 00 01

00 00

00 00 90

ed 00

00 00 0c

00 00 00 01

00 00

00 00 90

ed 00

00 00 14

Key

Key

Data

Data

35


Basic FilesChunk Size Each internal LOB has a chunk size Chunks can be manipulated individually Default chunk size is block size Maximum chunk size is 32768 for all tablespace block sizes Chunk size is:

specified in bytes rounded up to nearest block size

CREATE TABLE t3 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CHUNK 32768);

Chunk size is reported in DBA_LOB.CHUNK Chunk size is stored in SYS.LOB$.CHUNK

Although chunk size is specified and reported in bytes, it is stored in LOB$ in terms of tablespace blocks

36


Basic FilesChunk Size Example 12 - 69237 byte LOB CHUNK = 8192 (default)

00 54 00 01 02

0c 00

00 00

01 00 00 00

01 00

00 00

00 8d ff

00 34 05 00 00

00 00

08 10

55 00 00 00

00 00

02 01

00 02 5f

01 00 02 60 01

00 02

5c 01

00 02 5d 01

00 02

5e 01

00 02 6e

01 00 02 6f 01

00 02

70 01

00 02 6a

00 54 00 01 02

0c 00

00 00 01 00

00 00 01

00 00

00 00

94 d5

00 1c 05 00 00

00 00

08 10 55 00

00 00 00

00 02

01 00

04 45

01 00 04 55 01

00 04

5d

tab 0, row 0, @0x1f38tl: 54 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [48] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 94 d5 00 1c 05 00 00 00 00 08 10 55 00 00 00 00 00 02 01 00 04 45 01 00 04 55 01 00 04 5d

CHUNK = 32768 (maximum)

4 x 8132 = 32528 2 x 32528 = 65056 0x1055 = 4181 65056 + 4181+ 69237

37


Basic FilesChunk Size In Oracle 11.1 maximum LOB size is determined by chunk size:

CREATE TABLE t1 (c1 NUMBER, c2 CLOB,c3 CLOB,c4 CLOB

) LOB (c2) STORE AS (CHUNK 8192),LOB (c3) STORE AS (CHUNK 16384),LOB (c4) STORE AS (CHUNK 32768);

SELECT dbms_lob.getchunksize (c2), dbms_lob.get_storage_limit (c2),dbms_lob.getchunksize (c3), dbms_lob.get_storage_limit (c3),dbms_lob.getchunksize (c4), dbms_lob.get_storage_limit (c4)

FROM t1;

Chunk Size Storage Limit # Chunks

8132 34,926,674,042,940 4,294,967,295

16264 69,853,348,085,880 4,294,967,295

32528 139,706,696,171,760

4,294,967,295

38


Basic FilesRead Consistency PCTVERSION clause

Specifies the amount of space that will be used for the LOB consistent-read mechanism

Affects reclamation of old copies of LOB data Affects the ability to perform consistent read

Range is 0 to 100 Default value is 10

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (PCTVERSION 20);

Reported in DBA_LOB.PCTVERSION Can be set to 0 for read-only LOBs PCTVERSION should be set to a low value if:

updates and reads are not concurrent the LOB is written once and then read-only

PCTVERSION should be set to a high value if: there are large numbers of queries there is heavy read and write activity

39


Basic Files Read Consistency Example 9 - 32768 byte LOB - update 4000 bytes starting at offset 10000

PCTVERSION = 10 (default)

DECLARE l_clob CLOB;l_str VARCHAR2(4000);

BEGINl_str := LPAD ('Y',4000,'Y');

SELECT c2 INTO l_clobFROM t19WHERE c1 = 0;FOR UPDATE;

DBMS_LOB.WRITE (l_clob,4000,10000,l_str);END;/

40


Basic Files Read Consistency Example 9 (continued) Before Update

tl: 62 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [56] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 93 45 00 24 05 00 00 00 00 04 00 f0 00 00 00 00 00 03 01 00 03 fd 01 00 04 0f 01 00 03 ff 01 00 04 00 01 00 03 fc

00 54 00 01 02

0c 00

00 00 01 00 00 00

01 00

00 00 00 93 45

00 24 05 00 00

00 00

04 00 f0 00 00 00

00 00

02 01 00 03 fd

01 00 04 0f 01

00 03

ff 01 00 04 00 01

00 03

fc

00 54 00 01 02

0c 00

00 00 01 00 00 00

01 00

00 00 00 93 45

00 24 05 00 00

00 00

04 00 f0 00 00 00

00 00

02 01 00 03 fd

01 00 03 fe 01

00 03

ff 01 00 04 00 01

00 03

fc After Update

Updated Chunk

41


Basic Files Read Consistency Example 9 (continued)

row#0[7982] flag: ------, lock: 2, len=50, data:(32): 01 00 03 fe 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 00col 0; len 10; (10): 00 01 49 14 f1 b7 00 00 00 00col 1; len 4; (4): 01 00 03 fe

01 00 03 fe 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

LOB Index

LOB Data

00 01 49

14 fe c0 00

00 00 00 01 00 03

0fKey

Data

Free List

3FD 3FE 3FF 400 3FC 3FE40F

STOP

42


Basic Files Read Consistency Example 10 - 32768 byte LOB - update 4000 bytes starting at offset 30000

PCTVERSION = 10

DECLARE l_clob CLOB;l_str VARCHAR2(4000);

BEGINl_str := LPAD ('Y',4000,'Y');

SELECT c2 INTO l_clobFROM t19WHERE c1 = 0;FOR UPDATE;

DBMS_LOB.WRITE (l_clob,4000,30000,l_str);END;/

43


Basic Files Read Consistency Example 10 (continued) Before Update

tl: 62 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [56] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 93 45 00 24 05 00 00 00 00 04 05 bf 00 00 00 00 00 04 01 00 03 fd 01 00 04 0f 01 00 03 ff 01 00 03 fe 01 00 04 10

00 54 00 01 02

0c 00

00 00 01 00 00 00

01 00

00 00 00 93 45

00 24 05 00 00

00 00

04 05 bf 00 00 00

00 00

04 01 00 03 fd

01 00 04 0f 01

00 03

ff 01 00 03 fe 01

00 04

10

00 54 00 01 02

0c 00

00 00 01 00 00 00

01 00

00 00 00 93 45

00 24 05 00 00

00 00

04 00 f0 00 00 00

00 00

02 01 00 03 fd

01 00 04 0f 01

00 03

ff 01 00 04 00 01

00 03

fc After Update

Updated Chunk Updated Chunk

44


Basic Files Read Consistency Example 10 (continued) - Lob Index

row#0[7982] flag: ------, lock: 2, len=50, data:(32): 01 00 03 fc 01 00 04 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00col 0; len 10; (10): 00 01 49 14 fe c0 00 00 00 00col 1; len 4; (4): 01 00 03 fc

01 00 03 fc 01

00 04

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 01 49 14

fe c0

00 00 00

00 01

00 03 0fKey

Data

LOB Index

LOB Data

Free List

3FD 3FE 3FF 400 3FC 3FE40F 4003FE 3FC410

STOP

45


Basic FilesPartial Deletion Example 11 - 69237 byte LOB - Erase 18000 bytes from offset 12000

DECLAREl_clob CLOB;l_amount NUMBER := 18000;

BEGINSELECT c2 INTO l_clobFROM t22WHERE c1 = 0FOR UPDATE;

DBMS_LOB.ERASE (l_clob,l_amount,12000);END;

46


Basic FilesPartial Deletion Example 11 continued - 69237 byte LOB - Erase 18000 bytes from offset 12000 Before ERASE

00 54 00 01 02

0c 00

00 00

01 00 00 00

01 00

00 00

00 95 6b

00 34 05 00 00

00 00

08 10

55 00 00 00

00 00

03 01

00 04 6d

01 00 04 7d 00

00 00

00 01

00 04 7e 01

00 04

6c 01

00 04 7f

01 00 04 80 01

00 04

7a 01

00 04 7b

tab 0, row 0, @0x1f20tl: 78 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [72] 00 54 00 01 02 0c 00 00 00 01 00 00 00 01 00 00 00 00 95 6b 00 34 05 00 00 00 00 08 10 55 00 00 00 00 00 03 01 00 04 6d 01 00 04 7d 00 00 00 00 01 00 04 7e 01 00 04 6c 01 00 04 7f 01 00 04 80 01 00 04 7a 01 00 04 7b

00 54 00 01 02

0c 00

00 00

01 00 00 00

01 00

00 00

00 95 6b

00 34 05 00 00

00 00

08 10

55 00 00 00

00 00

02 01

00 04 6d

01 00 04 6e 01

00 04

6f 01

00 04 70 01

00 04

6c 01

00 04 7f

01 00 04 80 01

00 04

7a 01

00 04 7b

After ERASE

47


Basic FilesPartial Deletion Example 11 continued - 69237 byte LOB - Erase 18000 bytes from offset 12000 After ERASE - Lob Index

row#0[7982] flag: ------, lock: 2, len=50, data:(32): 01 00 04 6e 01 00 04 6f 01 00 04 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00col 0; len 10; (10): 00 01 49 15 3a 62 00 00 00 0col 1; len 4; (4): 01 00 04 6e

01 00 04 6e 01

00 04

6f 01 00 04

70 00 00

00 00

00 00 00 00 00

00 00

00 00 00 00

00 00 00

00 00LOB Index

LOB Data

Free List

Data

46D 46E 470 46C 47F 480 47A 46E 46F 47047B46F47D 47E

STOP

48


Basic FilesCACHE Clause Specifies whether a LOB will be stored in the database buffer cache when

values are read and written. Allows LOB values to be shared amongst users.

Possible values are CACHE, CACHE READS and NOCACHE Default value is NOCACHE

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (NOCACHE);

Reported in DBA_LOB.CACHE Can be NO, CACHEREADS or YES

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CACHE);

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CACHE READS);

49


Basic FilesCACHE versus NOCACHE CACHE LOGGING

Top 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) Time Wait Class------------------------------ -------- ----------- ------ ------ ----------enq: CF - contention 6,211 877 141 22.1 OtherCPU time 300 7.6log file sync 69,297 171 2 4.3 Commitcontrol file parallel write 31,126 154 5 3.9 System I/Olog file parallel write 60,083 94 2 2.4 System I/O

Top 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) Time Wait Class------------------------------ -------- ----------- ------ ------ ----------CPU time 558 72.6log file sync 79,335 210 3 27.2 Commitlog file parallel write 66,613 102 2 13.4 System I/OSQL*Net more data from client 37,857 19 1 2.6 Networkdb file parallel write 5,281 11 2 1.4 System I/O

NOCACHE NOLOGGING

50


Basic FilesLOGGING Possible values are LOGGING (default) and NOLOGGING If CACHE option is specified then LOGGING is mandatory LOGGING/NOLOGGING cannot be specified without CACHE clause

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CACHE LOGGING);

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CACHE READS LOGGING);

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (NOCACHE NOLOGGING);

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (NOCACHE LOGGING);

CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CACHE READS NOLOGGING);

Reported in DBA_LOB.LOGGING Can be NO or YES

51


Basic FilesLOB Compression LOBs can be compressed using the LZ_COMPRESS procedure in the

UTL_COMPRESS package

A quality can be specified 1 - Fastest 9 - Slowest

The default quality is 6

Compression achieved is not very consistent

File size in bytes

Quality dbms_stats.lst

alert.log

Uncompressed

69237 1889203

1 14408 868037

2 14121 694521

3 14067 500580

4 14718 1185502

5 14574 1175025

6 14383 1167914

7 14347 1167769

8 14303 1167240

9 14303 1167238

52


Basic FilesLOB Compression Example DECLARE

l_bfile BFILE;l_blob BLOB;l_clob CLOB;l_length NUMBER;l_quality NUMBER := 1;

l_dest_offset NUMBER := 1; l_source_offset NUMBER := 1; l_blob_csid NUMBER := DBMS_LOB.DEFAULT_CSID; l_lang_context NUMBER := DBMS_LOB.DEFAULT_LANG_CTX; l_warning NUMBER;BEGIN l_bfile := BFILENAME ('DIR1','alert.log');

DBMS_LOB.OPEN (l_bfile);

DBMS_LOB.CREATETEMPORARY (l_blob,FALSE);

l_length := DBMS_LOB.GETLENGTH (l_bfile);

UTL_COMPRESS.LZ_COMPRESS (l_bfile,l_blob,l_quality);

SELECT c2 INTO l_clob FROM t34 WHERE c1 = 0 FOR UPDATE;

DBMS_LOB.CONVERTTOCLOB (l_clob,l_blob,DBMS_LOB.LOBMAXSIZE, l_dest_offset,l_source_offset,l_blob_csid,l_lang_context,l_warning);END;

CREATE DIRECTORY dir1 AS '/tmp';

CREATE TABLE t34 (c1 NUMBER, c2 CLOB);INSERT INTO t34 VALUES (0,EMPTY_CLOB ());

53


Secure Files

54


Secure FilesOverview In Oracle 11.1 and above there are two types of LOB

STORE AS BASICFILE (default) STORE AS SECUREFILE

STORE AS BASICFILE Default Behaves as Oracle 10.2 and below

STORE AS SECUREFILE Oracle 11.1 and above Supports several new features including

LOB compression LOB encryption LOB deduplication Metadata only logging Configurable retention time

55


SecureFilesStorage Can only be created in an ASSM tablespace

New block types supporting SecureFiles include:

Description Block Type

NGLOB: Extent Map 0x3c

NGLOB: Hash Bucket 0x3d

NGLOB: Committed Free Space 0x3e

NGLOB: Segment Header 0x3f

NGLOB: Lob Extent Header 0x40

NGLOB: Persistent Undo 0x41

LOB Data Segments are still stored in blocks of type trans_data (block type 6)

Note CHUNK is advisory - only maintained for backward compatibility PCTVERSION not supported - use RETENTION instead

56


Secure FileInitialization EMPTY_CLOB () Basic File

00 54 00 01 02

0c 00

80 00

01 00 00 00

01 00

00 00 00

99 53

00 0a 48 90 00

04 00

00 00

00

tl: 36 fb: --H-FL-- lb: 0x1 cc: 2col 0: [ 1] 80col 1: [30] 00 54 00 01 02 0c 00 80 00 01 00 00 00 01 00 00 00 00 99 53 00 0a 48 90 00 04 00 00 00 00

00 54 00

01 02

0c 00

00 00

01 00

00 00

01 00

00 00 00

89 3d

00 10 09

00 00

00 00

00 00

00 00

00 00

00 00

00

Secure File

57


Secure FileLOB Locator Example DBMS_STATS.LST - 69237 bytes Basic File

00 54 00

01 02 0c 00

80 00

01 00 00 00

01 00

00 00 00

98 8b

00 1f 40

90 00 19 22

00 01

0e 75 01 02

01 01 00

05 6d 04

01 01 00

05 80 01

01 01 00

05 82 04

tl: 57 fb: --H-FL-- lb: 0x2 cc: 2col 0: [ 1] 80col 1: [51] 00 54 00 01 02 0c 00 80 00 01 00 00 00 01 00 00 00 00 98 8b 00 1f 40 90 00 19 22 00 01 0e 75 01 02 01 01 00 05 6d 04 01 01 00 05 80 01 01 01 00 05 82 04

00 54 00

01 02 0c

00 00

00 01 00

00 00 01

00 00

00 00

8d ff

00 34 05

00 00 00

00 08

10 55 00

00 00 00

00 02

01 00

02 5f

01 00 02

60 01 00

02 5c

01 00 02

5d 01 00

02 5e

01 00

02 6e

01 00 02

6f 01 00

02 70

01 00 02

6a

Secure File

DBA of first block in extent

# Blocks in extent

0x10E75 = 69237

58


Secure FilesLob Blocks Out-of-line LOB data is stored in a trans data block (block type 6) Each block has

a 128 byte header a 4 byte trailer

For example for a 8192 byte block

Trailer4 bytes

Header128 bytes

LOB Data8060 bytes

59


Secure FilesLOB Compression Oracle 11.1 and above

Enables server-side LOB compression Random read/write access is still possible Independent of table compression / index compression Only valid for SecureFile LOBs

Valid values are COMPRESS <defaults to MEDIUM> COMPRESS MEDIUM COMPRESS HIGH NOCOMPRESS <default>

COMPRESS MEDIUM Faster (less resource)

COMPRESS HIGH Better compression (less storage)

Individual LOBs can be compressed using DBMS_LOB.SETOPTIONS ()

60


Secure FilesLOB Compression Compressed size of LOB is not reported in Oracle 11.1

To calculate compressed size count number of extents In block dump Using DBMS_LOBUTIL.GETINODE

Size will be an (over) estimate

Example - alert.log - sizes in bytes

Uncompressed Size 1899203

COMPRESS MEDIUM 72540

COMPRESS HIGH 40300

SecureFile LOB Compression much better than UTLCOMPRESS package For example best compression achieved for same file using

LZ_COMPRESS was 500580 bytes

61


Secure FilesDBMS_LOBUTIL Unsupported package that describes SecureFile LOBs

Defined in $ORACLE_HOME/rdbms/admin/dbmslobu.sql

Subroutines include GETINODE GETLOBMAP GETEXTENTS

GETINODE Reports LOB header

GETLOBMAP Reports LOB extent header Numeric parameter is extent number (0..N-1)

GETEXTENTS Reports LOB extents in more detail Pipelined function - takes REF CURSOR as parameter

62


Secure FilesDBMS_LOBUTIL

DECLARE l_clob CLOB;l_inode DBMS_LOBUTIL_INODE_T;l_lobmap DBMS_LOBUTIL_LOBMAP_T;

BEGINSELECT c2 INTO l_clob FROM t29WHERE c1 = 0 FOR UPDATE;

l_inode := DBMS_LOBUTIL.GETINODE (l_clob);DBMS_OUTPUT.PUT_LINE ('LOBID '||RAWTOHEX (l_inode.lobid));DBMS_OUTPUT.PUT_LINE (' flags '||l_inode.flags);DBMS_OUTPUT.PUT_LINE (' length '||l_inode.length);DBMS_OUTPUT.PUT_LINE (' version '||l_inode.version);DBMS_OUTPUT.PUT_LINE (' extents '||l_inode.extents);

FOR i IN 0..l_inode.extents - 1LOOP

l_res := DBMS_LOBUTIL.GETLOBMAP (l_clob,i);DBMS_OUTPUT.PUT_LINE ('Extent# '||i); DBMS_OUTPUT.PUT_LINE (' rdba '||l_res.rdba);DBMS_OUTPUT.PUT_LINE (' nblks '||l_res.nblks);DBMS_OUTPUT.PUT_LINE (' offset '||l_res.offset);DBMS_OUTPUT.PUT_LINE (' length '||l_res.length);

END LOOP;END;

63


References Oracle Database Application Developer's Guide - Large Objects

10g Release 2 (10.2) - Part Number B14249-01

Oracle Database SecureFiles and Large Objects Developers Guide 11g Release 1 (11.1) - Part Number B28393-03

64


Any Questions ?

[email protected]

lob internals

Documents