We’re experiencing severe performance degradation after running mass change operations on cost elements in our SAP PLM 2020 system. The mass change updates around 45,000 cost records successfully, but subsequent queries against the costing tables take 10-15x longer than before.
Database statistics show significant index fragmentation on the primary cost element tables. Before the mass change, query response was under 2 seconds. Now the same queries timeout after 30 seconds.
SELECT index_name, fragmentation_pct
FROM dba_indexes
WHERE table_name = 'COST_ELEMENTS'
-- Results show 78% fragmentation
The mass change operation processes records in batches of 5000. We’ve noticed the query optimizer is no longer using the expected indexes. Has anyone dealt with index fragmentation issues after large batch operations? What’s the recommended approach to maintain index health during mass changes?
Check your PCTFREE and PCTUSED settings for the cost element tables. If PCTFREE is too low, updates cause row migration and index fragmentation. We increased PCTFREE to 20 for tables that undergo frequent mass updates. Also verify if you’re using APPEND hint during mass operations - this can help with direct path inserts but doesn’t solve update fragmentation. Your batch size of 5000 might also be suboptimal depending on your buffer cache size.
Adding to the discussion - consider implementing a proactive maintenance schedule. Monitor index fragmentation levels weekly and set thresholds for automatic rebuilds. We use a threshold of 30% fragmentation to trigger rebuilds during off-peak hours.
Have you looked at the actual execution plans before and after? The query optimizer might be avoiding the fragmented indexes because the cost calculation shows table scans are more efficient now. This is actually the optimizer working correctly - it’s just responding to the degraded index state. You need both index rebuild AND statistics refresh. Also consider adding query optimizer hints temporarily while you address the root cause.