//
you're reading...
Administration, Database, Oracle

Space Usage of LOBs, General Behavior and in OTM Database

What is LOB

LOB (large object) is a group of Oracle database build-in data types. Using LOB data types in Oracle database allows user to store large blocks of unstructured data, such as text, graphic images, video clips and sound waveforms, in character or binary format. Since they were introduced in Oracle 8, LOB data types have been recommended by Oracle over LONG data types.

Types of LOBs

CLOB – Character LOB. Stores large amounts of text, such as XML files in the database character set.
NCLOB – National Character Set LOB. Stores national character set data.
BLOB – Binary LOB. Stores large amounts of binary information.
BFILE – An external LOB. The database stores a pointer to a file in the operating system.

LOB Storage

The database stores LOBs differently from other data types. Creating a LOB column implicitly creates a LOB segment and a LOB index. LOB segment can be in a different tablespace from the one that contains the table. LOB index is always stored in the same tablespace as the associated LOB segment. Depends on LOB’s definition, small amounts of LOB data can be stored in the table itself rather than in a separate LOB segment.


CHUNK – a logically contiguous set of data blocks and the smallest unit of allocation for a LOB segment. A row in the table stores a pointer called a LOB locator, which points to the LOB index. When the table/LOB is queried, the database uses the LOB index to quickly locate the LOB chunks.


Read Consistency – Unlike table data, the database does not use UNDO tablespace to record changes of LOBs. The before images are stored in the segment itself. When a transaction changes a LOB database allocates a new chunk and leaves the old data in place. If the transaction rolls back the database rolls back the changes in the index to point to the old chunk.

SecureFile LOB and BasicFile LOB

Starting with Oracle Database 11g, SecureFile LOB data type was introduced. SecureFiles LOB is specifically engineered to deliver high performance for file data comparable to that of traditional file systems, while retaining the advantages of Oracle Database. The SECUREFILE LOB enables advanced features such as deduplication, compression, and encryption.


The original LOB storage architecture is now named BasicFile LOB. Compared with BasicFile LOBs SecureFile LOBs improve the performance and space usage manageability of database LOBs. For BasicFile LOBs CHUNK size is fixed and cannot be changed after the LOB is created. However with SecureFile LOBs, chunks vary in size from Oracle data block size up to 64 MB. The Oracle Database server attempts to co-locate data in physically adjacent location on disk, therefore minimizing internal fragmentation. By using variable chunk sizes SecureFile avoids versioning of large, unnecessary blocks of LOB data.

Check LOB Segment Size and Space Used by LOB Data

The allocated size of LOB segments of one schema owner can be found by below query:

select s.segment_name, s.partition_name, bytes/1048576 "Size (MB)"
from dba_segments s, dba_lobs l
where s.segment_name = l.segment_name
and s.segment_name not like 'SYS%'
and s.owner = ‘’
order by s.segment_name, s.partition_name;

Like other database segment types, the allocated space of a LOB segment can be divided into spaces above and under high water mark. The total space usage under LOB segment’s high water mark can be obtained by calling DBMS_SPACE.SPACE_USAGE procedure. This procedure has two overload forms, one for BasicFile LOBs and the one for SecureFile LOBs. The condition to use this procedure is the tablespace of the LOB segment needs to be ASSM (Automatic Segment Space Management).


The total size of real LOB data is usually much smaller than the allocated LOB segment size. To find out the size of LOB data in a table, run below query against the table.

select sum(dbms_lob.getlength ()) from <table_name>;

LOBs in OTM Database

As of this blog date, in the latest OTM (Oracle Transportation Management) ) version, 6.3.2, OTM out-of-box database does not use SecureFile for its LOBs. In order to be able to compress, to encrypt, to deduplicate LOBs, and to have better disk space management of LOBs customers have to migrate BasicFile LOBs to SecureFile LOBs on their own. Hope Oracle will change to use SecureFile LOBs in the future OTM release soon.


Below discussion are all based on BasicFile LOBs.


In OTM database, there are several tables with CLOB columns (BasicFile LOBs, as mentioned above). These LOB segments are stored in tablespaces LOB1, LOB2, .., LOB7, which are all ASSM (Automatic Segment Space Management). The biggest space usage of OTM LOBs is from I_TRANSACTION and I_TRANSMISSION table. Below discussions will use I_TRANSACTION table as an example.

LOB Space Allocations

I_TRANSACTION table is partitioned (by range). There are total 16 partitions in the table. Column XML_BLOB is defined as CLOB (BasicFile) data type, and stores OTM integration XML documents. The LOB is also partitioned into 16 partitions. Following query finds out the allocated size of LOB partition segments of I_TRANSACTION table:

select l.column_name, l.partition_name, l.lob_name, l.lob_partition_name, s.bytes/1048576 "Size (MB)"
from dba_segments s, dba_lob_partitions l
where s.segment_name = l.lob_name
and s.owner='GLOGOWNER'
and l.table_name ='I_TRANSACTION'
and l.lob_partition_name = s.partition_name;

COLUMN NAME  PARTITION NAME          LOB NAME                LOB PARTITION   Size (MB)
------------ ----------------------- ----------------------- --------------- ----------
XML_BLOB     INBOUND1                I_TRANSACTION_XML_BLOB  SYS_LOB_P1136        57500
             INBOUND2                                        SYS_LOB_P1137        45230
             INBOUND3                                        SYS_LOB_P1138        43645
             INBOUND4                                        SYS_LOB_P1139        49240
             OUTBOUND1                                       SYS_LOB_P1140        91355
             OUTBOUND2                                       SYS_LOB_P1141        82185
             OUTBOUND3                                       SYS_LOB_P1142        74030
             OUTBOUND4                                       SYS_LOB_P1143        94370
             OUTBOUND5                                       SYS_LOB_P1135       353805
             I_TRANSACTION_MSG_PART1                         SYS_LOB_P1144            5
             I_TRANSACTION_MSG_PART2                         SYS_LOB_P1145            5
             I_TRANSACTION_MSG_PART3                         SYS_LOB_P1146            5
             I_TRANSACTION_MSG_PART4                         SYS_LOB_P1147            5
             I_TRANSACTION_MSG_PART5                         SYS_LOB_P1148            5
             I_TRANSACTION_MSG_PART6                         SYS_LOB_P1149            5
             I_TRANSACTION_MSG_PART7                         SYS_LOB_P1150            5
---------------------------------------------------------------------------- ----------
Total                                                                           891,395

Using DBMS_LOB.GETLENGTH we can get total size of XML document in each LOB partition. The results are show below:

Table Partition         LOB Data (MB)
----------------------- -------------
Inbound1                         5137
Inbound2                         3132
Inbound3                         3320
Inbound4                         3966
Outbound1                       15543
Outbound2                       17439
Outbound3                       19028
Outbound4                       15262
Outbound5                      168880
I_TRANSACTION_Msg_Part1
I_TRANSACTION_Msg_Part2
I_TRANSACTION_Msg_Part3
I_TRANSACTION_Msg_Part4
I_TRANSACTION_Msg_Part5
I_TRANSACTION_Msg_Part6
I_TRANSACTION_Msg_Part7
------------------------- -----------
Total                         251,707

From above results we can see in this OTM database the MSG partitions do not have any LOB data in I_TRANSACTION table. The size of LOB segments for these partitions is 5 MB (the extent size of the tablespace). For other partitions, the allocated spaces of the LOB segments are much bigger than the actual used spaces by LOB data.


Let’s explore further the space usage at data block level of LOB segments by  using DBMS_SPACE.SPACE_USAGE. We can obtain information about how many blocks ( or MB) are used and how many blocks are empty for each LOB partition segment under high water mark . Following are the results from the procedure calls for all LOB partitions of the I_TRANSACTION table:

LOB_PARTITION   Full (MB)  Empty (MB)   Total(MB)
--------------- ---------- ---------- ----------
SYS_LOB_P1136        17249      40067      57316
SYS_LOB_P1137        17798      27287      45085
SYS_LOB_P1138        20152      23353      43505
SYS_LOB_P1139        12952      36130      49082
SYS_LOB_P1140        42528      48537      91065
SYS_LOB_P1141        51537      30387      81924
SYS_LOB_P1142        54565      19230      73795
SYS_LOB_P1143        46548      47523      94071
SYS_LOB_P1135       352691          3     352694
SYS_LOB_P1144            0          0          0
SYS_LOB_P1145            0          0          0
SYS_LOB_P1146            0          0          0
SYS_LOB_P1147            0          0          0
SYS_LOB_P1148            0          0          0
SYS_LOB_P1149            0          0          0
SYS_LOB_P1150            0          0          0
--------------- ---------- ---------- ----------
Total              616,020    272,517    888,537

Purging Effect on LOB Space

OTM database provides a utility (domainman.transmission_purge) to purge old transaction/transmission data. It deletes old rows from a group of tables. I_TRANSACTION table is one of them. In this section we discuss what effect of deleting LOBs on the LOB segment space usage.


First run the purge process to delete old rows.

SQL> execute domainman.transmission_purge(P_AGE_IN_DAYS=>90, P_ALLOWED_MINUTES=>120, -
P_LIMITED_BATCH_SIZE=> 10000, P_OPTIONAL_WHERE_CLAUSE=>null, P_KEEP_STATUS_CLOB=>false);

I_TRANSMISSION: 2495289 rows deleted.
I_TRANSMISSION_ACK: 27748
I_TRANSMISSION_PGROUP: 18724
I_TRANSMISSION_REFNUM: 0
I_TRANSMISSION_REPORT: 0
I_TRANSACTION: 2304202 rows deleted.
I_LOG: 1211494
I_TRANSACTION_ACK: 0
I_TRANSACTION_DETAIL: 736888
I_TRANSACTION_REFNUM: 0

PL/SQL procedure successfully completed.

After the purge process more than 2 million rows have been deleted from I_TRANSACTION table. Call DBMS_LOB.GETLENGTH for each LOB partition to get current LOB data size for each partition:

Table Partition         LOB Data (MB)
----------------------- -------------
Inbound1                         4172
Inbound2                         3132
Inbound3                         3059
Inbound4                          556
Outbound1                        9914
Outbound2                       11062
Outbound3                       14008
Outbound4                       12461
Outbound5                      168880
------------------------- -----------
Total                         227,244

From above results we can see LOB data are less than before the purge. However the allocated spaces for each partition segments do not change after the purge as seen below:

COLUMN NAME  PARTITION NAME          LOB NAME                LOB_PARTITION    Size (MB)
------------ ----------------------- ----------------------- --------------- ----------
XML_BLOB     INBOUND1                I_TRANSACTION_XML_BLOB  SYS_LOB_P1136        57500
             INBOUND2                                        SYS_LOB_P1137        45230
             INBOUND3                                        SYS_LOB_P1138        43645
             INBOUND4                                        SYS_LOB_P1139        49240
             OUTBOUND1                                       SYS_LOB_P1140        91355
             OUTBOUND2                                       SYS_LOB_P1141        82185
             OUTBOUND3                                       SYS_LOB_P1142        74030
             OUTBOUND4                                       SYS_LOB_P1143        94370
             OUTBOUND5                                       SYS_LOB_P1135       353805
---------------------------------------------------------------------------- ----------
Total                                                                           891,360

Moreover the purge process does not change the number of full/empty blocks under high water mark, therefore does not release any free space. This can be shown by calling DBMS_SPACE.SPACE_USAGE procedure again after purge:

LOB_PARTITION   Full (MB)  Empty (MB)   Total(MB)
--------------- ---------- ---------- ----------
SYS_LOB_P1136        17249      40067      57316
SYS_LOB_P1137        17798      27287      45085
SYS_LOB_P1138        20152      23353      43505
SYS_LOB_P1139        12952      36130      49082
SYS_LOB_P1140        42528      48537      91065
SYS_LOB_P1141        51537      30387      81924
SYS_LOB_P1142        54565      19230      73795
SYS_LOB_P1143        46548      47523      94071
SYS_LOB_P1135       352691          3     352694
--------------- ---------- ---------- ----------
Total              616,020    272,517    888,537

To turn the deleted space into free space, you have to rebuild the freepools. The command used to do this is:

alter table glogowner.i_transaction modify lob(xml_blob) (freepools 3);

After above command finishes, run DBMS_SPACE.SPACE_USAGE again:

LOB_PARTITION   Full (MB)  Empty (MB)   Total(MB)
--------------- ---------- ---------- ----------
SYS_LOB_P1136        14281      43035      57316
SYS_LOB_P1137        10059      35025      45085
SYS_LOB_P1138        10299      33206      43505
SYS_LOB_P1139         1992      47090      49082
SYS_LOB_P1140        21702      69363      91065
SYS_LOB_P1141        24314      57610      81924
SYS_LOB_P1142        30498      43297      73795
SYS_LOB_P1143        27335      66736      94071
SYS_LOB_P1135       352691          3     352694
--------------- ---------- ---------- ----------
Total              493,171    395,365    888,537

We can see total size of FULL blocks reduced and total size of EMPTY blocks increased (for some partitions). The released space is still under the segment’s high water mark, so it can only be re-used by the same segment. In other words, only the new data in each partition (inbound1, inbound2, …, etc.) can reuse its own free space. In this example, partition Outbound5 (SYS_LOB_P1135) did not get purged of any data and does not have much empty space. When new data are inserted to this partition, Oracle has to allocate more space for it, even though other LOB partitions have a lot of empty space.

Shrinking LOB to Free Space Back to Tablespace

To return the empty blocks in LOB segments (partitions) you need to use SHRINK SPACE clause to LOBs. Following SQL is used to “shrink space” of LOBs in I_TRANSACTION table.

SQL> alter table glogowner.i_transaction modify lob(xml_blob) (shrink space);

After shrink, all of empty blocks are removed from the LOB partition segments as below result shown:

LOB_PARTITION_N       Full      Empty      Total
--------------- ---------- ---------- ----------
SYS_LOB_P1136        14281          0      14281
SYS_LOB_P1137        10059          0      10059
SYS_LOB_P1138        10299          0      10299
SYS_LOB_P1139         1992          0       1992
SYS_LOB_P1140        21702          0      21702
SYS_LOB_P1141        24314          0      24314
SYS_LOB_P1142        30498          0      30498
SYS_LOB_P1143        27335          0      27335
SYS_LOB_P1135       352691          2     352694
--------------- ---------- ---------- ----------
Total              493,171          2    493,174

Also if we look at the allocated space for each segment we can see they are much smaller now.

COLUMN_NAME  PARTITION_NAME          LOB_NAME                LOB_PARTITION_N  Size (MB)
------------ ----------------------- ----------------------- --------------- ----------
XML_BLOB     INBOUND1                I_TRANSACTION_XML_BLOB  SYS_LOB_P1136        14330
             INBOUND2                                        SYS_LOB_P1137        10095
             INBOUND3                                        SYS_LOB_P1138        10335
             INBOUND4                                        SYS_LOB_P1139         2005
             OUTBOUND1                                       SYS_LOB_P1140        21775
             OUTBOUND2                                       SYS_LOB_P1141        24395
             OUTBOUND3                                       SYS_LOB_P1142        30600
             OUTBOUND4                                       SYS_LOB_P1143        27425
             OUTBOUND5                                       SYS_LOB_P1135       353805
---------------------------------------------------------------------------- ----------
Total                                                                           494,765

Conclusion

In OTM database, just running transmission_purge process does not release any space to be reused by new data. To free the space from deleted LOBs back to LOB segment you need to rebuild LOB’s freepools. To free the empty space inside LOB segments back to tablespace , you need to “shrink space” to LOBs.

Advertisements

About Hong Wang

I am an Oracle DBA, working in Oracle database since version 7.3. Worked in both application development and production support. Many experiences in real world complicate problems and database projects. This blog serves as a collections of notes I write on my database studies as well as issues I encountered/solved. Your comments are well welcome.

Discussion

2 thoughts on “Space Usage of LOBs, General Behavior and in OTM Database

  1. Hi, Thanks for the detailed post. How are you using DBMS_LOB.GETLENGTH on a table partition? Are you using a PLSQL Procedure?

    Posted by Rajan | July 10, 2014, 2:17 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: