Skip Headers
Oracle® Fusion Middleware Administrator's Guide for Oracle SOA Suite and Oracle Business Process Management Suite
11g Release 1 (11.1.1.4.0)

Part Number E10226-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

9 Managing Database Growth

This chapter describes how to manage the growth of data in your database through use of both a SOA composite application instance purge script and component database table partitioning.

This chapter includes the following topics:

Note:

Table partitioning is an advanced database task and must only be performed by an experienced database administrator (DBA).

9.1 Introduction to Managing Database Growth

When the amount of data in the Oracle SOA Suite database grows very large, maintaining the database can become difficult. To address this challenge, two methods for managing database growth are provided:

9.1.1 Deleting Large Numbers of Instances with the Purge Script

Deleting thousands of instances in Oracle Enterprise Manager Fusion Middleware Control takes time and may result in transaction time-outs. As an alternative, you can use the purge script for instance and rejected message deletion. The purge script is located in RCU_HOME/rcu/integration/soainfra/sql/soa_purge.

Because database partitioning is a task for an experienced DBA, the purge script is adequate for most Oracle SOA Suite installations. Consider using table partitioning when the schemas grow so large that the purge script does not meet your performance needs.

For more information about the purge script, see Section 9.3, "Deleting Large Numbers of Instances with the Purge Script."

9.1.2 Partitioning the Component Database Tables

Oracle SOA Suite has been instrumented with partition keys that enable DBAs to take advantage of Oracle RDBMS partitioning features and capabilities. This action enables the schema tables to be range-partitioned on time intervals. This is useful when you must reduce the database maintenance window of large tables. (Though not discussed in this chapter, this also provides for the possibility of archiving partitioned data.)

The task of partitioning the Oracle SOA Suite tables must be performed by an experienced DBA. Since partitioning tables is considered a core DBA skill, this chapter does not provide detailed, step-by-step instructions on how to partition tables. Rather, it provides the DBA with the knowledge and understanding of Oracle SOA Suite schemas and their associated scripts. With this knowledge, the DBA can customize any partitioning strategy for their environment, and incorporate any tuning parameters in response to the performance of their database. Tuning is never a one-size-fits-all proposition or a one-off configuration change. Rather, it is an iterative process of monitoring and tuning.

The partitioning schemes discussed in this chapter can only be used with Oracle SOA Suite 11g Release 1 (11.1.1.4).

The following components are associated with their own database schemas:

  • Oracle BPEL Process Manager

  • Oracle Mediator

  • Human workflow

  • Oracle B2B

  • SOA Infrastructure

  • Oracle BPM Suite

For more information about table partitioning, see the Oracle database administration documentation library located at the following URL:

http://www.oracle.com/technetwork/indexes/documentation/index.html

Notes:

  • A hash subpartition is an option the DBA may want to explore, especially for tables with large object (LOB) segments. This can assist with high water (HW) enqueue contention.

  • A global hash index on primary keys that are monotonically increasing (like CIKEY) may relieve block contention.

9.1.2.1 Referential Integrity and Equipartioning

For performance reasons, the Oracle BPEL Process Manager, Oracle Mediator, human workflow, Oracle B2B, SOA Infrastructure, and Oracle BPM Suite schemas have no foreign key constraints to enforce integrity. This fact discounts the use of the 11g RDBMS feature known as referential partitioning. This feature provides significant benefits because it equipartitions master and detail tables across foreign key constraints. Equipartioning means that the associated dependent table rows are in a database partition with the same partition key interval as their master table rows.

One benefit of this feature is that the state (for example, completed, faulted, and so on) of each detail row in the equipartition can be inferred from its associated master table row.

Although the 11g RDBMS referential partitioning feature cannot be used, similar behavior can be mimicked to achieve some of the same benefits. The Oracle BPEL Process Manager, Oracle Mediator, human workflow, Oracle B2B, SOA Infrastructure, and Oracle BPM Suite components ensure that the partition key of every detail table row is the same as the partition key of its master table row (that is, the date (timestamp) that is the partition key is pushed down). To then complete the setup, the DBA must ensure that the master and detail tables are range-partitioned on the same intervals. Some examples are provided in subsequent sections of this chapter.

Note:

You may decide that referential integrity of aged partitions is not a concern for your site. For example, the site may have ample disk space, allowing data to significantly age, or there may be no apparent, adverse impact of allowing unreferenced data to be stored in the dependent tables.

9.1.2.2 Introduction to Partition Key Selection

The following factors were considered when selecting the schema partition keys:

  • Convey or imply state (for example, completed) for referential integrity

  • Allow range partitioning on time intervals for maintenance operations

  • Be static to avoid row movement that may lead to unreferenced data

  • Be static to avoid row movement when table maintenance operations are performed

  • Provide performance benefits for console queries through partition pruning

9.2 Developing a Purging and Partitioning Methodology

This sections summarizes the main points into an action plan that you can follow if you want to purge and/or partition the dehydration store. Note that purging and partitioning are optional. Oracle SOA Suite does not require it; it is only needed if the data is consuming too much space or you have some other reason for removing the data.

There are three main strategies for reducing the size of the schemas:

In the first two cases, the same purge script is used - although if you are partitioning, you must edit the purge script to comment out your partitioned tables.

The purge script uses standard SQL DELETE statements to remove rows from the BPEL tables. For most sites, this is sufficient. However, some sites accumulate so much data that the purge script takes too long to run. In this case partitioning becomes the better solution. The trade off is that partitioning involves significantly more database maintenance. Moreover, partitioning is an advanced technique and requires a knowledgeable and skilled DBA. By contrast, running the purge script is straightforward and does not require significant DBA knowledge.

Try to profile the input messages, database growth rate, and how much data is purged in the purge process. If the input rate and purge rate match, then regular purging is sufficient. Otherwise, consider partitioning.

If you do use partitioning, Oracle recommends that you add disk space and eventually drop the partition. However, this creates additional requirements for managing disk capacity, deciding on the correct partition size, and so on. Do not use partitioning and then rely on the purge script to reclaim disk space.

Note:

If you only use the purge script in your environment, you can skip the remainder of this section. Only continue with this section if you plan on using partitioning.

9.3 Deleting Large Numbers of Instances with the Purge Script

Deleting thousands of instances with the Delete with Options button on the Instances page of a SOA composite application in Oracle Enterprise Manager Fusion Middleware Control takes time and may result in a transaction timeout. Instead, use the purge scripts for deletion.

The following sections provide examples of how to use the script:

Notes:

  • If you use the purge_soainfra_oracle.sql PL/SQL script provided in releases before 11g Release 1 (11.1.1.4), note that it cannot be used on a Microsoft SQL Server or IBM DB2 database.

  • The purge script provided in pre-11.1.1.4 releases has been deprecated. If you are an existing user of this script, you can continue to use it against your database in 11g Release 1 (11.1.1.4). However, this script is no longer shipped with 11g Release 1 (11.1.1.4). Oracle recommends that you use the new purge script provided with 11g Release 1 (11.1.1.4).

  • When upgrading from 11g Release 1 (11.1.1.3) to 11g Release 1 (11.1.1.4), ensure that you run the purge setup scripts from the new 11.1.1.4 RCU location, as this contains the latest purge details. For more information about upgrade, see Oracle Fusion Middleware Upgrade Guide for Oracle SOA Suite, WebCenter, and ADF.

9.3.1 Looped Purge Script

The master purge script includes a looping construct that allows for a batched purge. You can also provide this script with a max_runtime parameter that stops looping after the value for this parameter is exceeded.

The master script drives the purge of SOA database tables. You can use the delete_instances procedure to purge SOA database tables.

Note:

Set max_runtime to a higher value if there are many instances to purge. In this case, you should expect to wait for a longer time before the script exits. Alternatively, use a smaller batch size if you want the purge script to exit sooner.

9.3.1.1 delete_instances Procedure

Use the delete_instances procedure to delete instances. Example 9-1 shows the syntax.

Example 9-1 delete_instances Procedure Syntax

procedure delete_instances (
                   min_creation_date in timestamp,
                   max_creation_date in timestamp,
                   batch_size in integer,
                   max_runtime in integer,
                   retention_period in timestamp,
                   purge_partitioned_component in boolean
                   );

Table 9-1 describes the script parameters.

Table 9-1 delete_instances Procedure Parameter Descriptions

Parameter Description

min_creation_date

Beginning creation date for the composite instances.

max_creation_date

Ending creation date for the composite instances.

batch_size

Batch size used to loop the purge. The default value is 20000.

max_runtime

Expiration at which the purge script exits the loop. The default value is 60. This value is specified in minutes.

retention_period

Retention period is only used by the BPEL process service engine (in addition to using the creation time parameter).

This parameter checks for and deletes records in the cube_instance table. The value for this parameter must be greater then or equal to MAX_CREATION_DATE. The default value is null.

purge_partitioned_component

Users can invoke the same purge to delete partitioned data. The default value is false.


Note:

If you do not provide a value for retention_period, the value for this property defaults to the value of max_creation_date (this is, if retention_period equals null, then retention_period = max_creation_date). This consequence also applies to the script parameters described in Section 9.3.2, "Looped Purge in Parallel Script with dbms_scheduler."

9.3.2 Looped Purge in Parallel Script with dbms_scheduler

This script is functionally the same as the looped purge script described in Section 9.3.1, "Looped Purge Script." However, this script uses the dbms_scheduler package to spawn multiple purge jobs, with each job working on subset data.

You can use the following procedure to purge SOA database tables.

Note:

If you have a multiple CPU host, use of the parallel script can be beneficial. However, Oracle recommends that you enable the parallel script only during off hours. In addition, when purging data during off hours, Oracle recommends that you drop indexes before purging large amounts of data and then add the indexes back in. This speeds up the purge process, and also keeps indexes from becoming unbalanced.

9.3.2.1 delete_instances Procedure in Parallel

Use the delete_instances procedure in parallel to delete instances. Example 9-2 shows the syntax.

Example 9-2 delete_instances Procedure in Parallel Syntax

PROCEDURE delete_instances_in_parallel (
                   min_creation_date in timestamp,
                   max_creation_date in timestamp,
                   batch_size in integer,
                   max_runtime in integer,
                   retention_period in integer,
                   DOP in integer
                   max_count integer,
                   purge_partitioned_component in boolean)

Table 9-2 describes the script parameters.

Table 9-2 delete_instances in Parallel Procedure Parameter Descriptions

Parameter Description

min_creation_date

Beginning creation date for the composite instances.

max_creation_date

Ending creation date for the composite instances.

batch_size

Batch size used to loop the purge. The default value is 20000.

max_runtime

Expiration time at which the purge script exits the loop. The default value is 60. This value is specified in minutes.

retention_period

Retention period is only used by the BPEL process service engine only (in addition to using the creation time parameter). The default value is null.

DOP

Defines the number of parallel jobs to schedule. The default value is 4.

max_count

Defines the number of rows processed (not the number of rows deleted). A big temp table is created and then jobs are scheduled to purge based on the data. This is the maximum purge row count to use; it defaults to one million. The default value is 1000000.

purge_partitioned_component

Users can invoke the same purge to delete partitioned data. The default value is false.


9.3.3 Executing the Purge Scripts

This section describes how to execute the purge scripts.

To execute the purge scripts:

  1. In SQL*Plus, connect to the database AS SYSDBA:

    CONNECT SYS AS SYSDBA
    
  2. Execute the following SQL commands:

    GRANT EXECUTE ON DBMS_LOCK to USER;
    GRANT CREATE ANY JOB TO USER;
    

    where USER is the soainfra account to execute the scripts. These privileges are required to run the scripts.

  3. Load the purge scripts by executing the main purge script in RCU_HOME/rcu/integration/soainfra/sql/soa_purge.

    For a parallel purge, the debug logs from the jobs spawned by a parallel purge are logged into files created in the directory named SOA_PURGE_DIR. This directory must be accessible to the Oracle database.

  4. Create SOA_PURGE_DIR and grant write permissions to the soainfra user.

    mkdir -p /tmp/purgelog
    CREATE OR REPLACE DIRECTORY SOA_PURGE_DIR AS 'SERVER_DIRECTORY'
    

    where SERVER_DIRECTORY is the name of the directory to create (for example, '/tmp/purgelog/'). Note the required single quotes around the directory path.

  5. If you want to execute the scripts in debug mode, run common/debug_on.sql and set serverout to on in SQL*Plus. This step is optional.

    SET SERVEROUTPUT ON
    

    The logs from the spawned jobs are logged into the directory created in Step 4 (separate files per job). The rest of the logs are displayed on stdout (or the spool file, if configured).

    There are two options for purging:

    • Looped purge

    • Parallel purge

  6. Execute the purge scripts as shown below. Examples are provided for both options.

    1. For looped purge:

      DECLARE
      
         MAX_CREATION_DATE timestamp;
         MIN_CREATION_DATE timestamp;
         batch_size integer;
         max_runtime integer;
         retention_period timestamp;
      
        BEGIN
      
         MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
         MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
          max_runtime := 60;
          retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
         batch_size := 10000;
           soa.delete_instances(
           min_creation_date => MIN_CREATION_DATE,
           max_creation_date => MAX_CREATION_DATE,
           batch_size => batch_size,
           max_runtime => max_runtime,
           retention_period => retention_period,
           purge_partitioned_component => false);
        END;
        /
      
    2. For parallel purge:

      DECLARE
      
         max_creation_date timestamp;
         min_creation_date timestamp;
         retention_period timestamp;
        BEGIN
      
         min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
         max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
         retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
      
          soa.delete_instances_in_parallel(
           min_creation_date => min_creation_date,
           max_creation_date => max_creation_date,
           batch_size => 10000,
           max_runtime => 60,
           retention_period => retention_period,
           DOP => 3,
           max_count => 1000000,
           purge_partitioned_component => false);
      
       END;
      

9.4 Partitioning Component Tables

The runtime and schema code for the following components has been modified to store the flow creation date column with their transactional tables.

The CPST_CREATED_DATE column contains the flow creation date time populated by the instance tracking code. This is available as the normalized message property oracle.integration.platform.instance.CommonConstants.COMPOSITE_INSTANCE_CREATED_TIME.

All SOA components are partitioned on the same partition key. These partitioned components use the same time range and partition ID.

9.4.1 Introduction to the Verification Scripts

A verification script is provided for a DBA to determine when to drop a partition and its equipartitioned dependent table.

Note:

The verification script does not drop any partitions; it just ensures that partitions are eligible to be dropped.

9.4.2 Component Tables

This section lists the component tables, the groups to which they belong, and their partition key.

Note the following table partitioning constraints:

  • You have the choice of partitioning only at the granularity of the components. All the tables from each component must be partitioned or none of them should be partitioned. For example, you can partition the Oracle BPEL Process Manager tables and leave the other components unpartitioned. In such a case, however, all the tables related to the BPEL component must be partitioned.

  • Regardless of the group and component, all tables that are partitioned use the same time range and the partition ID.

Table 9-3 through Table 9-8 are divided into three groups.

  • Group 1: This includes tables that are directly related to the end-to-end flow trace of a composite. A majority of the tables fall into this group.

  • Group 1A: This includes a small set of tables that are not directly related to the flow trace (for example, REJECTED_MESSAGE).

  • Group 2: This includes a small of set of tables that have a dependency on multiple tables from Group 1 and 1A tables. You must first execute the group 1 verification script and drop the group 1 partitions before executing the group 2 verification script.

    Note:

    Groups 1 and 1A are combined in the verification scripts. Executing the verification scripts does not require you to have knowledge of this classification.

Table 9-3 Component: SOA Infrastructure

Table Range Partition Key Group

COMPOSITE_INSTANCE

PARTITION_DATE

1

REFERENCE_INSTANCE

CPST_PARTITION_DATE

1

COMPOSITE_INSTANCE_FAULT

CPST_PARTITION_DATE

1

COMPOSITE_SENSOR_VALUE

CPST_PARTITION_DATE

1

COMPONENT_INSTANCE

CPST_PARTITION_DATE

1

REJECTED_MESSAGE

CREATED_TIME

1A

REJECTED_MSG_NATIVE_PAYLOAD

RM_PARTITION_DATE

1A

INSTANCE_PAYLOAD

CREATED_TIME

2

COMPOSITE_INSTANCE_ASSOC

CREATED_TIME

2


Table 9-4 Component: Oracle BPEL Process Manager

Table Range Partition Key Group

CUBE_INSTANCE

CPST_INST_CREATED_TIME

1

CI_INDEXES

CI_PARTITION_DATE

1

CUBE_SCOPE

CI_PARTITION_DATE

1

DOCUMENT_CI_REF

CI_PARTITION_DATE

1

AUDIT_TRAIL

CI_PARTITION_DATE

1

AUDIT_DETAILS

CI_PARTITION_DATE

1

DLV_SUBSCRIPTION

CI_PARTITION_DATE

1

WORK_ITEM

CI_PARTITION_DATE

1

AUDIT_COUNTER

CI_PARTITION_DATE

1

WI_FAULT

CI_PARTITION_DATE

1

DLV_MESSAGE

RECEIVE_DATE

1A

HEADERS_PROPERTIES

DLV_PARTITION_DATE

1A

DOCUMENT_DLV_MSG_REF

DLV_PARTITION_DATE

1A

XML_DOCUMENT

DOC_PARTITION_DATE

2


Table 9-5 Component: Oracle Mediator

Table Name Range Partition Key Group

MEDIATOR_INSTANCE

COMPOSITE_CREATION_DATE

1

MEDIATOR_CASE_INSTANCE

MI_PARTITION_DATE

1

MEDIATOR_CASE_DETAIL

MI_PARTITION_DATE

1

MEDIATOR_AUDIT_DOCUMENT

MI_PARTITION_DATE

1

MEDIATOR_DEFERRED_MESSAGE

CREATION_TIME

1A

MEDIATOR_PAYLOAD

CREATION_TIME

2


Table 9-6 Component: Human Workflow

Table Range Partition Key Group

WFTASK

COMPOSITECREATEDTIME

1

WFTask_TL

COMPOSITECREATEDTIME

1

WFTaskHistory

COMPOSITECREATEDTIME

1

WFTaskHistory_TL

COMPOSITECREATEDTIME

1

WFComments

COMPOSITECREATEDTIME

1

WFMessageAttribute

COMPOSITECREATEDTIME

1

WFAttachment

COMPOSITECREATEDTIME

1

WFAssignee

COMPOSITECREATEDTIME

1

WFReviewer

COMPOSITECREATEDTIME

1

WFCollectionTarget

COMPOSITECREATEDTIME

1

WFRoutingSlip

COMPOSITECREATEDTIME

1

WFNotification

COMPOSITECREATEDTIME

1

WFTaskTimer

COMPOSITECREATEDTIME

1

WFTaskError

COMPOSITECREATEDTIME

1

WFHeaderProps

COMPOSITECREATEDTIME

1

WFEvidence

COMPOSITECREATEDTIME

1

WFTaskAssignmentStatistic

COMPOSITECREATEDTIME

1

WFTaskAggregation

COMPOSITECREATEDTIME

1


Table 9-7 Component: Oracle B2B

Table Range Partition Key Group

B2B_BUSINESS_MESSAGE

CPST_INST_CREATED_TIME

1

B2B_APP_MESSAGE

CPST_INST_CREATED_TIME

1

B2B_WIRE_MESSAGE

CPST_INST_CREATED_TIME

1

B2B_DATA_STORAGE

CPST_INST_CREATED_TIME

1

B2B_EXT_BUSINESS_MESSAGE

CPST_INST_CREATED_TIME

1


Table 9-8 Component: Oracle BPM Suite

Table Range Partition Key Group

BPM_AUDIT_QUERY

CI_PARTITION_DATE

1

BPM_MEASUREMENT_ACTIONS

CI_PARTITION_DATE

1

BPM_MEASUREMENT_ACTION_EXCEPS

CI_PARTITION_DATE

1

BPM_CUBE_AUDITINSTANCE

CI_PARTITION_DATE

1

BPM_CUBE_TASKPERFORMANCE

CI_PARTITION_DATE

1

BPM_CUBE_PROCESSPERFORMANCE

CI_PARTITION_DATE

1


9.4.3 Executing the Verification Scripts

A verification script is provided for the DBA to determine when to drop a partition and its equipartitioned dependent table. The verification script is located in RCU_HOME/rcu/integration/soainfra/sql/verify.

To execute the verification scripts:

  1. Create a directory with the SQL command PART_DIR. For example:

    CREATE DIRECTORY PART_DIR AS '/tmp/verify'
    
  2. Provide the soainfra user with write privileges on this directory. The log and SQL files are generated in this directory.

  3. For executing the stored procedure, the client script soa_exec_verify.sql can be used. Edit soa_exec_verify.sql and enter the partition names that require verification in the array mySoa_drv_list.

    1. To execute function verify_soa.verify_1, pass 1 as the parameter.

    2. To execute function verify_soa_verify_2, pass 2 as the parameter.

  4. Review the logs and SQL files generated in the PART_DIR directory.

Note:

Verification scripts are not provided for business rules.

9.4.4 Verifying and Dropping Partitions

To verify and drop partitions:

  1. Execute function verify_soa.verify_1.

  2. Check the log file in the PART_DIR folder with the name SOA_PARTITION_NAME_LOG_1 for any failures.

  3. Drop the partitions that can be dropped by using the script generated in the PART_DIR folder with the name SOA_PARTITION_NAME_RESULT_1.sql.

  4. Execute verify_soa.verify_2.

  5. Check the log file in the PART_DIR folder with the name SOA_PARTITION_NAME_LOG_2 for any failures.

  6. Drop the droppable partitions using the script generated in the PART_DIR folder with the name SOA_PARTITION_NAME_RESULT_2.sql.

Note:

There is an issue caused by the existence of foreign key constraints in the Oracle B2B table. When dropping the partition, the B2B partition purge is invoked, the foreign key constraints must be disabled before dropping the partition and enabled afterward. To perform this action, execute the PL/SQL procedures b2b_disable_constraints and b2b_enable_constraints at the appropriate steps in the above procedure. Because foreign keys are enabled and disabled in these procedures, it is not recommended to run them on a live system.

9.4.5 Partial Partitioning of Components

If you have an environment in which some components are partitioned, while other components are not partitioned, the nonpartitioned data set must be purged using the purge scripts described in Section 9.3, "Deleting Large Numbers of Instances with the Purge Script."

For example, assume human workflow is not partitioned, while other components are partitioned. The verification script reports that all SOA partitions can be dropped. However, the human workflow tables continue to hold workflow data until it is purged using the purge scripts.