Five years ago I have written the ORA-00060 little oddity article about this strange (for me!) error:
ORA-00060: deadlock detected while waiting for resource
Not understanding on time what was happening on the database forced the developers to rethink the architecture of the application and this added a couple of months of designing, developing and testing on an already tight schedule.
Instead of a simple solution as:
alter table T1 initrans 255 /
we’ve come up with a convoluted solution that only covered up the real issue.
Fast forward to this month when I’ve got the chance to play with the Method-R software: Method R Workbench and Method R Trace for Oracle SQL Developer. (For more details on how useful and what it can do check Some Things You Probably Didn’t Know about Oracle Tracing (Cary Millsap DOUG Presentation 2020-05-07))
While playing with this software I’ve gotten curious: given the same error, how fast I will get to the same conclusion?
I have recreated my test case and run it with Method R Trace for Oracle SQL Developer enabled. This automagically retrieved the trace file for me.
Drag-n drop the file into Method R Workbench and open the generated Method R Profile:
And under one minute I was looking directly at the problem. No more guesswork. No more Sherlock with the magnifier goofing around. And the time spend in search of the problem can easily be used to find a better solution.