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 :(
Thx Pando , just wondering people are experiencing any issues with 8173 Optimizer. This Release had given me query performance issues always. None of the methods of statistics gathering helped me before and now. Repeating cycles of tuning exercise helped me, which tables to have statistics and which tables not to have statistics for optimal performance of queries on 200 + million row range partitioned tables. Finally 8174 Solved most of the issues.
Reply With Quote
, just wondering people are experiencing any issues with 8173 Optimizer. This Release had given me query performance issues always. None of the methods of statistics gathering helped me before and now. Repeating cycles of tuning exercise helped me, which tables to have statistics and which tables not to have statistics for optimal performance of queries on 200 + million row range partitioned tables. Finally 8174 Solved most of the issues.
Bookmarks