互联网服务

SQL性能调优

环境：AIX 5.3.0.0
数据库版本：Informational tokens are "DB2 v9.5.0.4", "s090429", "U825478", and Fix Pack "4".
问题描述：有一个统计需求，需要根据该参数表a中的某些取值找到b表中对应的行业机构，再根据b表的行业机构找到对应的商户，跟根据这些商户最后找到对应的终端，并对上述商户和终端进行统计。编写完成SQL之后发现执行效率非常差，请问诸位如何进行性能调优。
问题SQL：

select
count(distinct b.m) as total_reg_mchnt,
count(distinct b.m||c.t) as total_reg_mchnt
from access_sys a,
       mchnt_inf b,
       pos_inf c
where a.spec= '5301'
and substr(a.access,3,8) = b.industry
and b.m = c.m

real 27m19.16s
user 0m0.01s
sys 0m0.01s

其他信息补充：
1.每张表的数据量

db2 "
select 'access_sys  '||char(count(*)) from access_sys union all
select 'mchnt_inf    '||char(count(*)) from mchnt_inf    union all
select 'pos_inf        '||char(count(*)) from pos_inf "

access_sys 1263
mchnt_inf 4854345
pos_inf    7994221

2.筛选因子
db2 "select count(case when spec = '5301' then 1 end) as result from access_sys"
RESULT
-----------
      3
  1 record(s) selected.

db2 "select ACCESS from access_sys where spec = '5301' "
ACCESS
----------------
0049910005S
0049914219S
0049917515S

db2 "select count(*) as result from mchnt_inf where industry in ('0049910005','0049914219','0049917515')"
RESULT
-----------
      0

3.执行计划

附件：

附件图标 db2expln.out (18.35 KB)

附件图标 db2exfmt.out (67.44 KB)

关注1

参与19

18同行回答
全部行业
全部行业 互联网服务 金融其它 政府机关
|
按赞同排序
按时间排序

wangyax软件开发工程师金融机构

因为涉及到机密问题，所以有些东西我是从测试环境下copy过来的，导致某些结果不尽一致，在此抱歉。
主要问题还是在于谓词之间的连接包含函数导致性能较差。收起

互联网服务 · 2012-11-18

tongjixianing软件开发工程师IBM

exfmt不是很明显的说ACCESS_SYS上的statistics不准吗？那个NLJOIN明显是问题收起

互联网服务 · 2012-11-12

wksw数据库管理员国外银行

另外，你能否给出db2 "select count(*) as result from mchnt_inf where industry in ('49910005','49914219','49917515')" 的结果收起

金融其它 · 2012-11-09

wksw数据库管理员国外银行

db2exfmt.out的结尾会列出表，索引，以及字段分布统计的数据。这些信息是我想要了解的. 但在你的db2exfmt.out没有发现字段分布统计的数据，所以我得出结论" POS_INF, MCHNT_INF虽然有简单的统计，但没有字段的数据分布统计信息". 结论可能是错误的，但很奇怪，你的没有 "Extended Statistics Information:"

请问你是否能进行下面的统计收集，再作explain贴上来db2exfmt 的结果 ? 我希望通过这些统计收集，可以给db2 optimizer提供足够信息来选择正确的join order
1) runstats on table access_sys with distribution on columns (spec, access) and detailed indexes all
2) runstats on table pos_inf with distribution on columns (m) and detailed indexes all
3) runstats on table mchnt_inf with distribution on columns (industry) and detailed indexes all收起

金融其它 · 2012-11-09

wangyax软件开发工程师金融机构

需要说明的是pos_inf表中分布键不是m也不是t，是别的字段。
access_sys表使用的也是别字段。收起

互联网服务 · 2012-11-08

wangyax软件开发工程师金融机构

2) POS_INF, MCHNT_INF虽然有简单的统计，但没有字段的数据分布统计信息.

这个是怎么判断出来的？我在测试环境中使用
runstats on table pos_inf with distribution and detailed indexes all bernoulli(10)
来进行runstats的，这样的话我认为应该是每个字段的分布统计信息吧？收起

互联网服务 · 2012-11-08

wksw数据库管理员国外银行

虽然问题貌似解决了，但咱们还可以进一步深入的讨论一下。

db2advis的思路基本上就是避免任何table scan全部作index access only. 思路虽然好，也迅速解决了该查询的问题，但缺点是增加了特殊的索引。

展开来讨论，如果在一个复杂的尤其是有很多ad-hoc query数据仓库环境下, 这种思路不是很可取。如果每个查询都要靠特殊的索引来提速，最后的结果是数据库整体效率的下降.

根据上传的db2exfmt.out可以看到
1) ACCESS_SYS根本没有统计信息
2) POS_INF, MCHNT_INF虽然有简单的统计，但没有字段的数据分布统计信息.
统计信息的缺失会造成db2 optimizer对access plan, join order的错误选择

建议进行下面的统计收集，再作explain贴上来db2exfmt 的结果。
1) runstats on table access_sys with distribution on columns (spec, access) and detailed indexes all
2) runstats on table pos_inf with distribution on columns (m) and detailed indexes all
3) runstats on table mchnt_inf with distribution on columns (industry) and detailed indexes all收起

金融其它 · 2012-11-07

wangyax软件开发工程师金融机构

童鞋们，速度帮忙看看，我晚上回去就给分了。我现在暂时增加了一个字段，情况有所缓解。收起

互联网服务 · 2012-11-07

wangyax软件开发工程师金融机构

这是db2exfmt执行计划收起

互联网服务 · 2012-11-07

drdb2系统工程师se

more than 90% improvement, go ahead

[missing index, that was my guess]收起

互联网服务 · 2012-11-07

SQL性能调优

18同行回答
全部行业
全部行业 互联网服务 金融其它 政府机关
|
按赞同排序
按时间排序

提问者

问题状态

SQL性能调优

18同行回答全部行业全部行业互联网服务金融其它政府机关|按赞同排序按时间排序

提问者

问题状态

18同行回答
全部行业
全部行业互联网服务金融其它政府机关
|
按赞同排序
按时间排序