I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale

Search My Oracle Blog

Custom Search

18 March, 2011

I/O for OutOfLine LOBs

When you define a LOB (CLOB or BLOB) as a column for a table, you have the choice of specifying whether it is Inline (stored with the row) or OutOfLine (stored in a separate segment). (Note : An Inline LOB still has a LOB Segment because an entry exceeding 4000bytes will automatically be stored OutOfLine).The data in the OutOfLine LOB, being stored in a separate segment (which is of type 'LOBSEGMENT') is accessed via an Index (which is of type 'LOBINDEX').

It is easy to map a LOB Segment to the actual Table Column. The Segment Name contains the ObjectID and ColumnID. Alternatively, you can query USER_LOBs.

OutOfLine LOBs are read and written in CHUNK sizes. Typically the CHUNK size is one datablock, although it can be defined to be a large multiple of the datablock size. Therefore, if you store less than a datablock sized contents in a LOB, the I/O is still executed as a datablock. Thus, in an 8KB tablespace, the I/O is one 8KB datablock even if you store only a few tens of bytes or hundreds of bytes in a deliberately defined OutOfLine LOB.

Here is a simple demonstration of LOB Storage and I/O. I start with a table that has 2 OutOfLine LOBs and one Inline LOB (remember : data exceeding 4000 bytes in Inline LOB is automatically stored OutOfLine so Oracle precreates a LOBSEGMENT even for the Inline LOB).

SQL> SQL> -- measure the writesSQL> -- notice how each LOB column had a write call (3 x 1000)SQL> -- also the data written is less than the CHUNKSIZE so it is 'unaligned'SQL> select n.name, s.value 2 from v$statname n, v$mystat s 3 where n.statistic#=s.statistic# 4 and n.name in ('lob reads','lob writes', 'lob writes unaligned') 5 /

Since the table has 3 LOBs, each LOB has a separate 'lob write' when INSERTing data. Because I insert less than 8KB, each write is 'unaligned'.The OutOfLine LOB columns still take up 87Bytes in the Table Row.The InLine LOB, even though I inserted 1500 characters takes up 3103 per entry. The total storage in each row in the Table segment is 3,310 bytes -- which, fortunately, fits into the 8KB block for a table (so I do not have Row Chaining).

As each OutOfLineLOB is an 8KB write, even a 10character write to OOL_LOB_1 takes an 8KB block. That is why the disk space for the two OutOfLine LOBs far exceeds that for the table rows itself.

When retrieving a LOB columnm, for each row retrieved, a separate read I/O is made.

If I were to retrieve only the non-LOB columns from the table, even if I do a FullTableScan, the OutOfLine LOBs are not read ! That is the advantage of them being OutOfLine. They are not read if they are not required. (Contrast that with normal columns in a table -- if reading a row, the normal columns are in the same row piece in the table block, so they are read from disk but, if not required by the query are ignored after the I/O is executed).

The statistics (from V$MYSTAT, V$SESSION_STAT, V$SYSSTAT and V$SEGMENT_STATISTICS) do show the Physical Reads and the LOB reads ('lob reads' are included in 'physical reads' so you don't need to add the two statistics).However, if you did an SQL trace, the LOB reads will not be apparent. Here's a tkprof of the SQL trace :

tkprof only shows that the Table was read --- it does not indicate that the LOBSegment for column OOL_LOB_1, which is a physically separate segment, was actually read. (It also doesn't show the LOBIndex being read).

So, the next time you are dealing with queries that retrieve OutOfLine LOBs (in the WHERE or SELECT clause), remember that additional LOB reads will be occurring as Physical Reads and this will not be apparent in a tkprof of a SQL Trace.

usprd204:PREMIERADBA.PP3PRE>select object_name,statistic_name,value from v$segment_statistics where object_name in 2 ('SYS_LOB0000313289C00005$$','SYS_LOB0000086636C00004$$','SYS_LOB0000082592C00005$$') 3 and statistic# in (0,4,6,5,7) order by object_name,statistic_name;