Skip Headers
Oracle® Communications Order and Service Management System Administrator's Guide
Release 7.2.2

E35414-02
Go to Documentation Home
Home
Go to Table of Contents
Contents
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

8 Partitioning Your OSM Database Schema

This chapter describes how to manage partitions in your Oracle Communications Order and Service Management (OSM) database schema.

About Partitioning Your OSM Database Schema

Database partitioning increases performance for systems with high order volumes, and in OSM installations which use Oracle Real Application Clusters (RAC). Database partitioning allows tables and indexes to be subdivided into smaller pieces which can be individually managed. This makes it easier to manage large volumes of data. For example, you can purge obsolete data in a partition without affecting data residing in other partitions.

Database partitioning, including the addition of new partitions, can be managed automatically by OSM. However, Oracle recommends that you add partitions manually in production environments, especially if you use Oracle RAC.

OSM tables which accumulate order-related information are range-partitioned by ORDER_SEQ_ID. Range partitions are often sub-partitioned using hash partitioning to reduce I/O contention. Tables are partitioned using equi-partitioning. For more information about the different types of partitioning, see the Oracle Database documentation.

The OSM installer allows you to choose whether or not to enable partitioning. The following values, set on the Database Schema Partition Information installer screen, affect partitions created during installation, as well as partitions created automatically by OSM after installation:

OSM creates the first range partition of each partitioned table during the installation (or upgrade) process. New partitions can be created in different tablespaces. OSM can create partitions automatically, as described in "Creating Partitions Automatically" (recommended only for development and testing environments). You can also add partitions manually, as described in "Adding Partitions with Oracle Scripter".

During installation, you choose tablespaces for Order Data and Order Index. If you choose the same tablespace for both of these, the OSM installer creates local index partitions in the default tablespace. New index partitions will be created in the same tablespace as new table partitions. If you specify a different tablespace for index partitions, the OSM installer creates all index partitions in the index tablespace regardless of where table partitions are created.

About OSM Database Partition Configuration

Optimal settings for the Orders per Partition and Number of Sub-partitions fields are dependent on factors such as data volume, throughput requirements, and maintenance policies.

Typical values for Orders per Partition range between 100000 (100 thousand) and 1000000 (1 million).

Hash sub-partitioning reduces I/O contention and improves performance for some queries through partition pruning. The default Number of Sub-partitions value is 64. A smaller number of sub-partitions is not effective for reducing I/O contention in high volume environments. A very large number of sub-partitions can have an adverse impact on queries that cannot take advantage of partition pruning, and can result in slightly increased CPU usage.

Oracle recommends that you set the Oracle Database initialization parameter deferred_segment_creation to true (the default). This setting saves disk space and minimizes the time it takes for OSM to automatically create new partitions. This can help avoid long delays and timeouts in database operations.

Starting with Oracle Database 11.2.0.2, the initial extent size for partitioned tables is 8 MB. If you have many hash sub-partitions, partitioned tables can consume a lot of space even if they contain very little data. For example, even with deferred segment allocation, a table with 64 sub-partitions can quickly expand to 512 MB. Although this is not an issue for production environments, it could be an issue in development or testing environments with limited storage capacity. In those environments, you can use a small number of sub-partitions (for example, 4), or a tablespace with uniform extent allocation and a small extent size (for example, 64 KB).

Oracle recommends that you set the Number of Sub-partitions value to a power of 2. Using other values can result in data skew; data unevenly distributed across partitions.

Oracle Database limits the number of partitions to 1024K - 1. For example, if you enter 64 in the Number of Sub-partitions field, range partitions can be of up to 16 KB in size. With a setting of 100000 in the Order per Partition field, you can keep 1.6 billion orders in the database before you must drop old partitions.

About Range Partitioning Configuration

Range partitioning helps you manage large volumes of data. You can drop old partitions if all orders in a partition are closed. You can drop a partition with no pending orders even if older partitions have pending orders. However, if you have long-lived orders (for example, data service orders that take weeks or months to complete), a large partition size can increase the length of time you must keep partitions before you are able to drop them.

Range partitioning improves performance for some queries through partition pruning, a database technique that allows a query access to a subset of partitions. However, a very large partition size makes partition pruning less effective. A very small partition size result in a large number of partitions. This can have a small negative impact on queries that cannot take advantage of partition pruning and can result in increased CPU usage. For more information about the different types of partitioning, see the Oracle Database documentation.

You can change the initial selections used by the OSM installer by updating the range_partition_size and subpartitions_number OSM database parameters (see "Partition Configuration Parameters"). Updates to these parameters do not affect existing partitions.

If you use an Oracle RAC database, range partitioning is critical for optimal performance. Each Oracle RAC instance processes orders in a different partition. This approach minimizes cluster waits.

Database Partitioning and Order IDs

Each database instance can allocate unique IDs to new orders. OSM allocates a block of unique order IDs for each database instance (for both single instance and Oracle RAC databases). The block size equals the partition size. When a block of order IDs is exhausted, the next block is allocated. If the corresponding partition does not exist, OSM will attempt to create it automatically. Range partitioning works best if the partition size is larger than 50,000 orders.

If you use an Oracle RAC database, OSM order IDs are not assigned sequentially. For example, if the partition size is 1 million orders, the group of WebLogic servers interacting with one Oracle RAC instance could generate order IDs 1000001 to 2000000 while another group of WebLogic servers interacting with a second Oracle RAC instance could generate order IDs 2000001 to 3000000. Use a smaller partition size to avoid large differences in the order ID ranges allocated by different instances.

Managing Partitions

The OSM installer automatically creates the first partition for each range-partitioned table. Adding new partitions and dropping old ones are the most common partition maintenance operations.

Partition Configuration Parameters

The following configuration parameters affect partition maintenance operations. Updates to these parameters do not affect existing partitions:

  • range_partition_size: This OSM parameter specifies the number of orders per partition. You can change it with the following SQL command, where N is the value in quotes (for example '100000').

    update om_parameter set value = N where mnemonic = 'range_partition_size';
    commit;
    

    Note:

    OSM uses the range_partition_size value to create the partition name. The syntax is P_ followed by a string of zeros up to the range_partion_size value. The string is always 18 character long.

    For example, if the range_partition_size were 100000, then the partition name is P_000000000000100000.

    For every new partition, the name of the next partition is the limit of the previous partition plus the value of the range_partition_size parameter.

    For example, if the previous partition were P_000000000000100000 then the next partition would be P_000000000000200000.

  • subpartitions_number: This OSM parameter specifies the number of hash sub-partitions. You can change it with the following SQL command, where N is the new value in quotes (for example '32').

    update om_parameter set value = N where mnemonic = 'subpartitions_number;
    commit;
    
  • deferred_segment_creation: This Oracle database initialization parameter, if set to true (the default), forces the database to wait until the first row is inserted into a table before creating segments for tables and their dependent objects. This parameter saves disk space and minimizes the time it takes to create a partition.

Adding Partitions with Oracle Scripter

To add range partitions, on the Windows machine where you installed the Database Utilities OSM Component, open the OSM_Home\Database\osm\maintenance\ dropPartitions\new_part.sql script using Oracle Scripter and update the values of the parameters in Table 8-1. The limit of the next partition is the limit of the latest partition plus the value of the range_partition_size parameter.

Note:

To specify the tablespace for the new partition you must execute the stored procedures as described in "Adding Partitions Using Stored Procedures".

Table 8-1 Schema Parameters Required by Oracle Scripter

Parameter Description

db admin username

The database administrator's username.

db admin password

The database administrator's password.

db connection string

The database connect identifier. Refer to the Oracle Net Services documentation for the connect identifier syntax. Some examples include:

  • ORCL

  • localhost:1521/osm.us.example.com

ordermgmt owner schema

The core database schema name.

ordermgmt owner password

The core database schema password.


Adding Partitions Using Stored Procedures

The new_part.sql script that is executed by Oracle Scripter runs the ADD_HIST_PARTITION and REBUILD_IND_PARTITION stored procedures in the OM_PART_CONFIG_PKG package. These procedures prepare SQL statements in the OM_SQL_LOG table, but do not run them. The script runs the OM_PART_CONFIG_PKG.EXEC procedure to perform those statements.

You may prefer to run the ADD_HIST_PARTITION and REBUILD_IND_PARTITIONS procedures directly instead of through new_part.sql script. For example, if you want to specify the tablespace for the new partition or review the prepared SQL statements:

  1. Run SQL*Plus and connect to the schema.

  2. Run the OM_PART_CONFIG_PKG.INIT procedure. This initializes the OSM tables used for preparing SQL statements.

    begin om_part_config_pkg.init(true);
    end;
    /
    
  3. Run the OM_PART_CONFIG_PKG.ADD_HIST_PARTITION procedure, which prepares the SQL statements allocating new partitions. You can optionally specify the tablespace of the new partition as the input argument. If you do not supply it or the input argument is null, the partition is created on the tablespace of the table:

    begin om_part_config_pkg.add_hist_partition;
    end;
    /
    
  4. Optional: Review the prepared SQL statements:

    select * from om_sql_log order by line;
    
  5. Run the OM_PART_CONFIG_PKG.EXEC procedure, which processes the prepared SQL statements to add new partitions:

    begin om_part_config_pkg.exec;
    end;
    /
    
  6. Run the OM_PART_CONFIG_PKG.REBUILD_IND_PARTITIONS procedure.

    This prepares SQL statements for rebuilding unusable indexes on the affected tables. In general, adding partitions does not render indexes unusable. However, it is safer to run this procedure.

    begin om_part_config_pkg.rebuild_ind_partitions;
    end;
    /
    
  7. Optional: Review the prepared SQL statements.

    If you find no unusable indexes, the following query returns no rows:

    select * from om_sql_log order by line;
    
  8. Run the OM_PART_CONFIG_PKG.EXEC procedure, which processes the prepared SQL statements to rebuild any unusable indexes:

    begin om_part_config_pkg.exec;
    end;
    /
    

Creating Partitions Automatically

When a block of order IDs is exhausted, the next block is allocated. If the corresponding partition does not exist, OSM will attempt to create it automatically. This is useful in development and testing environments. However, it is recommended that you add partitions manually in production environments, especially if you use Oracle RAC.

Adding partitions automatically during heavy processing can lead to order creation failures due to transaction timeouts, especially if your database storage is slow or orders are received over HTTP(S). Note that failed transactions initiated through JMS messages are rolled back and retried automatically when the JMS messages are redelivered.

For performance reasons, adding a new partition also involves a SPLIT PARTITION operation on om_order_flow_coordinator. If the database is an Oracle RAC database, this is needed to eliminate cluster waits. In addition, this improves performance when you drop old partitions because the script can drop entire om_order_flow_coordinator partitions, which is faster than row deletion.

The SPLIT PARTITION operation is performed on the om_order_flow_coordinator table after all ADD PARTITION operations succeed. If the system is busy processing orders, SPLIT PARTITION usually fails due to "resource busy" timeouts.

Note:

The SPLIT PARTITION operation may create a partition with a size limit that is less than zero. The syntax for the name of the negative partition is N_ followed by a string of zeros up to the negative value. The string is always 18 character long.

For example, if the negative value were 100000, then the partition name is N_000000000000100000.

Dropping Partitions with Oracle Scripter

You can drop existing partitions from a database schema if they have no pending orders, and you no longer require the legacy order data they contain.

To drop a partition, on the Windows machine where you installed the Database Utilities OSM Component, open the OSM_Home\Database\osm\maintenance\ dropPartitions\drop_partitions.sql script using Oracle Scripter and update the values of the parameters in Table 8-2.

Table 8-2 Schema Parameters Required by Oracle Scripter

Parameter Description

db admin username

The database administrator's username.

db admin password

The database administrator's password.

db connection string

The database connect identifier. Refer to the Oracle Net Services documentation for the connect identifier syntax. Some examples include:

  • ORCL

  • localhost:1521/osm.us.example.com

ordermgmt owner schema

The core database schema name.

ordermgmt owner password

The core database schema password.

max_order_limit

The maximum order limit.


After you save the updated parameter values, run the script using Oracle SQL*Plus.

The max_order_limit parameter places an upper limit on the set of orders to be deleted. Orders with ORDER_SEQ_ID greater than this limit are not deleted. Consequently, only partitions with an upper boundary less than or equal to max_order_limit + 1 are eligible for removal. A range partition is created with an upper boundary that is strictly less than a given value.

The script runs the drop_partitions stored procedure in the OM_PART_MAINTAIN package. It also handles database jobs, rebuilds any unusable indexes on the affected tables (global indexes become unusable when partitions are removed), and updates schema statistics.

The following issues apply when running the drop_partitions.sql script:

  • Partitions with pending orders are not dropped.

  • Partitions cannot be dropped if the schema contains only a single partition.

  • The script assumes that partition names follow the OSM naming convention for partitions (see the partition_name function in the om_part_config_pkg package).

As an example, consider an OSM database with partitions P_000000000001000001, P_000000000002000001, …, P_000000000009000001, and assume that all partitions but P_000000000001000001 and P_000000000003000001 have pending orders. Running drop_partitions.sql with 3000000 as max_order_limit drops only P_000000000001000001 and P_000000000003000001.