1 / 64

LOB Internals

LOB Internals. Julian Dyke Independent Consultant. Web Version - December 2008. juliandyke.com . Objectives. Understand how LOBs use storage in the Oracle database Consider options to optimize LOB performance in terms of: Reads Writes Impact on physical I/O Impact on buffer cache.

carver
Download Presentation

LOB Internals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. LOB Internals Julian Dyke Independent Consultant Web Version - December 2008 juliandyke.com

  2. Objectives • Understand how LOBs use storage in the Oracle database • Consider options to optimize LOB performance in terms of: • Reads • Writes • Impact on physical I/O • Impact on buffer cache

  3. Basic Files Secure Files Agenda

  4. Basic Files

  5. 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)

  6. 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

  7. 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

  8. 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 • 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 • 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

  9. GETOPTIONS (11.1+) GET_STORAGE_LIMIT INSTR ISSECUREFILE ISTEMPORARY ISOPEN LOADFROMFILE LOADBLOBFROMFILE LOADCLOBFROMFILE OPEN READ SETOPTIONS (11.1+) SUBSTR TRIM WRITE WRITEAPPEND Basic FilesDBMS_LOB Package • BLOB and CLOB Subroutines • 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. Basic FilesDBMS_LOB Package • BFILE Subroutines • CLOSE • COMPARE • FILECLOSE • FILECLOSEALL • FILEEXISTS • FILEGETNAME • FILEISOPEN • FILEOPEN • GETLENGTH • INSTR • ISOPEN • OPEN • READ • SUBSTR

  11. Basic FilesDBA_LOBS View

  12. SYS_IL0000055604C00002$$LOB INDEX SYS_LOB0000055604C00002$$LOB DATA T1TABLE LOB LOB LOB 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,);

  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. SYS_IL0000055607C00002$$LOB INDEX SYS_IL0000055607C00003$$LOB INDEX SYS_LOB0000055607C00003$$LOB DATA SYS_LOB0000055607C00002$$LOB DATA T2TABLE LOB LOB LOB LOB LOB 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);

  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. SYS_LOB0000055612C00002$$LOB SYS_IL0000055612C00002$$LOB INDEX T3TABLE LOB LOB LOB SYS_LOB_P22LOB PARTITION SYS_LOB_P21LOB PARTITION SYS_LOB_P24INDEX PARTITION SYS_LOB_P23INDEX PARTITION P2TABLE PARTITION P1TABLE PARTITION Basic FilesPartitioned LOBs

  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. P1S1TABLE SUBPARTITION SYS_LOB0000055633C00003$$LOB T4TABLE SYS_IL0000055633C00003$$LOB INDEX LOB LOB LOB SYS_LOB_P41INDEX PARTITION SYS_LOB_P44INDEX PARTITION P1TABLE PARTITION P2TABLE PARTITION SYS_LOB_P34LOB PARTITION SYS_LOB_P31LOB PARTITION SYS_LOB_SUBP32LOB SUBPARTITION SYS_LOB_SUBP32INDEX SUBPARTITION SYS_LOB_SUBP33 INDEX SUBPARTITION SYS_LOB_SUBP33LOB SUBPARTITION SYS_LOB_SUBP35LOB SUBPARTITION SYS_LOB_SUBP35INDEX SUBPARTITION SYS_LOB_SUBP36INDEX SUBPARTITION SYS_LOB_SUBP36LOB SUBPARTITION Basic FilesSub Partitioned LOBs P1S2TABLE SUBPARTITION P2S2TABLE SUBPARTITION P2S1TABLE SUBPARTITION SYS_IL_P23 SYS_IL_P24

  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 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); • 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

  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); C2 occupies 0 bytes INSERT INTO t1 VALUES (c1,EMPTY_CLOB()); C2 occupies 37bytes

  21. Basic FilesLOB Initialization • LOB locator is initialized using EMPTY_CLOB() or EMPTY_BLOB() • For example: INSERT INTO t1 VALUES (c1,EMPTY_CLOB()); • Block dump includes : 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 Header LOBID Body Length Flags LOB Length Version

  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: 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 INSERT INTO t1 VALUES (0,EMPTY_CLOB) • LOB ID = (0x9219 = 37401) 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

  23. Basic FilesLOB Inline Storage • Example 1 - 12 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1, LPAD ('X',12,'X')); 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 0x1C = 28 0x0C = 12

  24. Basic FilesLOB Inline Storage • Example 2 - 400 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,LPAD ('X',400,'X')); 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. . . . . . . 0x01A0 = 416 0x0190 = 400

  25. Basic FilesLOB Inline Storage • Example 3 - 3964 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,LPAD ('X',3964,'X')); 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 . . . . . . . 0x0F8C = 3980 0x0F7C = 3964

  26. Basic FilesLOB Out-of-Line Storage • Example 4 - 3965 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,LPAD ('X',3965,'X')); 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 Header56 bytes LOB Data8132 bytes Trailer4 bytes

  28. Basic FilesOut-of-Line Storage CREATE DIRECTORY dir1 AS '/tmp'; CREATE TABLE t11 ( c1 NUMBER, c2 CLOB); INSERT INTO t11 VALUES (0,EMPTY_CLOB()); DECLARE l_bfile BFILE; l_clob CLOB; l_result NUMBER;BEGIN l_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. Basic FilesLOB Out-of-Line Storage • Example 5 - 69237 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,BFILE...); 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 DBA of LOB chunks 0x34 = 52 8 chunks 0x1055 bytes 8 x 8132 = 65056 0x1055 = 4181 65056 + 4181+ 69237

  30. Basic FilesLOB Out-of-Line Storage • Example 6 - 97584 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,BFILE...); 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 empty 0x40 = 64 12 chunks 0 bytes 12 x 8132 = 97584

  31. Basic FilesLOB Out-of-Line Storage • Example 7- 97585 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,BFILE...); 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 LOB Index is used for 13th chunk 0x40 = 64 12 chunks 1 byte

  32. Basic FilesLOB Out-of-Line Storage • Example 7 continued - 97585 byte LOB - ENABLE STORAGE IN ROW • LOB Index 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 LOBID First chunk number Key Data • LOB Index is always unique • Data is always 32 bytes

  33. Basic FilesLOB Out-of-Line Storage • Example 8 - 187264 byte LOB - ENABLE STORAGE IN ROW INSERT INTO t1 VALUES (c1,BFILE...); 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 12 chunks in row11 chunks in index 0x17 = 23 chunks 0xe4 = 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 Key Data Key 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) • CHUNK = 32768 (maximum) 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 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;

  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);BEGIN l_str := LPAD ('Y',4000,'Y'); SELECT c2 INTO l_clob FROM t19 WHERE c1 = 0; FOR UPDATE; DBMS_LOB.WRITE (l_clob,4000,10000,l_str);END;/

  40. Basic Files Read Consistency • Example 9 (continued) • Before Update • After 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 Updated Chunk

  41. 3FE 3FE 40F STOP 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 Key Data LOB Index Free List 3FD 3FF 400 3FC LOB Data

  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);BEGIN l_str := LPAD ('Y',4000,'Y'); SELECT c2 INTO l_clob FROM t19 WHERE c1 = 0; FOR UPDATE; DBMS_LOB.WRITE (l_clob,4000,30000,l_str);END;/

  43. Basic Files Read Consistency • Example 10 (continued) • Before Update • After 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 Updated Chunk Updated Chunk

  44. 3FE 40F 3FE 400 400 3FC 3FC 410 3FE STOP 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 Key Data LOB Index Free List 3FD 3FF LOB Data

  45. Basic FilesPartial Deletion • Example 11 - 69237 byte LOB - Erase 18000 bytes from offset 12000 DECLARE l_clob CLOB; l_amount NUMBER := 18000;BEGIN SELECT c2 INTO l_clob FROM t22 WHERE c1 = 0 FOR 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 • After ERASE 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

  47. 46C 47A 480 47F 470 46E 46D 47E 46F 470 47B 46F 47D 46E STOP 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 Data LOB Index Free List LOB Data

  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 (CACHE); CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (CACHE READS); CREATE TABLE t4 (c1 NUMBER, c2 CLOB) LOB (c2) STORE AS (NOCACHE); • Reported in DBA_LOB.CACHE • Can be NO, CACHEREADS or YES

  49. Basic FilesCACHE versus NOCACHE • CACHE LOGGING 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 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

  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 (CACHE READS 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 (NOCACHE NOLOGGING); • Reported in DBA_LOB.LOGGING • Can be NO or YES

More Related