-
Query (Outer joins , Nested Loops)
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 :(
Last edited by sreddy; 02-11-2004 at 12:13 PM.
Reddy,Sam
-
Have you tried tracing the fast andslow executions, to see what the difference is in terms of individual waits?
-
Thats my next step and need to coordinate with application group as these queries with lots of input values are given by PB application.
I flushed the shared pool yesterday and query was running instantaneously and when they complain again I will doing the trace as first step.
Thx anyway.
Reddy,Sam
-
-
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.
Reddy,Sam
-
Re: Query (Outer joins , Nested Loops)
Originally posted by sreddy
Here in this environment (Govt) I can't make a call to go for next release level as its Govt.
God help 'em and convience !
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Re: Query (Outer joins , Nested Loops)
Originally posted by sreddy
Here in this environment(Govt) I can't make a call to go for next release level as its Govt.
Me Gov't, me 9.2.0.4!
-
Re: Re: Query (Outer joins , Nested Loops)
Originally posted by slimdave
Me Gov't, me 9.2.0.4!
Explains a lot (see CNN, BBC, alJesira or similar www).
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|