Database considerations for Oracle
Character sets
Oracle character sets require careful handling. BMC Helix ITSM typically requires NLS_LENGTH_SEMANTICS to be set to BYTE, so the length of character fields is measured in bytes, not characters. When moving data to a new Oracle database that does not have an identical character set or one that is not compatible with the source database, the number of bytes used to store data might change. Consequently, some data might be too long to be stored in the target system. With point fixes, you can apply data transformation to the impacted fields, but this significantly increases the migration effort due to the iterative migrations required to identify all affected fields.
Instead, you can apply a system-wide configuration option to allow BMC Helix Data Manager to automatically truncate the data to the byte length of the target field. Instance IDs and Request IDs are excluded from this transformation. You can use this system-wide setting if you are changing Oracle character sets to an incompatible character set. Use this option with caution, after analysis, to prevent unwanted data loss. This system-wide setting impacts import performance because all character data is transformed on import.
It is simpler to ensure from the outset that the Oracle Character sets are compatible (that is, the same number of bytes are needed to store all characters) or identical.
RAM and memory requirements
While RAM requirements can vary, we recommend that the RDBMS server has as much RAM as your production system. For most environments with databases of up to 2 TB, this is usually in the 196 to 256 GB range.
For Oracle, the memory target should be set to the same values as your production databases.
Too much RAM will not negatively impact performance, and too little RAM can be a bottleneck.
Tablespace requirements—export
BMC Helix Data Manager creates tables as part of the export of dependent data. These tables hold the key values for identifying records in child forms that should be included in the scope of the data export. During the export process, these tables are dropped as soon as possible (that is, as soon as the all child forms are processed). BMC Helix Data Manager stores only key information in these temporary tables. For example, for a 20-million-row table that has child records with a qualification on the Incident Number field, the storage requirement can be calculated as follows:
Column | Data type | Per row | 20 million rows |
|---|---|---|---|
C1 | VARCHAR(15) | 30 bytes | 572 MB |
C1000000001 | VARCHAR(15) | 30 bytes | 572 MB |
TOTAL | 1114 MB |
We recommend that the BMC Helix Data Manager schema owner has its own tablespace assigned and this tablespace is sized to be approximately 10% of the total data size. This guideline is based on typical data distribution across a typical BMC Helix ITSM system. You should monitor the size during your initial test migration and increase the size if necessary.
Tablespace requirements—threading
Multithreading can improve the overall performance of migration, but then multiple tables are exported in parallel. This increases the impact on the database table space because more than one set of temporary tables are created when performing multithreaded export and import. Make sure that you have sufficient database resources to handle a multithreaded migration if you use this feature.
Performance impact of CLOB columns on migration
If you migrate data on the CLOB columns, the migration runs for a long time and the performance is impacted due to the following reasons:
- The MERGE statement takes a long time to execute.
- Indexes are not utilized because CLOB columns are involved on the target database table.
- Internally, the Oracle database takes a long time to execute the Hash join while importing data on the CLOB columns.
AR System in-row CLOB usage
AR System supports two options for CLOB storage. If you have out-of-row CLOBs (now deprecated by BMC), your performance will be poor for day-to-day usage of the application and for data migration. To establish whether your tables and columns have out-of-row storage configured, run the following SQL command as the aradmin user or the BMC Helix Data Manager user:
The presence of out-of-row CLOBs will affect your production BMC Helix ITSM system performance and increase the DB storage space that the database requires. We recommend that you review and fix this configuration in the production database. For more information, see Evaluating and specifying storage for Oracle LOBs.
BMC provides a stored procedure that performs the conversion. The procedure takes a long time to execute and requires DBA support. For more information, see Converting LOB storage
AR System in-row CLOB usage
To see how many records in each CLOB that uses out-of-row storage would be in-row, run the following SQL command to generate the SQL statements. Run the command as the aradmin user or the BMC Helix Data Manager user.
Run the SQL output as an SQL command.
'select '''||a.name||''',
'''||l.table_name||''','''||l.column_name||''',count(*) total, sum(case
when length('||l.column_name||') > 3964 then 1 else 0 end) over_4k from
'||l.table_name||' UNION ALL'
from all_lobs l, arschema a where
l.table_name = ‘T’||a.schemaid and l.in_row = 'NO'
UNION ALL
select 'select ''----'',''----'',''----'',0 ,0 from dual’ from dual;
AR System case-insensitive option for Oracle
If you have configured your Oracle database as case-insensitive, the performance of BMC Helix Data Manager will be impacted. You can mitigate the impact of this by configuring BMC Helix Data Manager to set the NLS_COMP and NLS_SORT parameters to BINARY_CI, and to create function-based indexes for its temporary tables by using the Properties tab on the System Configuration page in the BMC Helix Data Manager Workbench (select Configuration > Register & Configure Systems). See Registering-source-and-target-systems.