I am looking for a static analyser of Oracle queries and PL/SQL procedures (triggers, constrains, ...) - a tool that will pass on our DB scheme and point to potential deadlocks. Just like FindBugs for Java.
If such a tool does not exist, would you like to have it ?
TOAD have some static analysis (or at least some kind of code quality) tools. I doubt they will be able to find dead locks though.
From 10g onwards, the database now has a static PL/SQL analyser built into it:
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';
Have a google for PLSQL_WARNINGS and you'll find some helpful references
I agree with Matthew though, it's unlikely that you'll be able to find an analyzer that will be able to detect deadlocks particularly effectively... there's too many variables in play.
There is an endless potential for deadlocks in any database. So the tool would need usage statistics to know which deadlocks would statistically happen more than once every year. Such a tool would be too complex to justify the development work.
The most common practice would be to install the database in a Q&A or production environment. And then monitor the deadlocks that actually occur. You could run automated unit tests against the Q&A environment to simulate load.
Deadlocks would depend on transactions not on static code. There is no concept of a 'BEGIN TRANSACTION' statement in Oracle, so a static analyzer has no way of knowing what the start point of a transaction is. Hypothetically, an analyzer could be written such that if it was given a starting SQL or PL/SQL statement, it could track all the potential execution paths and determine which tables were subjected to update/delete/insert/merge statements in what order. Then you could 'compare' two (or more) results of that and determine if there are any where tables are manipulated in a different order (eg TAB_A then TAB_B in one and TAB_B then TAB_A in another). I suspect that would throw up a lot of false positives.
In Oracle, selects do not lock (except SELECT...FOR UPDATE). As such, deadlocks only occur on data updates and only when two concurrent transactions are trying to update the same rows.