Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41

    Unanswered: same sql but different explan 2 db

    Hi, I have a complicated select sql run in stress test database with execution time 24min. When I run the same sql in production database with similar data volume and similar configuration, the sql cannot be completed in 12 hours! Attached sql, dbcfg, dbmcfg, explan of both database. Can anyone help?

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    one more file here
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Wow!

    You've got a massive SQL. Optimizing this could be a major task. However, there are always a few things to do:
    1) Are the explains similar? In this case, no. The problem can be there...
    2) Beware the TBSCAN. When reading a table with merge scan, doing JOIN + TBSCAN is a murder. At least, EX_BOARD in the prod-explain seems to have TBSCAN on.
    3) And how to eat an elephant? One part at a time. Consider breaking this sentence into many small sentences, for explain-purpoise only. If any TBSCAN's jump out, consider adding a new index.

    If I had to optimize this beast - I'll probably allocate 50-80 workhours.

    Cheers, Bill

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    If you have a good access path on one system, you could try using an optimization hint with the good access path when binding on the other system.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I'll point out the obvious... RUNSTATS and REORG.

    Presumably same indexes and everything on both systems?
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In addition to RUNSTATS and REORG, for complex queries change the queryopt level to 7. The default is 5.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Marcus_A
    In addition to RUNSTATS and REORG, for complex queries change the queryopt level to 7. The default is 5.
    One more I've found tremendously useful lately is db2advis... often it makes recomendations that at the very least are a good starting point.
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    Thanks all guys.

    I forgot to post some basic info: UDB V7.2, FP7, SOLARIS

    to hurmavi: I will try to break down the sql, but it would not be an easy job!

    to urquel: Could you show me how to use optimization hint? I didn't know if I can add hint similar to that of Oracle.

    to J Petruk: reorg and runstats were already run. Indexes are the same on both system. I will try using db2advis to see if can help.

    to Marcus_A: currently we are using optimization level 0. when we used default 5 in stress test database, the query need 11GB temp tablespace and run for nearly 1 hour. After we changed to level 0, only 0.2GB temp tablespace needed and run for 24 min only. Thus we use level 0. However, when we do the same on production database, it is a nightmare! Users do not expect to change optimization level in different db or in different time. And also, I don't quite understand why the explain plan in both database are so different.

    Again, thanks all. I will post update info as well as my testing result asap.

  9. #9
    Join Date
    Aug 2004
    Posts
    330
    It looks like optimization hint is available on mainframe, but I don't see any reference to it in the midrange manuals. If you were on the mainframe, you would just copy the rows from the PLAN_TABLE corresponding to the access path you want to use and insert them into the PLAN_TABLE where you want to implement the optimization hint. Then update all of those rows and set the OPTHINT column = "MYHINT" where ... Then, REBIND and add the parameter OPTHINT(MYHINT). Of course, I doubt that this will work on your Solaris box, but you could give it a try.

  10. #10
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    41
    Hi urquel, I can't find and table named 'plan_table' in the database. Is it under any particular schema?

  11. #11
    Join Date
    Oct 2004
    Posts
    13
    Hi,

    PLAN_TABLE is available in SYSIBM schema but you have to excute script to create that table as it is not autamatically created .

Posting Permissions

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