Query (Outer joins , Nested Loops)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Query (Outer joins , Nested Loops)

  1. #1
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you tried tracing the fast andslow executions, to see what the difference is in terms of individual waits?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    sql trace and tkprof!

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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"

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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
  •  


Click Here to Expand Forum to Full Width