I_TRANSMISSION cleanup

[brussp]

We are currently on OTM 5.5 (CU4 RU2). We perform a fair amount of integration into OTM via SOA (20K transmissions = ~ 50K transactions). This results is a fair amount of database space being consumed by the I_TRANSMISSION table, due to the staged XML stored in CLOB columns. Due to this, we do run the OTM-provided I_TRANSMISSION purge process on a nightly basis (we keep 14 days). Immediately after this purge we run a reorg job to reclaim the space from the purged CLOB data. This reorg runs an extemely long time (3 hours or more). Much of this has to do with how the I_TRANSMISSION table is partitioned out-of-the-box. Various folks at Oracle said that there are many OTM customers that have more integration volume, keep more days of I_TRANSMISSION data, but have no problems to speak of.

So.......I am curious to find out what 'best practices' other OTM customers have instituted to make this process less painful. Any feedback would be greatly appreciated!

[MURTHYKO]

Re: I_TRANSMISSION cleanup

Hi All,

Does any one explain how can we purge "I_TRANSMISSION" & "I_TRANSACTION" data in OTM without any customization?

[sabisidhu]

Re: I_TRANSMISSION cleanup

First thing, you will surely need enough space allocated to LOB tablespaces to keep your data. In order to stop the growth of these tablespaces, you can perform following...

1. Use OTM purge procedures to delete data from transmission and transaction tables.
- you can use domainman.transmission_purge procedure for the same. If you run this daily you should not see the transmission table growing (at least we are not!)

2. Enable OTM DB partition purge jobs. Most OTM partitioned tables are on Monthly cycle (except outbound transactions/transmission - this is weekly). Once you enable partition purge you will reclaim space every week/month on LOB tablespaces.
- you can do this by executing partit.submit_job() procedure.

You can also try using following property to disable XML storage at transmission level. (This is default in OTM 6.1.)
glog.integration.transmission.suppressPersistFullX ml=true

[MURTHYKO]

Re: I_TRANSMISSION cleanup

Thanks Gurpreet. From where (directory path on server) can I get these procedures?

Are there any mandatory arguments to pass to execute domainman.transmission_purge?

Will this procedure delete or archive the transmissions? Will it internally delete transactions also?

[sabisidhu]

Re: I_TRANSMISSION cleanup

These procedures are part of OTM DB. You can find more information in OTM 5.5 Admin Guide (Section 8-23/24/25).

Yes, my understanding is that this procedure will delete corresponding transactions also.

Here is disclaimer from Oracle:

Note

: Transmission_purge is an expensive operation. It should be run during off-peak

hours.

[chrisplough]

Re: I_TRANSMISSION cleanup

I definitely agree with the statement that purging is expensive - I wouldn't run it during operational hours.

--Chris

[stoganv]

Re: I_TRANSMISSION cleanup

This is what we've done:

- Change the partition scheme to be weekly
- Above requires changes to triggers
- Develop custom purge routine that runs Sunday night and truncates the next partition
- Therefore we only store 3-4 weeks of i_trans data
- It consumes about 100GB and gets reused in separate tablespace
- No need for reorg as the truncated blocks get reused

Things that I wanted but couldn't get passed the power brokers:

- Oracle Advanced Compression
- Different size tablespace - 16K, 32K?

[josephliang]

Re: I_TRANSMISSION cleanup

Things that I wanted but couldn't get passed the power brokers:

- Oracle Advanced Compression

As mentioned by Oracle during OTM SIG Conference 2010 http://www.otmfaq.com/forums/f31/otm...html#post10011, OMT 6.x has been certificated for certain 11gR2 DB Features, including Advanced Compression.

- Different size tablespace - 16K, 32K?

As each CLOB record uses integer number of chunks, the larger block size may improve R/W performance when you always have large size of XML, but may waste more db space.

[josephliang]

Re: I_TRANSMISSION cleanup

Additional information:
Metalink Notes
Note 882072.1 - Maintaining Data Window for OTM Applications
Note 882074.1 - OTM Database Defragmentation Using Move Lobs Method

Table Reorganization and MoveLobs timing
OTM manages partitions for inbound and outbound transmission logs separately and rotates partitions by monthly for inbound and by weekly for outbound. LOBs.jpg illustrates how OTM stores CLOB data across LOB1~4 tablespaces. Therefore, if you perform reorg and MoveLobs at least once a month, you could have minimal one LOB tablespace with empty LOB Partition for inbound.

Apply OUT_XML_PROFILE to reduce outbound XML size
OUT_XML_PROFILES are critical for OTM performance as well as space consumption of outbound Transmission logs. All outbound transmissions need fine-tuning of OUT_XML_PROFILEs to include only minimal elements.

LOB CHUNK size
As CHUNK is the smallest unit of LOBSEGMENT allocation, any non-null CLOB will is occupied at least one CHUNK of space. Due to OTM defines LOB CHUNK as 16K, any transmissions that are less than 16K still consume 16K space each or a 17K transmission uses 32K (16K x2) space. Therefore, there are many wasted spaces inside LOBs tablespaces.

[Prasad Chandane]

Re: I_TRANSMISSION cleanup

Manually Purging Transmissions from the Database::
Manually purging is an alternative to truncating the partitioned tables. Learn more about the scheduled jobs that truncate partitioned tables later in the chapter. Truncating data is much faster than purging, but purging allows you to more exactly specify what data to purge.

Transmission_purge deletes data associated with xml blobs used for integrating Oracle Transportation Management with external systems. In general, the xml blobs are temporary, so there is no downside the getting rid of them.

DBA or System Administrator should set up a nightly or weekly background job to run purges as described below.

Manually Purge Transmissions
To purge the transmission tables:
1. Log into sql*plus as glogowner. (glogowner/{password}@{tnsname})
2. SQL> exec domainman.transmission_purge(<age_in days>, <total_minutes>, <batch size>);

Note: The old procedure transmission purge
SQL> domainman.transmission_purge(<age_in days>, <where clause>,<totalminutes>,<keep status clob flag>, <batch size); is deprecated (still workable).
Tender collaboration records (TENDER_COLLABORATION), which have their own partition, are not able to be purged or truncated with this procedure. They can be purged with associated shipments if the parameter PURGE_TENDER_TRANSACTION=TRUE or manually purged with sql command
SQL> exec domainman.orphan_tender_purge(<total_minutes>,<bat ch size>);”
Transmissions with shipment events (IE_SHIPMENT_STATUS) are purged just like other elements.

The transmission purge process accepts the following arguments:

Age in days (required): the age of the transmissions that you want to purge.
If you have no idea of how many transmissions you have that are older than 30 days (for example), you can do the following query:
SQL> select count(*) from i_transmission where sysdate-create_date > 30;

Total Minutes (defaults to 60): The maximum number of minutes you would like the total purge process to take. After processing a set of rows, the procedure will check the total time. If it exceeds the inputted time, the process will wrap up the existing task, and stop. This is helpful if you have a tight time schedule in which you can perform this task.

Batch size (default 5000): Limits the number of transmission records that should be processed at a time. The smaller your rollback segment, the lower this number should be set. If you get a rollback segment error, rerun the process with a lower batch size specified.

 
The transmission purge process deletes records from the following tables:
I_TRANSMISSION
I_TRANSMISSION_ACK
I_TRANSMISSION_PGROUP
I_TRANSMISSION_REFNUM
I_TRANSMISSION_REPORT
I_TRANSACTION
I_TRANSACTION_ACK
I_TRANSACTION_DETAIL
I_TRANSACTION_REFNUM
I_LOG
I_LOG_DETAIL

Examples:

exec domainman.transmission_purge(90,30,1000);

Deletes all transmission records (for above tables) that are older than 90 days except tender. The process would run for 30 minutes and would process 1000 records at a time.
Note: Transmission_purge is an expensive operation. It should be run during off-peak hours.

----------------------------------------------------------------------------------

After that, to release space from db we need to use below commands which is just an example
e.g.
ALTER TABLE I_TRANSMISSION ENABLE ROW MOVEMENT;
ALTER TABLE I_TRANSMISSION MODIFY LOB (XML_BLOB) (SHRINK SPACE);

After that we have to rebuild indexes to avoid performance issue


Please let me know, is there anything more required to add while doing this activity

[sirish]

Thanks Prasad for the details explanation. If want to purge TenderOffer elements as well from I_transaction than what is the way? should we modify the original procedure or there is any other way available?