Oracle ETPM Partitioning Recommendation

ETPM Partitioning Strategy Recommendations

For transactional tables using Random, Generated PK_IDs

ETPM Batch processing capacity can be grown by increasing the degree of parallelism – employing a higher number of threads that process the records. For throughput to follow the growth in the number of threads in a near linear pattern, thread contention for data has to be contained. Partitioning of ETPM transactional tables is a mechanism for minimizing thread contention when running jobs with a high level of concurrency (i.e. many batch threads).

This document describes a partitioning strategy to reduce database concurrency issues such as (hot blocks) which inhibit scalability. These hot blocks are reported by Oracle as ‘buffer busy waits’, they are bad for scalability because only one thread (oracle process) at a time can access a block and other threads competing for the same block must wait until the previous thread is finished. As more threads compete for the same blocks the total wait time gets longer.

Objective of ETPM partitioning strategy is to minimize data contention between the threads by having each thread process a subset of records with primary key values in a range that do not overlap with the ranges of keys values assigned to the other threads. E.g., in an extreme case there would be a 1:1 relationship between the number of threads and number of partitions

In order to better understand these recommendations it is necessary to understand ETPM Primary Key (PK) inheritance and the method used by the Batch infrastructure to distribute work to the threadpoolworker (TPW) threads.

Primary Key Inheritance.

Many ETPM tables with a parent-child relationship are designed so that the child tables partially inherit their own ID (primary key) from the parent table. For example the following tables: CI_FT, CI_FT_GL, CI_SA  all inherit a portion of their own ID from CI_ACCT. When a new record is inserted into the parent table, new IDs are generated randomly across the entire range of possible keys. When a new record is inserted into one of the child tables the new ID consists of a substring of the parent ID (for example the first 6 characters of the ACCT_ID) the remainder of the new ID is generated randomly. This means that the IDs of the parent and child tables will always share the most significant (first) characters of the ID string. This is important when we come to partitioning the data.

Distribution of records among Batch threads

When a batch job is started, the work is distributed among the threads using the driving column of the job. For most jobs this is the PK of the driving table. For example by default C1-TXMTD which processes Tax Forms distributes work based on the tax_form_id. Each thread is given a contiguous subset of the full range of possible IDs. For example if the ID is CHAR(10) and the job is run with 10 threads the records would be allocated to the threads as follows:


Low ID

High ID










































Since IDs are generated randomly, then with an effective random number generator, the number of records per thread (partition) will be similar. You can check this when batch jobs are run, each thread should process about the same number of records. Each thread will process the records it has been allocated sequentially within its predetermined range, thus no two threads will be processing records with ‘adjacent’ IDs. This does not help much if the table is a normal heap organized table because the records may be in any block, but if we partition the table we are organizing the data.


Let’s consider a simple example where we partition the table by PK_ID using the same number of partitions as threads. We will also create a LOCAL index on the PK (each partition will have an index which only refers to records within the same partition). It should be obvious that each job thread will only ever access data from a single partition.

Now let’s consider the child tables, remember they have inherited the ‘most significant digits’ of their PK from their parent table so the corresponding child records will be distributed in the same way as the parent table. Therefore a record in the Parent table processed by thread #4 will be located in partition #4 and the corresponding child table records will also be in partition #4. This is the reason child tables use key inheritance.

Since data from different partitions cannot be located in the same physical DB block, this virtually eliminates job threads accessing data from the same block, reducing buffer busy waits and improving scalability.

This strategy is only effective for generated keys where numbers are spread randomly and evenly across the range of possible ids. It does not work for tables with sequential keys (e.g.  Forms Upload Staging)

Secondary indexes

LOCAL indexes are only effective if the partition that a record is located in is known to the query optimizer. If the partition cannot be determined then Oracle will not be able to use partition pruning and will be required to search all partitions which will increase the number of logical reads significantly. In general this means that if the ID is not a part of the index then the index should be GLOBAL. One exception to this rule is a low cardinality (high selectivity) index, however if you have any of these you should consider changing or dropping them.

Oracle RAC.

So far we have considered partitioning and Oracle concurrency in the context of a single RAC instance. If your implementation uses Oracle Real Application Clusters (RAC) then you may see Global Cache Waits (GC * Waits). There are several different GC wait events but what they are telling you is that the data requested in one RAC instance has also been requested by a thread running on a different instance. Before the thread on the second instance can see the data the first instance must release it and transfer it to the second instance, this may also require the block to be written to disk adding further delay. Consequently GC * waits can kill application scalability, however there is an effective strategy to reduce them for ETPM: Configure the TPWs to talk to a specific RAC instance (i.e. do not use load balancing). For example configure half the TPWs to talk to RAC instance#1 and the others to RAC instance #2. This will align the threads to a single instance and since each thread is aligned to a single partition the instance will also be aligned to the partition. This strategy is very effective at reducing GC contention on partitioned tables and indexes but it does not address GC * waits on global indexes.

Partitioning and OLTP

The partitioning strategy outlined above make use of the design of the Batch Framework which makes data access by the batch threads predictable. Since OLTP requests are random the considerations above do not apply to OLTP. However this does not mean partitioning is not beneficial, however increasing the number of partitions for OLTP will have a diminishing return. For example 2 partitions will (on average) reduce contention by 50%, 4 partitions will reduce contention to 25% and 10 partitions to 10%.

Partitioning can also increase the efficiency of SQL query (for example by partition pruning) this works equally for both batch and OLTP queries, for further information refer to the Oracle Documentation on Partitioning and SQL performance tuning.

Partitioning and Clustering Guidelines

Partition the transactional (high volume tables) tables. For example:

CI_ACCT, CI_FT, CI_FT_GL, CI_SA     basically all tables which inherit the PK from account_id


Any child tables (log/parm/log_parm) belonging to any of the above.

Make the PK index LOCAL

In general secondary indexes will normally be global

Partition by RANGE on the PK_ID column.   (e.g. acc_id, sa_id etc).

The number of partitions is driven by the number of job threads, ideally they should be an exact multiple or divisor.

One effective way to choose the number of partitions is to determine how many threads can be run by a single physical batch server and use this. For example if the server can handle up to 20 threads then use 20 partitions. This works because you will generally increase capacity one server at a time. If you anticipate a different mode of incremental growth then consider this when partitioning. Be careful to choose a number of partitions which is flexible when you want to change the number of job threads. Avoid prime numbers, even numbers are generally more flexible than odd numbers, For example 20 partitions has many more multiples and divisors than 19.

A partition can be regarded as equivalent to a single table and will have a similar upper limit to the number of threads that it can support. This number may vary by implementation or job. Internal testing we found that up to 8 threads per partition the concurrency waits were not causing any significant concurrency issues. So if you are using more than 8 threads and are not partitioned or are using more than 8 threads per partition then you may want to monitor how DB concurrency is impacting performance. You can do this through the Oracle AWRreport.


This information is also uploaded here: 

A special thank you to Graeme Robinson and Ilya Klebaner for developing this content!!!


Post a Comment:
  • HTML Syntax: NOT allowed

This blog is for our Oracle Public Sector Revenue Management customers, delivery partners, and even our fellow Oracle professionals, and is dedicated to all things Oracle Tax and Revenue Management. We will focus on support and implementation tips, announcements, and FAQs (frequently asked questions) for all of our Oracle Public Sector Revenue Management products. This include Oracle Public Sector Revenue Management (PSRM), Oracle Tax Analytics, and our Oracle PSRM Self Service and integration products.


« August 2016