No announcement yet.

OTM Purge for Q_INTEGRATION_IN getting sub-optimal

  • Filter
  • Time
  • Show
Clear All
new posts

  • OTM Purge for Q_INTEGRATION_IN getting sub-optimal


    We are running a Database Table purging Stored Procedure call for purging data stored within the OTM table Q_INTEGRATION_IN. We've begun the purge using a logic configuration parameter PURGE_LOG_KEEP set to 550 days.

    PURGE_MAX_BATCH_SIZE is set to 1000 records.

    Currently, we are experiencing the following statistics: Out of total records exceeding 100000 which are qualifying for the purge, a single call to this stored proc is able to delete only around 1000 records, which is way below the expected capability of the purge.

    eg: Out of 118717, only 1207 records could get purged and the purge halts. Again, out of 118141 records, only 932 could get purged before the purge halts, etc.

    Subsequently, after the second or third purge, the purge action halts and no reduction in the number of records is observed.

    Request helpful pointers, hints and solutions as to how we can enable the current set up to perform better with respect to the purge. I also welcome alternate ideas or experiences about how the purge operation has been carried out in your teams.

    Rahul Raje

  • #2
    Re: OTM Purge for Q_INTEGRATION_IN getting sub-optimal

    Hi Rahul,

    Have you tried partition purge? if not give it a shot, we use that and it's working smoothly for us in 6.3.1. The catch is this will only perform meta data purge (which you are trying to perform - at least how I understood your post).

    The documentation is good and to the point in OTM Data management guide.
    Thank You,

    Oracle Certified OTM 6 Implementation Specialist
    If my post was useful please click Thanks!


    • #3
      Re: OTM Purge for Q_INTEGRATION_IN getting sub-optimal

      Thanks for your response. Current customer restrictions may not allow us to proceed with a partition purge. Owing to this, we look forward to optimizing the current purging scheme itself in any manner possible.