Just a couple of small updates on some previous blogs.
The database that was suffering from network latency problems between it and the app servers was moved down South last week. It all went very smoothly and that particular problem has been resolved. The 20+ hour job runs over night very easily and other areas of the app that people hadn’t realised were unnecessarily slow have improved too. Definitely a success, and the 10046 tracing did get to the root cause of the problem.
[Updated 9th Feb 2006 – Actually, the root cause of the problem is the application design but the network latency is the problem that we can solve now.]
The queries that were suffering after the 9i upgrade have been reviewed by Oracle Support and the solution is to disable a couple of 9i features
_complex_view_merging=false
_push_join_predicate=false
I’d had a fairly confident guess at the first (and in fact mentioned it in a comment to the original blog), but I’m glad we got support involved. Both of these settings were part of the optimizer_features_enable change we made.
[Updated 9th Feb 2006 – I should point out that these two hidden parameters are being set through a login trigger for the particular application user experiencing this problem, based on Oracle’s recommendation. The longer term solution will be to tune specific queries]