Share This:

Summary

Oracle 12C has made a major change to the default behavior of LOB fields which are now stored using the SecureFiles format.

Remedy ARS makes extensive use of CLOB fields which in Oracle 12C are now stored, by default, using SecureFiles.

Prior to Oracle 12C Release 2, Oracle will always store SecureFiles CLOB objects out-of-row which will result in serious performance impacts for data migration and Remedy application usage.

Alderstone recommends upgrading Oracle to 12C Release 2.

 

 

What is a CLOB?

CLOB stands for "Character Large Object." A CLOB is a data type used by various database management systems, including Oracle. It stores large amounts of character data. The CLOB data type is similar to a BLOB, but includes character encoding, which defines a character set and the way each character is represented. BLOB data, on the other hand, consists of unformatted binary data.

 

 

How does Remedy ARS use CLOBs?

Data in Character fields for your Remedy application running on an Oracle database is either stored in a column with a data type of either VARCHAR2 or CLOB column.

Remedy ITSM is a very heavy user of CLOBs to store data, largely for historical reasons.

 

Remedy Corporation was started in 1990 which makes Remedy ARS System what we'd call "mature" software.

 

Over this time the maximum length of a character field which can be stored in the VARCHAR2 field has dramatically increased:

 

Oracle VersionRelease DateMax Length Bytes VARCHAR2
Oracle 7June 20072000
Oracle 11GSept 20094000
Oracle 12GJuly 201332,767

 

The latest Remedy ARS System 9.1 will store any character field which has a maximum length of 4000 or more characters as a CLOB.

 

There are 2,888 Fields in an out-of-the-box ITSM System which will be stored as CLOBS. You can obtain this list for your application using the following SQL query:

 

select a.name, f.fieldname, maxlength from field_char c JOIN field f on f.schemaid = c.schemaid and f.fieldid =c.fieldid

JOIN arschema a on a.schemaid = f.schemaid

where ( maxlength >4000  OR maxlength = 0) AND f.fieldid != 15 and f.foption != 4

order by 1, 2 asc

 

Out-of-Row versus In-Row CLOBs

There are two places where the CLOB data may be stored:

  • In-Row - the CLOB data is stored with the rest of the data for a record. LOB data may only be stored in-row if its less than ~4000 bytes.
  • Out-of-Row - the CLOB data is stored in a separate location and the record only stores a reference to the CLOB data location. CLOB data will be stored out of row if the data is more than 4000 bytes or the database column is set to use out-of-row CLOB storage

 

Please see this very useful explanation.

 

The following SQL query can be used to show whether your Remedy system running on Oracle has columns set to always use out-of-row storage for CLOBS and which fields have this setting:

 

select a.name, l.table_name, l.column_name, l.in_row from all_lobs l, arschema a where l.table_name = ‘T’||a.schemaid and l.in_row = 'NO';

 

Storing CLOBs out of row has known a negative impact on performance of both data migration and the Remedy application.

BMC has issued a number of warnings about the performance impact and provide SQL scripts to convert out of row to in-row CLOB storage. Please see the following links for more details:

 

 

 

Oracle 12C Behavior

Oracle 12C prior to Release 2, appears to ignore the setting to store CLOBS in-row when using SecureFiles and will always store them out-of-row with the resulting known performance impact.

Upgrading to Oracle 12C Release 2 will fix this defect for SecureFiles. It will be necessary to run a number of database  scripts from Oracle to accomplish this conversion.

 

 

Performance Metrics

We tested merging the same data set of 1 million CIs  into the BMC.CORE:BaseElement T-Table. We used two copies of the T-Table, one with SecureFiles as the CLOB storage and the other using BasicFiles storage on Oracle 12C Release 1.

 

CLOB StorageMerge Duration
BasicFiles5 mins 40 seconds
SecureFiles51 mins 30 seconds

 

That makes SecureFiles 9 times slower than BasicFiles for the same operation. A significant difference was also seen in the storage requirements with SecureFiles using much more space in LOB segments and failing to reclaim space after a delete operation.

 

After patching this difference was significantly reduced:

 

CLOB StorageMerge Duration
BasicFiles5 mins 40 seconds
SecureFiles25 mins 5 seconds

 

Storage for SecureFiles after the patch reverted back to expected levels and we observed that the CLOB in-row storage setting was now being respected.

 

 

Further Investigation
Please be aware that in even after patching to Oracle 12C Release 2, Alderstone continue to see unexplained but significant performance differences when using SecureFiles versus BasicFiles for INSERT and MERGE operations. There is an open and ongoing investigation being performed by Oracle into these differences.