Background: The IM object12_5 table is observed to experience table fragmentation during large number of transactions, due to inserts with deletes.
Oracle recommends that partitioning be utilized to allow an auto-rebuild of tables that have this scenario.
A POC process was executed manually by DBA to address the table fragmentation to increase performance.
a. Create a copy of Object12_5 table
b. Recreate the Object12_5 and import data for a fresh build
- Immediate performance benefit for I/O for the Oracle DB.
- However, over time (about a month of heavy use-case testing) the table has experienced the same challenge due to fragmentation.
Next Steps: Desire to automate this process through partitioning process.
This partitioning process would require a time-date stamp to be added to the select tables to allow use of automated partitioning; after N days, the partition would be replaced with a clean version of the partition of data.
- Perform a non-manual process to add in time-date stamps to the object12_5 and related tables.
- Enable automated partitioning for these tables.
- Scheduled rebuild on a weekly basis.
Request from CA Engineering:
- Validate process for future versions and current release of IM r14.1
Side Note: (PRODUCTION ENV IMPACT)
Oracle has confirmed a bug in version 184.108.40.206 for enqueue TX / Buffer busy waits on LOB objects (fixed in 18.1).
Bug 26439748 : ENQ: TX - CONTENTION AND GC BUFFER BUSY ACQUIRE WAITS ON SECUREFILES IN 12.1
Severity 1 - Complete Loss of Service
"Attempted to copy of object12_5 using ‘create table as select’ in Stage (300GB) – but it always fails with 1555 snapshot too old errors – this error also happens when using data pump export.
There’s too much activity on the table and the IM app would need to be down in order rebuild with the existing data. "