1

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?

6
  • 1
    This is a horrible query on a horrible data model, which tool creates this? The first thing I would check if the statistics on those tables are up-to-date.
    – dnoeth
    Jun 3, 2015 at 11:23
  • @dnoeth Query is generated by IBM WSRR. Data model made by IBM. 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.
    – ctomek
    Jun 3, 2015 at 11:40
  • Creating a SQL Plan Baseline for this query should help you. Tim Hall has a worked example on his comprehensive site: oracle-base.com/articles/11g/… Jun 3, 2015 at 13:01
  • @Colin'tHart If I create baseline for this query will it still work for very similar, but not identical queries? I have very basic knowledge of SQL Plan Baseline and I couldn't find that information.
    – ctomek
    Jun 3, 2015 at 21:39
  • @Colin'tHart As I stated "I am running similar, but not the same, queries all the time so tuning one query is not a solution." My queries differ sometimes in a little bit in structure, sometimes only in arguments, but they always use the same tables and same operations. As far as I know baselines won't help in this case. Am I right?
    – ctomek
    Jun 9, 2015 at 13:22

0