My Oracle Support Banner

Upgrade of OSM table OM_ORDER_INSTANCE (Doc ID 2304297.1)

Last updated on JULY 29, 2019

Applies to:

Oracle Communications Order and Service Management - Version 7.2.4 to 7.3.5.0.0 [Release 7.2 to 7.3.0]
Information in this document applies to any platform.
Oracle Communications Order and Service Management - Version 7.2.4.3 and later patch levels for that release as well as version 7.3.5.1 and later.
Does not apply to OSM 7.3.0 or 7.3.1 releases.

Purpose

The OM_ORDER_INSTANCE table is the largest table in OSM and contains several very large indexes. These indexes can cause high demands on storage, IO, CPU and SGA. In addition, when using row-based purging a significant portion of time is used updating these indexes, and fragmentation of these indexes may require regular index rebuilding.


To improve the performance of these indexes they are replaced by function based indexes, and will only contain a small subset of the tables records. This requires an upgrade to the OM_ORDER_INSTANCE table and its indexes, since this table is the largest table in OSM the upgrade can take a long time.


To help shorten the offline upgrade maintenance window a pre-upgrade database procedure is available that can be executed online, days or weeks before the actual upgrade.  The pre-upgrade script prepares the OM_ORDER_INSTANCE table so that the required offline upgrade window is shorter.  

The following article contain the following sections:

During upgrade testing it took approximately 1 minute per hash subpartition of the OM_ORDER_INSTANCE table to execute.  Therefore, if a system has 50 full order range partitions and 32 subpartitions each, it would take about 27 hours total to upgrade the table (50 order partitions x 32 subpartitions x 1 minute per partition / 60 minutes per hour).  This time can vary based on several factors (parallelism, partition size, hardware, cartridge/order data).  To improve the execution times it is best to purge/drop as many of the older order partitions as possible before running the pre-upgrade.

The pre-upgrade should be executed during off-peak order volumes because it is resource intensive.  Reducing the parallelism used by pre-upgrade can reduce the resources required, but lengthens the execution time. The pre-upgrade only updates data in older, full partitions, the active partitions are updated as part of the actual offline upgrade.  The pre-upgrade can be stopped at any time, then started again and it resumes where it left off.

 

Scope

Applies to customers upgrading to OSM 7.2.4.3.0 (and later patch levels) or OSM 7.3.5.1.0 (as well as later patch levels and subsequent releases) from prior releases. Customers already on 7.2.4.3.0 (and subsequent patch levels) would not require this pre-upgrade step , as this schema migration will have already been performed. All other releases, including OSM 7.2.x, 7.3.0.x and 7.3.1.x will undergo this schema migration and hence would benefit from this pre-upgrade step.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 Description of the schema changes made in pre-upgrad
 Installing the OM_ORDER_INSTANCE pre-upgrade
 Executing the OM_ORDER_INSTANCE pre-upgrade
 Monitoring  the OM_ORDER_INSTANCE pre-upgrade
 Monitoring Partitioned Schema OM_ORDER_INSTANCE pre-upgrade
 Monitoring Non-Partitioned Schema OM_ORDER_INSTANCE pre-upgrade
 Stopping/Resuming  the OM_ORDER_INSTANCE pre-upgrade
 Post Pre-Upgrade Steps

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.