Results 1 to 6 of 6

Thread: Optimizerrr

  1. #1
    Join Date
    Oct 2003
    Posts
    29

    Unanswered: Optimizerrr

    Hello Friends

    Can any one tell me What is the correct optimizer level to set in DSS environment?

    Here, complicated queries -- minimum with 10 table joins to get the result set from the tables which contains more than 100 billion rows each.

    Please help!

    -Racha

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    DFT_QUERYOPT=5

    Hi,

    We prefer to use 5 and we are pretty happy with the performance of the
    same.

    So i feel you can go ahead and set DFT_QUERYOPT=5 and monitor your performance.

    HTH

    Nitin

  3. #3
    Join Date
    Apr 2003
    Posts
    191

    Re: DFT_QUERYOPT=5

    Hi,

    same with us. My boss did some tests on dss loads with version 7 one year ago and found level 5 to be most effective.

    Johann

    Originally posted by nitingm
    Hi,

    We prefer to use 5 and we are pretty happy with the performance of the
    same.

    So i feel you can go ahead and set DFT_QUERYOPT=5 and monitor your performance.

    HTH

    Nitin

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DFT_QUERYOPT=5

    I'll side Nitin and Johann .... 5 is a good choice ...

    It may be worth using Visual Explain to see if higher optimization levels achieve anything ...

    Cheers
    Sathyaram

    Originally posted by jsander
    Hi,

    same with us. My boss did some tests on dss loads with version 7 one year ago and found level 5 to be most effective.

    Johann
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Oct 2003
    Posts
    29

    Question

    OOOPS!!!

    Iam sorry I did not mention the Version

    Yes its Db2 V8.1 FP3 AIX-5.2

    Did some benchmarking on OPT's

    Optimizer 7 -- No good with performance
    Optimizer 5 -- No good with performance
    Optimizer 3 -- Performance is good but (Missing Hash Join)
    Optimizer 9 -- Perf is good and plan to change it to..

    Any suggestions, ideas..

    -Racha

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Based on your results (poor optimization of level 7), I would make sure all your tables are reorged and you have full runstats on all tables, indexes, and columns. You probably don't need to continually execute runstats if your data is pretty much the same over time, but you need to full and comprehensive statistics at least once.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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