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