OTM (Oracle Transportation Management) Object locks have been my nemesis lately. You can see my past battles with refactoring and code quality. In migration or upgrades, direct copy of old packages from old to new domain often do not work as expected. Of course, you will only realise it when you hit object locks.
Signs of Object Locks
- If you see very long PLSQL packages, it will mean the codes will take a while to travel from end to end. Any delay will lock object or tables giving the user a non responsive feel in the UI.
- Many UPDATE SQL are seen in the agent or PLSQL.
- Locks in Agent are used in Modified agent.
- Copying of data from different objects like Shipment to OR (Order Release).
Tips
- Removal of PLSQL packages in Agent and DSU (Direct SQL Update) to bare minimal, especially Modified Agents.
- Reduction of codes in PLSQL. Delete or comment out old codes.
- Reduce your UPDATE to a single point.
- Add exception handling in your PLSQL to allow graceful exit of your PLSQL.
- Conduct unit testing to each Agent or procedures before enabling them. If in doubt, remove them.
It is a painful process and huge amount of mandays are spend. Of course, lots of practice and hair tearing are done before you realise these tips. Hopefully, they are of help to those doing migration or upgrades of OTM.
Danny Kwok 