Query tuning question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Query tuning question

  1. #1
    Join Date
    Jun 2008
    Location
    Canada
    Posts
    8

    Query tuning question

    Hi,

    I'd be grateful for any insight that might help me with this problem.

    I have a query in oracle - it's fairly large, and it has quite a lot of joins involved (about 15). It runs fine in 9i but in 10g it's very slow (about 200 seconds). What stumps me is that it is very slow even on a database with NO DATA in it. It appears to only become very slow when I reach a certain number of joins in the query. Does anyone have advice for pinpointing exactly where the problem might be here? I think it's because the optimizer is taking a long time to determine the best execution plan, and have considered using the /*+ ordered */ hint to get around this, but so far to no avail.

    Any thoughts?

    Thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Question Maybe it's a bug

    If you upgraded your 10g version to 10.2.0.3 and the OS is Solaris/AIX or HP-UX, then there is an optimizer bug.

    Check Metalink for AIX/HP-UX patch or Solaris work-around.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jun 2008
    Location
    Canada
    Posts
    8
    Thanks for the reply...I should have mentioned this is running on Windows Server 2003.

    It's Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

    I don't think it's an issue with the particular instance, as I've tested it on other instances of 10g.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    Quote Originally Posted by tuseau
    Hi,

    I'd be grateful for any insight that might help me with this problem.

    I have a query in oracle - it's fairly large, and it has quite a lot of joins involved (about 15). It runs fine in 9i but in 10g it's very slow (about 200 seconds). What stumps me is that it is very slow even on a database with NO DATA in it. It appears to only become very slow when I reach a certain number of joins in the query. Does anyone have advice for pinpointing exactly where the problem might be here? I think it's because the optimizer is taking a long time to determine the best execution plan, and have considered using the /*+ ordered */ hint to get around this, but so far to no avail.

    Any thoughts?

    Thanks.
    post the query and the two execution plans

  5. #5
    Join Date
    Jun 2008
    Location
    Canada
    Posts
    8
    I'm not sure how much use this is without knowledge of the db schema, but here is the query:

    /* removed */
    -------------------------------------------------

    Not sure how to post an execution plan here where it would actually make sense and not look like a total mess...can I export an execution plan?
    Last edited by tuseau; 06-27-2008 at 04:23 PM.

  6. #6
    Join Date
    Jun 2008
    Location
    Canada
    Posts
    8
    So in fact there are at least 25 joins here.

    I'm wondering if there's anything that can be done that doesn't require massive restructuring of the db or the views...

    Is there anything here that could create some kind of "loop" in the optimizer?
    It would seem the 10g optimizer is doing something radically different than the 9i optimizer.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    without posting the execution plans its impossible

  8. #8
    Join Date
    Oct 2006
    Posts
    175
    Are the execution plans both for the same query? I can see different numbers on bytes column. Please confirm and post it again with everything properly visible.

    gtcol

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by tuseau
    I'm not sure how much use this is without knowledge of the db schema
    I fully support you, don't understand either why these guys want to see what you are doing... BS!... just look at the left philangy, I'm sure is something wrong with it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Jun 2008
    Location
    Canada
    Posts
    8
    Quote Originally Posted by gtcol
    Are the execution plans both for the same query? I can see different numbers on bytes column. Please confirm and post it again with everything properly visible.

    gtcol
    Yes, they are definitely for the same query.

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