Professional Web Applications Themes

Optimisation Levels & related Prepare times. - IBM DB2

Hi, We have a query (pretty complex) that we have investigating at different optimisation Levels and I am Little surprised at the results, and it raises some questions: Notes. 1.Package cache flushed before each running. 2.The majority of the elapsed time - 99% is spent preparing statement - verified with db2batch Level Elapsed time(ms) 0 125 1 141 2 328 3 7,250 5 49,828 7 17,938 9 7,328 Q1. If Level5 has heuristics to avoid spending too much time optimising what is a very short running query, why does it insist on continuing to Prepare for some 50 seconds on ...

  1. #1

    Default Optimisation Levels & related Prepare times.

    Hi,

    We have a query (pretty complex) that we have investigating at
    different
    optimisation Levels and I am Little surprised at the results, and it
    raises
    some questions:
    Notes.
    1.Package cache flushed before each running.
    2.The majority of the elapsed time - 99% is spent preparing
    statement - verified with db2batch

    Level Elapsed time(ms)
    0 125
    1 141
    2 328
    3 7,250
    5 49,828
    7 17,938
    9 7,328

    Q1. If Level5 has heuristics to avoid spending too much time
    optimising
    what is a very short running query, why does it insist on
    continuing
    to Prepare for some 50 seconds on a query that can run in 100ms ?

    Q2. Can this very long prepare be controlled/influenced?

    We will move to using 2 to avoid this, but it does seem very strange?

    Paul.
    Paul Guest

  2. #2

    Default Re: Optimisation Levels & related Prepare times.

    Using embedded static SQL eliminates all "prepare time" except the one-off
    bind. Using dynamic SQL - which includes all forms of CLI, ODBC, ADO, etc,
    means you have to choose the trade-off.

    DB2 performs query caching that can assist with repetitive prepares;
    needless to say flushing the package cache is the way to eliminate any such
    benefit.

    The 50 seconds prepare seems excessive to me, unless your query really is
    ludicrously complicated (and even then I find 50 seconds hard to believe).
    See if you can determine where the time is going (CPU, disk, swapping): you
    may just need to tweak some database parameters.

    There is nothing strange in prepare's swamping the total execution time. It
    is a direct consquence of the various database APIs you can use, and your
    choice of what may well be the wrong one for your problem.

    BTW, you can tweak the optimiztion level dynamically (SET CURRENT QUERY
    OPTIMIZATION statement).

    "Paul Reddin" <co.uk> wrote in message
    news:google.com... 


    Mark Guest

  3. #3

    Default Re: Optimisation Levels & related Prepare times.

    Mark,

    I hear what you are saying, but ...

    My bottom line here is that Optimiser appears to have the
    ability to disappear off for 50 seconds (with 100% CPU) without
    anyway of overiding that behaviour?

    - i.e I would expect the heuristics to kick in and just get
    on with running the query after some reasonable or
    controllable point?

    PS. I don't want to shoot the messenger here. :-)

    Paul.
    Paul Guest

  4. #4

    Default Re: Optimisation Levels & related Prepare times.

    Doentation has always stated that levels 7 and 9 (can) consider
    more possibilities than 5. In particular, level 9 is unlimted. So
    the fact that level 5 takes much longer than 9 - which should be
    considering more possibilities rather than fewer - seems all wrong,
    surely?


    Jeremy Rickard
    Jeremy Guest

  5. #5

    Default Re: Optimisation Levels & related Prepare times.

    If your query is very very complex (after application of RI, triggers, etc),
    then it is possible that you may have massive prepare times at higher
    optimization levels (and a slow CPU).

    You should definitely review the section on optimization levels in the DB2
    Administration Guide. It is basically pointless to use an optimization level
    that is too high for your problem (which, in your case appears to include
    mandatory, forced run-time re-preparation). Static SQL is still the best way
    to go for high performance.

    "Paul Reddin" <co.uk> wrote in message
    news:google.com... 


    Mark Guest

  6. #6

    Default Re: Optimisation Levels & related Prepare times.



    whenever we have to shoot at a moving target, we need to take a
    careful aim each time before pulling the trigger. Taking an aim just
    once would result in misses every time after the first shot. It is a
    good rule of thumb to use static whenever posiible. Yet, like every
    other rule of thumb I know about, this one has got a lot of
    exceptions, usually relevant to data skew
    AK Guest

  7. #7

    Default Re: Optimisation Levels & related Prepare times.

    "Mark Yudkin" <org> wrote in message news:<bmiscu$dk0$news.aol.com>... 

    But has anyone a plausible explanation as to why levels 7 and 9 are
    taking so much longer than 5? If not, I would argue this is something
    Paul should be taking up with IBM support as a possible bug.


    Jeremy Rickard
    Jeremy Guest

  8. #8

    Default Re: Optimisation Levels & related Prepare times.

    I've seen queries take tens of minutes or even hours to compile.
    Legitimately. Join enumeration - the work involved to find the best join
    order - is an NP-complete problem, and the higher optlevels use an algorithm
    that grows exponentially with the number of tables involved in the join.
    Other properties of the various objects in the query can add to this too.
    (Lower optlevels use a polynomial-time algorithm).

    Why Paul would see a peak at opt 5, I wouldn't know without looking in
    detail, but I can dream up a bunch of possible explanations, so honestly
    this doesn't surprise me.

    Optimization level is the Big Knob that you use to control this. The
    DB2_REDUCED_OPTIMIZATION registry variable may also come in handy (see
    online v8 doc). With both of these you face a tuning tradeoff - more
    optimization means possibly a better plan; less optimization means you may
    miss the best plan.

    Hope this helps,


    Kaarel Guest

  9. #9

    Default Re: Optimisation Levels & related Prepare times.



    Paul Reddin wrote:
     

    Where did you do these testings? On the testing machine?
    Can you post your hardware resources - # of processors, CPU speed,
    physical memory size, # of disks your table is stored ..?
    The number of table records? If more than one table refered in the sql
    query, how many?
    How large is the result size?
    Is it a EE or EEE db? intral_partition? inter_partition?

    Regards,
    FRX





    Fan Guest

  10. #10

    Default Re: Optimisation Levels & related Prepare times.

    Thanks again All,

    But my basic problem is that the Optimiser at Level 5
    is able to take 50 secs over a Prepare without me having
    control to stop/override this - other than changing the
    optimisation Level, which we may not want to do
    for many other reasons e.g Mixed Workload etc.

    PS. Our CPUs are very Fast!

    What I would really like is a governor/timeout on the optimiser
    Prepares to ensure I can limit the maxmimum time it takes
    doing the prepare.

    - Optimisation Level is a crude/inflexible/unquantifiable way to have to do this.

    Paul.
    Paul Guest

  11. #11

    Default Re: Optimisation Levels & related Prepare times.

    "Kaarel Truuvert" <ibm.com> wrote in message news:<bmk5ac$qau$torolab.ibm.com>... 

    Kaarel, Now you are really scaring me! On a production machine
    How could anyone tolerate this without a trap-door to
    stop it chewing so much CPU?
     

    The Query does join maybe a dozen or so tables?
     

    I'll Take a look this.

    Thanks.
    Paul Guest

  12. #12

    Default Re: Optimisation Levels & related Prepare times.

    Kaarel,

    I appreciate that the optimizer is complex, but surely you aren't
    suggesting that optimisation level 5 taking many times longer than 9
    is somehow okay - rather than a bug that Paul should report if he has
    time? To my mind that would contradict and undermine the whole
    doented concept of 9 being an exhaustive search and 5 a limited
    one.


    Jeremy Rickard
    Jeremy Guest

  13. #13

    Default Re: Optimisation Levels & related Prepare times.

    There are some general rules which will help you to choose the optimizer level, or you
    can begin with this.
    (1). Is this a DW/OLAP/DSS system or an OLTP system?
    (2). If this a Mixed system, do you care THROUGHPUT or RESPONSE TIME?
    db2batch is a good tools to find out the response time.
    (3). The higher the optimizer level, the more system resources it will need.
    I met some cases we have to decrease the optimizer level to 2. The main reason
    is because the system resource limitation.
    (4). The higher the optimizer level, the more the optimizer will depend on the
    statistics/distribution information.
    (5). Ö.

    So there are always some balance you need to find out and take care of it. That is also
    why for each system, the performance tuning is always case-by-case. That is possible
    what you saw here canít reproduce to the other box.

    Let's say if we plot the Elapsed time on a XY coordinate, where X axis is the optimizer
    level, Y
    axis is the Elapsed Time when run the same query with optimizer level applied to the X.
    The Curve you see on NT maybe different than what you see on a Unix box. Even they have
    the same CPU Hz.




    Paul Reddin wrote:
     

    Fan Guest

  14. #14

    Default Re: Optimisation Levels & related Prepare times.

    "Jeremy Rickard" <biz> wrote in message
    news:google.com... 

    Paul is of course welcome to pursue his problem with db2 service. All I'm
    saying is that "50 seconds? there must be a bug!" is not a foregone
    conclusion and that there are often explanations for curious behaviour and
    exceptions that prove the rule.

    Here are two examples of where this can happen. I'm not saying Paul is
    hitting either of these, I'm just providing them as examples off the top of
    my head.

    1. Optlevel 7 starts its optimization, aggresively attempts to acquire a ton
    of memory, runs out rather quickly, and automatically restarts optimization
    using greedy join enumeration instead, which completes quickly. (If this
    happens you'd see an sqlcode warning 437 reason code 1). Optlevel 5 uses
    less memory, chugs along and manages to complete without this fallback, but
    as a result it takes longer.

    2. Query Rewrite finds a Real Cool meta-optimization at optlevel 9 that's
    not applied at 5, making the whole shebang a lot less complex by the time it
    gets to the optimizer. Paul can see whether this is the case by comparing
    the Optimized Statements near the top of db2exfmt output.

    Kaarel Truuvert
    DB2 UDB Development


    Kaarel Guest

  15. #15

    Default Re: Optimisation Levels & related Prepare times.

    Sorry, Paul - didn't mean to scare you. Just wanted to point out that it
    really isn't that hard at all to write a query that could "exhaust all time
    and space in the universe" if exhaustively optimized. Think of the old story
    about rice grains doubling on each square of a chess board.

    You're absolutely welcome to pursue this via the service channels, of
    course, but I had to throw in my 2 cents' before you did so. And yes, we
    have had bug fixes dealing with prep time problems before - make sure you're
    up to date on your fixpaks.

    (Remember also that optlevel can be dynamically reduced on a per-query basis
    via the current query optimization special register).

    "Paul Reddin" <co.uk> wrote in message
    news:google.com... 

    Kaarel Truuvert
    DB2 UDB Development


    Kaarel Guest

Similar Threads

  1. Replies: 5
    Last Post: January 14th, 12:19 AM
  2. DateTime search optimisation
    By Salagir in forum MySQL
    Replies: 2
    Last Post: February 14th, 08:31 AM
  3. Replies: 40
    Last Post: July 25th, 04:50 PM
  4. Algorithm for optimisation - using Graph.pm?
    By Clyde Ingram in forum PERL Miscellaneous
    Replies: 0
    Last Post: July 24th, 09:45 PM
  5. functions - optimisation
    By hairybobby webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 3
    Last Post: July 21st, 09:28 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139