I am facing query performance issue in 11g. it takes long time to run or couldn't run.
Same script able to run in 10g faster.
Database were upgraded 10g to 11g.
Those script using "connect by" hit the issue.
select col1 from tableA
where col2 in (select col2
start with col3 IN (select col3 from tableC where col4=22222)
connect by prior node = parent_node
They were many script same like this scenario , it is difficult to change the script.
Difficult to answer if you don't provide sufficient information. Check the FAQ in the section SQL and PL/SQL FAQ and provide information that could be analyzed.
Post everything mentioned in the FAQ so people will be able to help you.
Did you find any solution for this performance issue? On changing the optimizer parameter "_connect_by_use_union_all" to the value prior to 11G, the performance seems to be ok. However I don't know (yet) what impact of this change implies.