query optimizer runs astray....

blom0344 · #1 (**permalink**) 11-09-04, 07:13

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

quigleyd · #2 (**permalink**) 11-09-04, 13:49

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.

blom0344 · #3 (**permalink**) 11-09-04, 15:47

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...

xamar · #4 (**permalink**) 11-10-04, 03:57

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

blom0344 · #5 (**permalink**) 12-15-04, 03:45

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.....

dr_suresh20 · #6 (**permalink**) 12-15-04, 06:33

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.

Marcus_A · #7 (**permalink**) 12-16-04, 22:16

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.

blom0344 · #8 (**permalink**) 12-17-04, 04:51

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?

blom0344 · #9 (**permalink**) 12-17-04, 05:15

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech