Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    Unanswered: query optimizer runs astray....

    I am struggling with a user-defined report that runs on a 4-million facttable.

    The facttable primary key consists of 5 fields, including 'item'
    The itemmaster is joined to this field. Both keys are indexed.

    The itemmaster contains (among others) 12 fields storing just Y/N for all records. These are non-indexed fields , about 1% of the records contains a 'Y' for a certain fields (99% 'N')

    With a condition on field11 the access path is by merge joins. The query reads first 29750 records directly and then slows to a halt (1 row/s)

    A condition on field10 creates a path that reads 3 tables first, then uses hash joins and completes in about 1 minute.

    I created an additional index on field11 with no effect on the query and its path. I dropped the index and updated statistics , to no avail.

    In desparation I updated field10 with data from field11 and ran the report with condition on field10. Job finished in one minute........

    This report has been running for some months now without a problem and right now the optimizer chooses a completely wrong path.

    What can I do to steer it into the right direction?

    query-definition:

    Code:
    SELECT
      A.ITEM_ID,
      A.ITEM_DESC,
      B.IREF01,
      B.IABBT,
      sum(C.QTY_ORDER),
      sum(C.VAL_ORDER),
      sum(C.QTY_SALES),
      sum(C.VAL_SALES),
      sum(C.COST_SALES),
      D.CSAL,
      D.CCUST
    FROM
      A,
      B,
      C,
      D
    WHERE
      ( A.ITEM_ID=C.ITEM  )
      AND  ( A.IREF01=B.IREF01  )
      AND  ( D.CCUST=C.CUSTOMER  )
      AND  (
      D.CCUST  BETWEEN  200000 AND 400000
      AND  D.CSAL  IN  (1, 2, 3, 4, 5, 6, 4001, 4002, 4003, 4004, 4005, 4006)
      AND  A.FIELD11 = 'Y'
      AND  C.FISC_YEAR = 2004 AND C.FISC_MONTH  = 11
      )
    GROUP BY
      A.ITEM_ID, 
      A.ITEM_DESC, 
      B.IREF01, 
      B.IABBT, 
      D.CSAL, 
      D.CCUST
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  2. #2
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    If you have an old copy of the plan, compare where its going wrong, Make sure the statics are run for the entire table without any warnings. Any warnings will skew the results, have seen it may of times. You could also play around with the stats, do you have a copy of them from when it ran good, if you do update them with the old copy. You can also manually adjust them till it choosed the best plan. We have done it all 3 ways. DB2 optimizer is buggy and can be influenced the wrong way by 1 bad result.
    David Quigley

  3. #3
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Hello David,

    Not sure how to handle modifying the statistics manually....
    My AS400 co-worker suggested getting rid of the SQL packages, but I suspect DB2 UDB does not store packages the way that AS400 does.
    Must confess I am out of my depth here , as I always thought that DB2 optimizer was the best in the field. I know ORACLE can be manipulated by applying 'hints' , but DB2 seems to lack such features...
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  4. #4
    Join Date
    Apr 2004
    Posts
    64

    Reply

    I don't know about AS/400, but in OS/390, you can use OPTHINT to change accesspath using PLAN_TABLE. So it is not true that HINT is possible only in Oracle.

    You can see Admin Guide for more info on OPTHINT. Basically, you bind it once with explain yes. Change the acesspath in PLAN_TABLE and give a HINT name. Then bind again with explain and ask to use that HINT. Go back to PLAN_TABLE to check if it used it. Thats all.

    xamar

  5. #5
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    another month , another problem

    In the meantime I had no opportunity to look into the matter. The problem is still not resolved though, the forementioned query crawls to a halt after reading the first couple of tenthousand records UNLESS I use the condition on the YYYY10 field.
    Does anyone have a suggestion how to correct things. Is it possible to get completely rid of statistics and then make a fresh start?

    This is DB2 7.1 on Windows NT.....
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  6. #6
    Join Date
    Sep 2003
    Posts
    218
    In addition to what ppl have already suggested, you may wanna give this a shot ..

    * Try REORGCHK on table to see if it requires REORG
    * Try evaluating to include bitmap indexes on low cardinality columns eg: Y/N field.
    * Try materialized views or summary tables to cut down on processing time.

    HTH.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Make sure you capture distribution statistics when running runstats. You can capture distribution on all columns or just key columns. In your case I would capture stats on all columns just once since it will take quite a while.

    If that does not work, try experimenting with altering to table to volatile.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    succes at last

    Thanks for the replies. I reran statistics on both the facttable and the dimensiontable once again and to my surprise the forementioned query was resolved in a third manner.
    Thankfully this included hash joins and performance was acceptable again!

    This brings me to the subject of the join types choosen by the optimizer. Can one of you point to good documentation on the various join strategies that DB2 has in store?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  9. #9
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256

    succes at last

    Thanks for the replies. I reran statistics on both the facttable and the dimensiontable once again and to my surprise the forementioned query was resolved in a third manner.
    Thankfully this included hash joins and performance was acceptable again!

    This brings me to the subject of the join types choosen by the optimizer. Can one of you point to good documentation on the various join strategies that DB2 has in store?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

Posting Permissions

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