I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with 10.2.0.3. Query executed in matter of seconds. It didn't work with 10.2.0.4 and higher versions so the question is which functionality from 10.2.0.4 causes this query to execute for a very long time (now I am running this query on 10.2.0.4 and it's over half an hour already).
Query: pastebin.com/zmzpwYgq
(I would post link with "http://", but I need at least 10 reputation to post 3 links... Great idea.)
Execution plans for above query:
optimizer_features_enable='10.2.0.3' //works well
optimizer_features_enable='10.2.0.4' //works horribly slow
Text versions of execution plans (they differ from above):
optimizer_features_enable='10.2.0.3': pastebin.com/zu7wf0e5
optimizer_features_enable='10.2.0.4': pastebin.com/r6xFGv5H
I don't know how to analyze execution plans, but one thing I see is that execution plan on 10.2.0.3, which works well, uses USE_HASH hint and 10.2.0.4, which works badly, doesn't use this.There is no possibility to modify this query. I am running similar, but not the same, queries all the time so tuning one query is not a solution.
There are 3 tables used in this query: OBJECT, SUBJECT and STATEMENT. I tried before to gather statistics on all those tables and nothing changed. Now I did the same, same result. Sample percent was set to 35. I also gathered statistics for entire schema at 35 estimate percent, still nothing.
//EDIT
As I see there is problem with joining tables. When nested loops are used in 10.2.0.4 for joining tables it takes forever to execute. When hashes are used in 10.2.0.3 to join tables it works normally. But parameter hash_join_enabled is deprecated now and it is disabled in 10.2.0.4. So.. what should I do to make joining tables work faster without using hashes?