Shrinking Volatile OSM Table Sizes to Increase OSM Performance (Doc ID 2268291.1)

Last updated on MARCH 27, 2023

Applies to:

Purpose

The performance of some queries and delete statements in OSM can be improved by reducing the size of volatile tables.  Because these tables are relatively small, in terms of the number of records, the optimizer is more likely to choose a table scan.  Old partitions of these tables may be empty, but they still have blocks.  Therefore, as the number of partitions grows, these queries must scan a growing number of blocks.

The simple change of reducing the volatile table size has been found to improve order processing performance and reduce the time of certain administrative operations, like cartridge deployment. The more partitions that exist in the system, the larger the benefit by shrinking these tables. For instance, reductions in cartridge deployment times of a few minutes have been observed for systems with a large number of partitions.

Details

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