越来越怀疑DB2的优化器

近来多次发现DB2（V8.2）的优化器没有IBM声称的那么好，其行为令人无法理解。今天就又遇到了一次。


cat <<'!' >test1.sql
  select a.IBMSNAP_INTENTSEQ, a.IBMSNAP_OPERATION,
          a.IBMSNAP_COMMITSEQ, a.IBMSNAP_LOGMARKER, a.UWIP_KEY,
          a.MFG_LINE_ID, a.HIST_ENTRY_TIME, a.SERIAL_NUM,
          a.CHILD_SEQ_NUMBER, a.CHILD_MFG_ORDER, a.CHILD_MFG_LINE,
          OPERATION_ID, a.WORK_POSITION_ID, a.BARCODE, a.PART_NAME,
          a.HEADER_CODE_TYPE, a.HEADER_CODE, a.PART_SERIAL_NUM,
          a.HEADER_SERIAL_NUM, a.REASON_CODE, a.REJECT_FLAG,
          a.DESCRIPTION, a.PRODUCT_ID, a.PROD_ITEM_NAME,
          a.SHELL_NUMBER
  from userid.x_uwip_part_usage a, (
     select mfg_line_id, uwip_key
     from userid.x_uwip
     where SERIAL_NUM in ('L3C7379', 'L3C7380', 'L3C7381', 'L3C7382','
             L3C7383', 'L3C7384', 'L3C7385')AND PRODUCT_ID ='065877C')b
  where a.uwip_key=b.uwip_key and a.mfg_line_id=b.mfg_line_id
  with ur;
!
cat <<'!' >test2.sql
  select a.IBMSNAP_INTENTSEQ, a.IBMSNAP_OPERATION,
          a.IBMSNAP_COMMITSEQ, a.IBMSNAP_LOGMARKER, a.UWIP_KEY,
          a.MFG_LINE_ID, a.HIST_ENTRY_TIME, a.SERIAL_NUM,
          a.CHILD_SEQ_NUMBER, a.CHILD_MFG_ORDER, a.CHILD_MFG_LINE,
          OPERATION_ID, a.WORK_POSITION_ID, a.BARCODE, a.PART_NAME,
          a.HEADER_CODE_TYPE, a.HEADER_CODE, a.PART_SERIAL_NUM,
          a.HEADER_SERIAL_NUM, a.REASON_CODE, a.REJECT_FLAG,
          a.DESCRIPTION, a.PRODUCT_ID, a.PROD_ITEM_NAME,
          a.SHELL_NUMBER
  from userid.x_uwip_part_usage a, (
     select mfg_line_id, uwip_key
     from userid.x_uwip
     where (SERIAL_NUM ='L3C7379'or SERIAL_NUM ='L3C7380'or
             SERIAL_NUM ='L3C7381'or SERIAL_NUM ='L3C7382'or
             SERIAL_NUM ='L3C7383'or SERIAL_NUM ='L3C7384'or
             SERIAL_NUM ='L3C7385')AND PRODUCT_ID ='065877C')b
  where a.uwip_key=b.uwip_key and a.mfg_line_id=b.mfg_line_id
  with ur;
!
cat <<'!' >test3.sql
  with tmp (SERIAL_NUM, PRODUCT_ID)as (values ('L3C7379', '065877C'),(
          'L3C7380', '065877C'), ('L3C7381', '065877C'), ('L3C7382', '
          065877C'), ('L3C7383', '065877C'), ('L3C7384', '065877C'), (
          'L3C7385', '065877C'))
  select a.IBMSNAP_INTENTSEQ, a.IBMSNAP_OPERATION,
          a.IBMSNAP_COMMITSEQ, a.IBMSNAP_LOGMARKER, a.UWIP_KEY,
          a.MFG_LINE_ID, a.HIST_ENTRY_TIME, a.SERIAL_NUM,
          a.CHILD_SEQ_NUMBER, a.CHILD_MFG_ORDER, a.CHILD_MFG_LINE,
          OPERATION_ID, a.WORK_POSITION_ID, a.BARCODE, a.PART_NAME,
          a.HEADER_CODE_TYPE, a.HEADER_CODE, a.PART_SERIAL_NUM,
          a.HEADER_SERIAL_NUM, a.REASON_CODE, a.REJECT_FLAG,
          a.DESCRIPTION, a.PRODUCT_ID, a.PROD_ITEM_NAME,
          a.SHELL_NUMBER
  from userid.x_uwip_part_usage a, (
     select c.mfg_line_id, c.uwip_key
     from userid.x_uwip c inner join tmp on c.SERIAL_NUM=
             tmp.SERIAL_NUM AND c.PRODUCT_ID =tmp.PRODUCT_ID)b
  where a.uwip_key=b.uwip_key and a.mfg_line_id=b.mfg_line_id
  with ur;
!
cat <<'!' >test4.sql
  with tmp (SERIAL_NUM)as (values 'L3C7379', 'L3C7380', 'L3C7381', '
          L3C7382', 'L3C7383', 'L3C7384', 'L3C7385')
  select a.IBMSNAP_INTENTSEQ, a.IBMSNAP_OPERATION,
          a.IBMSNAP_COMMITSEQ, a.IBMSNAP_LOGMARKER, a.UWIP_KEY,
          a.MFG_LINE_ID, a.HIST_ENTRY_TIME, a.SERIAL_NUM,
          a.CHILD_SEQ_NUMBER, a.CHILD_MFG_ORDER, a.CHILD_MFG_LINE,
          OPERATION_ID, a.WORK_POSITION_ID, a.BARCODE, a.PART_NAME,
          a.HEADER_CODE_TYPE, a.HEADER_CODE, a.PART_SERIAL_NUM,
          a.HEADER_SERIAL_NUM, a.REASON_CODE, a.REJECT_FLAG,
          a.DESCRIPTION, a.PRODUCT_ID, a.PROD_ITEM_NAME,
          a.SHELL_NUMBER
  from userid.x_uwip_part_usage a, (
     select c.mfg_line_id, c.uwip_key
     from userid.x_uwip c inner join tmp on c.SERIAL_NUM=
             tmp.SERIAL_NUM AND c.PRODUCT_ID ='065877C')b
  where a.uwip_key=b.uwip_key and a.mfg_line_id=b.mfg_line_id
  with ur;
!
for i in 1 2 3 4; do (echo test$i.sql; db2expln -d eware -f test$i.sql -g -t -z ";" | grep Estimated; echo; echo);done



同样的语句和命令，一开始是
test1.sql
Estimated Cost = 560.270813
Estimated Cardinality = 0.000119

test2.sql
Estimated Cost = 560.270813
Estimated Cardinality = 0.000119

test3.sql
Estimated Cost = 135.274155
Estimated Cardinality = 0.000119

test4.sql
Estimated Cost = 876.493164
Estimated Cardinality = 2.809414

后来突然变成了
test1.sql
Estimated Cost = 560.270813
Estimated Cardinality = 0.000119

test2.sql
Estimated Cost = 560.270813
Estimated Cardinality = 0.000119

test3.sql
Estimated Cost = 876.493164
Estimated Cardinality = 2.809414

test4.sql
Estimated Cost = 1476.353027
Estimated Cardinality = 2.809414

真是莫名其妙！

CBO的东西，有时候是让人比较晕的。尤其还要坐统计更新。

问题是什么都没变。两次做db2expln，时间就差了几分钟。

估计8.2自优化还不成熟

我看9里面好多自适应的参数，估计这部分功能应该好很多