Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    5

    Question Unanswered: Performance Degrades in V8 :-( Help!

    Hi All,

    When I execute a query in version 7.2, I get the cost as 648.
    I migrated my database from Db2 v7.2 to v8.1.2.

    If I run the same query again, its cost is about 748.

    When I check the access plan, then I find that version 8.1 is not using some indexes that exist on the tables which also got migrated from version 8.

    Anyone knows why and what to do?

    Pls do mail to me ASAP, if u know!

    Thanks & Regards,
    Priya

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Runstats

    Hi Priya

    Try running runstats on the tables in question
    they optimizer might not have the appropriate
    information; - and then run the query again.

    Its just a guess but I hope it helps

    Cheers
    Last edited by Tank; 05-26-04 at 07:23. Reason: Forgetfullness
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    May 2004
    Posts
    5
    Thanks for the response!
    But I have tried RUNSTATS already on the affected tables and also on the systables.

    Also, I have converted the Type-1 index to type-2 using REORG indexes.

    Both have not helped!

    Rgds,
    -hp

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Unhappy Optimizer

    hmmm... thats not very good

    - which optimizing level are you using?
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Total cost of 748 is very very good. What is total execution time of this query?

    Grofaty

  6. #6
    Join Date
    May 2004
    Posts
    5
    I didnot change or specify any optimizing level - its the default

  7. #7
    Join Date
    May 2004
    Posts
    5
    Its about 6 minutes ...since its on a query and there is a huge list of values comapring with the IN clause...
    The query is in a stored procedure where the application calls this repetedly and I am afraid of the greater fall on performance.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Can you post the SQL? Please also provide index information (on which table and on which columns), is index cluster or regular, etc.

    What is your operating system (windows, unix) and version?

    Grofaty
    Last edited by grofaty; 05-26-04 at 09:24.

  9. #9
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by db2priya
    Its about 6 minutes ...since its on a query and there is a huge list of values comapring with the IN clause...
    The query is in a stored procedure where the application calls this repetedly and I am afraid of the greater fall on performance.
    Did you runstats with indexes?

    RUNSTATS ON TABLE <blah> AND INDEXES ALL

    If you have the space/time, this might further help:
    RUNSTATS ON TABLE <blah> AND DETAILED INDEXES ALL

    If it's static, rebind after...

    Check the cardinality on the access plan nodes, too, to see if it's realistic. If it is, then it may not be a stats issue.
    --
    Jonathan Petruk
    DB2 Database Consultant

  10. #10
    Join Date
    May 2004
    Posts
    5
    The SQL is on join between a view & a table. The SQL is at the end of thsi message!
    The index is on the table and its a regular index.The operating system is Windows.

    Yes. I had executed the command RUNSTAT on indexes.

    What I observe from the access plan is that the index which exists on that table- column was used by version 7 whereas its not being used by 8 and so there is an increase in the cost. I notice whenever there is a NLJOIN on the two cloumns the cost difference is huge between the two versions.


    Select view.Col1, cas.c1, cas.c2,view.c3,
    view.c4, view.c5, view.c6, view.c7,
    view.c8, view.c9, view.c10, view.c11,
    view.c11, view.c12, view.c13
    from VIEW view,TABLE cas
    where view.c1 IN (0, 4)
    and view.c2 IN( 12397, 12398, 12399, 12400, 12403, 12404, 12405,
    12406, 12407, 12408, 12409, 122910, 122911, 122912, 122913, 122914, 122915,
    122916, 122917, 122918, 12420, 12421, 12422, 1229123, 1229124, 1229125, 12426,
    12427, 12428, 12429, 12430, 12431, 12432, 12433, 12434, 12435, 12436,
    12437, 12438, 12439, 12440, 124291, 12442, 12443, 12444, 12445, 12446,
    12447, 12448, 12449, 12450, 12451, 12452, 12453, 12454, 12455, 12456,
    12457, 12458, 12459, 12460, 12461, 12462, 12463, 12464, 12465, 12466,
    12467, 12468, 12469, 12470, 12471, 12472, 12473, 12474, 12475, 12476,
    12477, 12478, 12479, 12480, 12481, 12482, 12483, 12484, 12485, 12486,
    12487, 12488, 12489, 12490, 12491, 12492, 12493, 12494, 12495, 12496,
    12497, 12498, 12499, 12500, 12501, 12502, 12503, 12504, 12505, 12506,
    12507, 12508, 12509, 12510, 12511, 12512, 12513, 12514, 12515, 12516,
    12517, 12518, 12519, 12520, 12521, 12522, 29101, 29102, 29103, 29104,
    29105, 29106, 29107, 29108, 29109, 29110, 29111, 29112, 29113, 29114,
    29115, 29116, 29117, 29118, 29119, 29120, 29121, 29122, 291123, 291124,
    291125, 29126, 29127, 29128, 29129, 29130, 29131, 29132, 29133, 29134,
    29135, 29136, 29137, 29138, 29139, 29140, 291291, 29142, 29143, 997510)
    and view.c2= ?
    and view.c3 = ?
    and view.c4 = ?
    and view.C1 = cas.C1
    and view.C2 = cas.c2
    and cas.c5 = ?
    and cas.c6 IN( 'A', 'D')
    and ((cas.c6 <> 'D')
    or (cas.c7 > CURRENT DATE))
    order by view.c3, view.c4, view.c5, view.c6, view.c7, view.C8

  11. #11
    Join Date
    Jan 2003
    Posts
    1,605
    Hi db2priya,

    Can you also post the view definition and explain of the sql? Can you please also post the index definitions?

    Thanks,
    Grofaty

  12. #12
    Join Date
    Apr 2004
    Location
    Jeddah-Saudi Arabia
    Posts
    7
    after you run RUNSTATS, try to BIND PLAN or BIND PACKAGE
    and run your query

Posting Permissions

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