9 Managing Database Growth

9.1.1 Deleting Large Numbers of Instances with the Purge Script

Deleting thousands of instances in Oracle Enterprise Manager Fusion Middleware Control Console 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/technology/documentation/database.html

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.

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
                   );

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.

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)

9.3.3 Executing the Purge Scripts

This section describes how to execute the purge scripts.8

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.1 Understanding the Verification Scripts

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

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.

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.

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.

9.4.4 Verifying and Dropping 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.

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.