Solution : https://service.sap.com/sap/support/notes/787533 (SAP Service marketplace login required)
Summary :
High enqueue waits post-reorganization or adjustments to extent sizes can freeze performance by occupying all work processes, displaying enqueue waits in Oracle sessions. Causes include held enqueues on same tables, visible in transaction (TX) locks. Commonly, operations causing these issues are updates or index rebuilds, where stopping these can release the locks. Analyzing the allocation of extents that aren't reused post-reorganization, situations where the space management activity leads to “ST” lock hangs, especially when freeing up extents not promptly reused. Troubleshooting includes verifying the freespaces. Permanent solution involves reorganizing tablespaces into locally managed schemas, while temporary adjustments can manage space better to avoid relocks.
Key words :
deletedmlspace managementcoalesce free spacealter tablespace coalescereorganizationnext extent size reason, st04 -> detailed analysis menu -> oracle sessions match, command sql> truncate table <tablename> reuse storage, st04 -> detail analysis menu -> oracle sessions, commandsql> alter table <tablename> deallocate unused, sql> alter table <tablename> deallocate unused, st04 --> detailed analysis session -> oracle sessions, sql> alter table <tablename> allocate extent, oracle sessions show enqueue waitsthere, sqlsql> select' alter index
Related Notes :
| 745639 | FAQ: Oracle enqueues |
| 618868 | FAQ: Oracle performance |
| 541538 | FAQ: Reorganization |
| 214995 | Oracle locally managed tablespaces in the SAP environment |