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

Thread: 10g Optimizer Choosing Wrong Path

  1. #1
    Join Date
    Sep 2006
    Posts
    23

    10g Optimizer Choosing Wrong Path

    I'm in the testing phase of upgrading my database from 9.2.0.6 to 10.2.0.1. After the upgrade many queries ran slower. I noticed that the slow queries were doing hash joins instead of nested loops. Both tables that are joined in the query are very large tables. If I change the optimizer_features_enable to 9.2.0, it will then do nested loops, but when I change it back to 10.2.0.1 it does hash joins again. I can use a nested loop hint which make the query run very fast, but is there any parameters I can set so I don't have to use hints. I have too many queries that may be effected by 10g optimizer to hint. Does 10g score nested loops or hash joins different than 9i? I also know the 10g factors in cpu cost when calculating cost. Could this be the difference maker? Thanks for any help!

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you gathered system statistics on the database?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Nov 2001
    Posts
    335
    You can ajust optimizer_index_cost_adj. Make it 20 and see if it changes for the better.
    Or you can try to analyze your tables differently to come up with optimial solution.
    It favors hash because it thinks that sequential read are not that expensive in your system(check db_file_multiblock_read_count ) and that you have large amount of memory to play with.
    You can try to ajust them as well.
    There is an event you can turn on and you could see all optimizer iterations and why it is choosing the plan that it is choosing.Sorry, I do not remember event number.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Quote Originally Posted by BV1963
    .
    There is an event you can turn on and you could see all optimizer iterations and why it is choosing the plan that it is choosing.Sorry, I do not remember event number.
    you mean 10053, quite hard to interpret though, you need to be very experienced

    Branshee if I were you I would get all parameters in 9i and compare it to 10g and see what changed, including the hiden parameters.

  5. #5
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72

    Wink

    Hi ,
    How about trying the following :
    1. Analyze table TB_NAME delete statistics; (If the existing statistics generated using ANALYZE command)
    2. Generating statistics for the involved tables using DBMS_STATS package
    3. Alter session set optimizer_index_caching=90;
    4. Alter session set optimizer_index_cost_adj= <(db file scattered read/db file sequential read)*100>
    ( As BV1963 said, 20 can be used to begin with)


  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The gathering of system statistics would probably be a more robust solution thatn tweaking the oica, though you could do that as well. It will alow Oracle to take into account the CPU component of cost as well.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Nov 2001
    Posts
    335
    It seems like the same problem all over again, I lived it when we upgraded from 8I to 9I!
    I really hate the fact that ORACLE is chaning preferred optimizer behavior in every version.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    not versions, there are changes between patchsets as well

    but you probably notice queries runs faster as well, software changes and we cannot really do anything against that, I personally prefer working with latest versions than working with version which are 7, 8 years old

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    dave's suggestion solves most of such problems.
    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"

  10. #10
    Join Date
    Sep 2006
    Posts
    23
    Sorry for taking so long to respond. I have gathered schema stats with the dbms package. I also gathered system stats. In the system stats, it only shows my cpuspeed as 121.23. Should I manually set this to the speed of my cpu? Also I had to manually tune sreadtim, mreadtim, and mbrc. The optimizer was using full scans too often. After changing those two, many queries have spead up through the use of indexes. But it still wants to do hash joins. I will try to do a 10053 and see why. The bytes from the explain plan for the build table are many times larger than my allocated hash area in memory. But why is it still choosing hash without hints? Thanks!

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