Dehydration Store Database Growth Management Challenges and Purging Strategies

Dehydration Store Database Growth Management Challenges 

SOA tables may grow excessively due to an ineffective management strategy.  When an ineffective purging strategy has been implemented the SOA suite tables may grow very large leading to an urgent need to reclaim space. The larger the tables the harder it will be to delete rows and reclaim space.

This situation highlights the need to constantly monitor the effectiveness of the growth management strategy and to take corrective actions as soon as possible before the tables become very large.

 Recommended Strategies

The growth management strategies recommended is based on the SOA database profile.

The strategies require testing in order to determine their effectiveness. The testing needs to ensure that the purge scripts have headroom grow; that partitioned tables have sufficient disk space; that growth is monitored and that maintenance is performed with minimal impact to OLTP performance.

Recommendations for Large Database profiles

Installation with large database profile must seriously consider table partitioning as part of their strategy. Table range partitioning is a proven method for managing large tables:

1.       Execute the parallel purge excluding the tables that will be partitioned

2.       Partition the bottleneck tables to the purge script

3.       Reclaim space as part of maintenance cycle

4.       Drop eligible partitions as part of the maintenance cycle

5.       Monitor the inflow and outflow

6.       Monitor database and hardware resources

Recommendations for Medium Database profiles

The installations with a medium database profiles have the potential to become large thus the recommendations are the same as that for large profiles. It is important that medium installation perform rigorous QA testing and monitoring, as table partitioning post go-live might require downtime.

The only additional recommendation is that the CTAS (Create Table as Select) scripts when available may be an alternative to purging. This depends on the size of the database being truly medium and the availability of downtime during the maintenance windows.

Recommendations for Small Database profiles

Small installation with limited CPU, memory and disk space should start with the single threaded purge and then move to the parallel purge:

1.       Execute the single threaded purge

2.       Execute the parallel threaded purge. If the single threaded purge is not performing and then the parallel purge should be tested.

3.       Monitor the inflow and outflow

4.       Monitor database and hardware resources

For more information please refer below whitepaper published by Oracle.

References: http://www.oracle.com/technetwork/database/features/availability/soa11gstrategy-1508335.pdf