We are using Oracle 9i for our application's database. A certain trigger in the application performs a lot of queries to the database and these queries causes our database to be in a deadlock. The reason for this is that the threads are locked in the database when the application executes the queries in the trigger. Due to this, our BEA runs out of the JDBC threads and losses the connectivity to the Nodes.
In order to fix this problem, we focused on optimizing the queries made to the database by creating indexes to the columns being accessed in the queries (index scan). The query statement goes something like:
Select sum(amount_field) from Requisition Req, ariba.user.common.User PartUser where (OrderedDate between Date1 and Date2 OR ReceivedDate between Date1 and Date2) AND Req.Requester.UniqueName = PartUser.User.UniqueName AND PartUser.Accounting.Region.UniqueName = 'Test_Region'
Do you think this is the best approach to this type of problem? Does anyone have an idea what would be a good approach for this type of problem? Are there any impact when index scan is used or will index scanning even fix this problem?