Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Long Parses

31 August 2008 6 Comments Written by Christian Antognini

From time to time I have to fight against long parses. In this post I would like to share with you what happened to a customer of mine while upgrading from 9.2.0.7 to 10.2.0.3 (10.2.0.4 was not yet available at that time).

The parse of a complex SQL statement took 5 seconds in 9.2.0.7. Not very fast… But, for a report that is rarely executed, it is not a disaster. The problem was that after upgrading to 10.2.0.3 the parse time when up to more than one hour! Of course:

  • It was no longer acceptable.
  • The problem was caused by a bug (in 10.2.0.4 the parse lasts 20 seconds).

In case of long parses that are executed only a few times as in this case, it is usually not possible to avoid the parse. In fact, the SQL statement must be parsed at least once. In addition, if the SQL statement is rarely executed, a hard parse is probably inevitable because the cursor will be aged out of the library cache between executions. This is especially true if no bind variables are used. Therefore, the only possible solution is to reduce the parse time itself. What causes long parse times? Commonly, they’re caused by the query optimizer evaluating too many different execution plans. This means that to shorten the parse times, you must reduce the number of evaluated execution plans. This is generally possible only by forcing an execution plan through hints or stored outlines.

In this case after creating a stored outline the parsing time went down to one second. However, the solution (workaround) implemented in this case was another one…

A careful comparison of the query optimizer’s configuration of both databases pointed out that the 9.2.0.7 was running with the following undocumented parameters set (it goes without saying that nobody remembered why…):

  • _b_tree_bitmap_plans = FALSE
  • _new_initial_join_orders = FALSE

By setting these two undocumented parameters in 10.2.0.3 the parse time when down to 18 seconds. The customer got the consent from Oracle Support to set them in production and, therefore, so they did.

10gR2, 9iR2, Bug, Query Optimizer
TOP: Downloadable Files Are Available
TOP: Addenda and Errata

6 Comments

  1. Christian Antognini Christian Antognini
    1 September 2008    

    Hi Martin

    I agree that this is not the best way to solve the problem. Actually, it’s not a solution, it’s a workaround.

    It was not possible to use the stored outline because the SQL statement was dynamically generated and, in addition, no bind variables were used.

    AFAIK only this one hit the problem with the long parse time.

    Cheers,
    Chris

    Reply
  2. Martin Berger Martin Berger
    1 September 2008    

    Can you explain, why they used 2 undocumented parameters in 10.2.0.3? They probably decrease the optimizers quality of the whole instance. Using a stored outline instead would help in this particular problem, but leave all other plans untouched.
    Where other statements affected also?

    Reply
  3. Robert Klemme Robert Klemme
    12 September 2008    

    What about SQL Profiles? From what I am reading they can change DB parameters and statistics. Wouldn’t this be another option? To avoid confusion, I am talking about this:

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#i37675

    Greetings

    robert

    Reply
  4. Christian Antognini Christian Antognini
    15 September 2008    

    Hi Robert

    One of the characteristics of SQL profiles is to *not* force the query optimizer to choose a specific execution plan. Probably for this reason, I never saw a SQL profiles containing access and join hints (I would really like to see one if somebody did such an encounter… sort of close encounter of the third kind…). Therefore, they are generally not helpful to solve (workaround) long parsing problems.

    Best regards,
    Chris

    Reply
  5. Vineet S Vineet S
    1 March 2010    

    Hello guys

    Can someone please send me the Oracle support response related to this issue as we are also planning to change that DB parameter?
    Thank you!

    Regards
    Vineet

    Reply
    • Christian Antognini Christian Antognini
      1 March 2010    

      Hi Vineet

      The service request was opened by the customer and, as a result, I do not have the response. In any case, if I were you, I would not base any decision on a response given to somebody else 1.5 years ago. Hence, I suggest you to open a service request and let Oracle Support have a look to your specific problem.

      HTH,
      Chris

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.