与7.1相比，DB2 8.2性能下降 [英] DB2 8.2 Performance drop on Query compared to 7.1
问题描述
我们正在将客户从版本7.1 FP3迁移到版本8.2（8.1 FP8）。
大多数情况下，事情更快，但有一个查询是
要慢得多，这是一个一直使用的查询。
从physical.part_list中选择ATTR1，ATTR2，ATTR3，ATTR4
其中S_PART_NUMBER喜欢''％KJS％''
之前和之后的widlcard似乎正在冲洗它，但对于这个
应用程序的特定部分，这种类型的查询是必要的。
在版本7.1上，此查询大约需要1.5秒（对于36个返回来表示
表中的120,000个事物）
在版本8.2上，此查询需要13.8秒。 （制作副本
数据库）
大多数其他查询相同或更快。
我通过使用改进了我的查询
从physical.part_list中选择ATTR1，ATTR2，ATTR3，ATTR4
where posstr（S_PART_NUMBER，''KJS''）> 0
此查询约为3.5秒。好多了，但仍然比1.5美元的b $ b版本还要慢1.5秒
有什么我可以做的，以改善时间让它恢复到1.5
秒？ DB2 8中发生了什么？我正在阅读一篇文章
说优化引擎已经改变，以至于10个查询中有8个将会更快。双通配符是2中较慢的一个吗？
带有修复的AIX 5.2 ML04上的DB2 8.1FP8（8.2）
默认值为5的优化值
肯
肯，
你有没有比较两个查询的解释（最好的db2exfmt）？
这应该是起点。
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
" 73blazer" <哟** @ ma.com>在留言中写道
新闻：Lu ******************** @ centurytel.net ...我们正在将客户从版本7.1 FP3迁移到版本8.2（8.1
FP8）。
在大多数情况下，事情更快，但有一个查询要慢很多，并且这是一个一直使用的查询。
从physical.part_list中选择ATTR1，ATTR2，ATTR3，ATTR4
其中S_PART_NUMBER喜欢''％KJS％''
<之前和之后的widlcard似乎正在冲洗它，但是对于这个特定的应用程序，这种类型的查询是必要的。
在版本7.1上，此查询需要1.5秒（表中有120,000个表格中有36个返回）
在版本8.2上，此查询需要13.8秒。 （制作
数据库的副本）
大多数其他查询相同或更快。
我通过使用选择ATTR1，ATTR2改进了我的查询，atTR3，ATTR4 from physical.part_list
where posstr（S_PART_NUMBER，''KJS''）> 0
此查询大约是3.5秒。好多了，但仍然比版本7.1还慢1.5秒
有什么我可以做的，以改善时间让它回到1.5
秒？ DB2 8中发生了什么？我正在阅读一篇文章说，优化引擎已经改变，10个查询中有8个会更快。双通配符是2中较慢的一个吗？
AIX 8.2 ML04上的DB2 8.1FP8（8.2）带有修复程序
默认值5的优化值
Ken
首先，对表及其所有索引进行重组。
然后确保执行runstats并获取键上的分布统计数据
列和S_PART_NUMBER如果它不是索引列。
如果这没有帮助，你应该评估缓冲池并确保
索引与V7类似的缓冲池（与之前的对象相同）。可能在您的旧系统中，索引更可能是缓冲池中的居民
并且没有被其他页面刷新。
另外，检查
索引的表空间属性（范围大小，预取等）（假设索引了S_PART_NUMBER）。
Serge Rielau写道：
Ken，
你有没有比较两个查询的解释（最好的db2exfmt）？
这应该作为起点。
干杯
Serge
在查看两者的exlplains时，优化的声明是
相同。缓冲池匹配，优化计划是一样的，一切看起来都很好。我没有看到任何怪物。慢一点8.2 / b
的成本是32458，相比之下7.1的78334，所以7.1不应该慢一点吗？在7.1数据库上CPU速度
也较慢。 （8.2数据库是4路PWR5
AIX机器，7.1数据库是4路pwr3机器，我从db2adm上运行所有
的参数机器）
那里有什么特别的东西可供选择。
Ken
We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1 FP8).
For the most part, things are faster, but there is one query that is
much much slower, and it is a query that is used all the time.
select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like ''%KJS%''
The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.
On Version 7.1, this query takes about 1.5 seconds (for 36 returns out
of 120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I''ve improved my query by using
select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,''KJS'')>0
This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds
Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was
saying the optimization engine was changed such that 8 out of 10 queries
will be faster. Is the double wildcard one of the 2 that is slower?
DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5
Ken
Ken,
Have you compared the explains (best db2exfmt) for the two queries?
This should be the starting point.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
"73blazer" <yo**@ma.com> wrote in message
news:Lu********************@centurytel.net...We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1
FP8).
For the most part, things are faster, but there is one query that is much
much slower, and it is a query that is used all the time.
select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where S_PART_NUMBER like ''%KJS%''
The widlcard before and after seems to be hosing it, but for this
particular piece of the application, this type of query is neccessary.
On Version 7.1, this query takes about 1.5 seconds (for 36 returns out of
120,000 things in the table)
On Version 8.2, this query takes 13.8 seconds. (A copy of the production
database)
Most other queries are the same or faster.
I''ve improved my query by using
select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
where posstr(S_PART_NUMBER,''KJS'')>0
This query is around 3.5 seconds. Much better, but still slower than
version 7.1 at 1.5 seconds
Is there anything I can do to improve that time to get it back to 1.5
seconds? What happened in DB2 8? I was reading one article that was saying
the optimization engine was changed such that 8 out of 10 queries will be
faster. Is the double wildcard one of the 2 that is slower?
DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
Optimization value of the default 5
Ken
First, do a reorg of the table and all its indexes.
Then make sure you execute runstats and get distribution stats on key
columns and S_PART_NUMBER if it is not an indexed column.
If that does not help, you should evaluate the bufferpools and make sure the
index is in a similar buffer pool as V7 (with same objects as before). It is
possible that in your old system, the index was more likely to be resident
in the bufferpool and not flushed out by other pages.
Also, check the tablespace attributes (extent size, prefetch, etc) of the
index (assuming that S_PART_NUMBER is indexed).
Serge Rielau wrote:
Ken,
Have you compared the explains (best db2exfmt) for the two queries?
This should be the starting point.
Cheers
Serge
In looking at the exlplains for both, the optimized statment is the
same. Bufferpools match, optimization plan is the same, everything looks
ok. I don''t see anything there out of wack. Just slower. The cost on 8.2
is 32458 compared to 78334 on 7.1, so shouldn''t 7.1 be slower? CPU speed
is also slower on the 7.1 database. (The 8.2 database is a 4-way PWR5
AIX machine, the 7.1 database is a 4-way pwr3 machine, I run all the
statments from db2adm on the repective machine)
Is there anything special to look for in there.
Ken
这篇关于与7.1相比，DB2 8.2性能下降的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
