Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    61

    Unanswered: Query performing good in 9i but very very slow in 10g

    Hi,

    I have a large query that completes in about an hour in 9i but it takes about 6 hours in 10g. I have contacted support, just want to know if anyone of you have faced such an issue before.

    Thanks,
    Srinivas

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Have you gathered recent statistics on the 10g machine? Is the data distribution the same on both machines? Do both machines have the same indexes? Are the servers similar in memory and processors? Are the execution plans the same? There can be many reasons why this is happening.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2005
    Posts
    61
    Thanks for your response. This is a bug and it's fixed.

    Thanks,
    Srinivas

  4. #4
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    What was the bug and how was it fixed?

  5. #5
    Join Date
    Dec 2006
    Location
    Latrobe, PA
    Posts
    16
    I don't know if this was the resolution, but there is a bug with log running sql in 10g if your optimizer_features_enable parameter is set to the version/release your running (i.e. 10.2.0.2). There is a solution or recommendation to set it to the previous release of Oracle you were running...for example 9.2.0.7.

    Don't know if this helps....

  6. #6
    Join Date
    Dec 2006
    Posts
    5
    I had the same problem recently on a 9i to 10g upgrade.
    I had a 5 min query that was estimated to run for 166 hours!

    I resolved it by setting
    _optimizer_cost_based_transformation = off
    _gby_hash_aggregation_enabled" = FALSE

    The problem was described in Metalink note 295819.1 -
    The Oracle Cost Based Optimizer is continually been enhanced. These enhancements were designed to improve performance but in some cases can cause a minority of queries to perform no better or worse than before. This article discusses some of the potential problems you may encounter when moving SQL Queries from Oracle 9i to Oracle 10g.

    Hope this helps
    Bob

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by anurbas
    I don't know if this was the resolution, but there is a bug with log running sql in 10g if your optimizer_features_enable parameter is set to the version/release your running (i.e. 10.2.0.2). There is a solution or recommendation to set it to the previous release of Oracle you were running...for example 9.2.0.7.
    Presumably the idea would be to disable all new optimizer features only temporarily, until you had fixed the actual problem.

  8. #8
    Join Date
    Dec 2006
    Posts
    5
    Oracle are not actually saying it is a problem either with the query or the optimizer, just that the optimizer can handle some queries differently!
    It's most odd.
    The query that generated the problem was fairly innocuous, and the database was newly built under 10g, then the data imported (with COMPATIBLE set to 10.2.0.2).
    This is the extract from the SR with Oracle support -
    Since you hit a query performance problem after a migration from 9i to 10g and the workround above solve the problem you can use this solution as a long term solution.
    The "Costed Query transformations" feature and the "Hash Group by aggregation" optimizer features were designed to improve performance but in some cases can cause a minority of queries to perform no better or worse than before and this is not considered as a bug (for example BUG 4890603 - HARD PARSE TAKING SIGNIFICANTLY LONGER IN 10.2 THAN 9.2, closed as not a bug).
    With the setting above you simply disable this new optimizer features.
    About the query performance problem after a migration to 10g I've found also the Note 5131645.8 - Bug 5131645 - 10g hard parse CPU regression [appopd-> qksfrochild ], fixed in 10.2.0.3

    At this point (close to go-live), I'm only too glad to have the workaround!

    Bob

  9. #9
    Join Date
    Oct 2005
    Posts
    61
    I apologize for not having posted more details, I just got back after the vacation.

    The problem we were facing was similar to what "anurbas" mentioned. We were hitting at bug 3499674. In our case deleting the statistics of the concerned table did the trick for us. I am sceptical (rather would not want to)about setting optimizer_features_enable to any earlier versions as I want to use the 10g features.

    Thanks,
    Srinivas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •