Upgrade of OSM table OM_ORDER_INSTANCE (Doc ID 2304297.1)

Last updated on APRIL 02, 2024

Applies to:

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:

• Description of the schema changes made in pre-upgrade
• Installing the OM_ORDER_INSTANCE pre-upgrade
• Executing the OM_ORDER_INSTANCE pre-upgrade
• Monitoring the OM_ORDER_INSTANCE pre-upgrade
• Stopping/Resuming the OM_ORDER_INSTANCE pre-upgrade
• Post Pre-Upgrade Steps

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

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