Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Posts
    4

    Unanswered: Upgrading To 8.2.0 Leads To Performance Degradation

    Hi,
    does UDB 8.2.0 Fixpack 7 degrades performance.

    I have 7.2.9 UDB on Windows 2003.
    steps follwed were as follows,
    i) benchmark queries and stored procedures on 7.2.9.
    ii) install 8.1.2 UDB.
    iii) migrate database, convert indexes, rebind packages.
    iv) benchmark queries and sp's.
    ( at this point - the results of 7.2.9 and 8.1.2 were close enough ) .
    v) Apply Fixpack 7
    vi) benchmark queries and sp's.
    ( at this point - the results of 8.2.0 was not even close to the results from 7.2.9 or 8.1.2, the results were showing
    a severe performance degardation.) same is the case with Fixpack 7a and 8

    IBM Support suggested :
    to change DB2_HASH_JOIN to NO cos the default had changed to YES in 8.2.0

    tried that, still then, i am not able to acheive the same results as in 7.2.9.


    that optimizer is not giving the expected results...
    i tried to influence the optimizer by changing the DFT_DEGREE, MAX_QUERYDEGREE, DFT_QUERYOPT parameters

    i would like to know, if i am making any mistake in the upgrade process or is there anything else that needs to be followed up inorder to achieve tha same results

    Suggestions and help required...

    Thanks

    Ratish

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would try installing fixpak 8, which is now available. Also, do reorgs and runstats again if possible, then the rebinds. The runstats command has changed from V7 to V8, so make sure you are getting stats on "Indexes all," and "distribution on key columns" (at a minimum).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2002
    Posts
    4
    Hi Marcus,

    Thanks for the reply...

    yes, i has read thru the Fixpack 8 release notes, applied Fixpack 8, did the runstats (DB2 RUNSTATS ON TABLE <> WITH DISTRIBUTION AND DETAILED INDEXES ALL ) and rebind (DB2RBIND <> ALL -u <> -p <> -l <>).
    but the results are still the same with no difference...

    i didn't do the reorg, r u suggesting that i do the reorg followed by runstats and rebind ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would normally do the reorg, but I can't really tell you that it is the problem.

    I think that they only way we can really help on this forum is if you give us details about the problem SQL statement. That would include providing the DDL of tables and indexes with row and distribution counts.

    If you could do an explain report on the problem query, that would also be useful.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    Probably too late

    But the only way to get a definite answer as to what is going wrong would be to have taken explain plans of your benchmark queries on 7.2 then done the migration and then taken explain plans once again. You'd then be able to see if the manner in which the optimiser was accessing the data had changed or not.
    jdey@macehill.co.uk
    http://www.macehill.co.uk

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It looks like the DB2 memory management principles have been changed significantly between v7 and v8. You'll need to play with agent memory allocation parameters to remedy the situation. Check db2diag.log for memory allocation errors and take snapshots of running applications to see actual memory utilization.

    E.g. in one of the cases we had to decrease SORTHEAP, otherwise the optimizer tended to choose plans favouring in-memory sorts as opposed to index access.

Posting Permissions

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