Query (Outer joins , Nested Loops)

[sreddy]

Hi,

I have been trying to get hold of the performance of a query with Outer Joins and Nested loops. Its runs fine after initiating the first time but drags like anything after running couple of times.

If I flush shared pool it runs instantaneously. The plan shows its doing a FTS on 2 of 100K tables,as thats how the query is. What surprise me is runs with acceptable performance at times and slows at times (having the same load on the database always)

Couldn't figure out whats the problem. I know I had multiple problems on 8173 before(at other client site and I was the lead) with optimizer and patching up to 8174 gave me lots of releif. Here in this environment(Govt) I can't make a call to go for next release level as its Govt.

Anybody experienced same issues with optimizer on 8173 with Outerjoins and nested loops.

Calling Oracle is not a solution as they will be telling me to patch it up to 8174 :(