Query (Outer joins , Nested Loops)

Printable View

02-11-2004, 11:57 AM
sreddy

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 :(
02-11-2004, 12:40 PM
slimdave

Have you tried tracing the fast andslow executions, to see what the difference is in terms of individual waits?
02-11-2004, 12:50 PM
sreddy

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.
02-11-2004, 01:27 PM
pando

sql trace and tkprof!
02-11-2004, 01:35 PM
sreddy

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.
02-12-2004, 02:29 AM
abhaysk

Re: Query (Outer joins , Nested Loops)

Quote:

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 !
02-12-2004, 09:43 AM
slimdave

Re: Query (Outer joins , Nested Loops)

Quote:

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!
02-12-2004, 09:59 AM
DaPi

Re: Re: Query (Outer joins , Nested Loops)

Quote:

Originally posted by slimdave
Me Gov't, me 9.2.0.4!

Explains a lot (see CNN, BBC, alJesira or similar www).