sql 使CPU使用100%，棘手！

wangzhonnew

明天看一看访问计划，如果一切没有问题你可以试一下手工单独运行那个查询看看有没有高cpu的迹象

wangzhonnew

原帖由 myfriend2010 于 2007-12-7 23:50 发表


中午的时候我看了一下，cost正常！和前边9月份的cost基本上一致！


4个分区都在同一个磁盘上！

也就是说都在同一物理节点上了，这样的话分区间通讯应该直接走共享内存（不知道windows是不是这样），这样的话没有了i/o wait，大量的内存操作很有可能造成高cpu的现象。
如果发现在使用了一个分区键依然存在大量TQ的情况就需要看看为什么还会有TQ，否则看一看是否有哪个join可能造成这个问题

myfriend2010

单独执行：有CPU偏高的迹象，但是小于100%，有一段持续的时间，不过最终可以运行出来！

wangzhonnew

和9月份的good case相比呢？用db2batch单独执行一下这个query，for both 9月份的good case和10月份的 bad case

askgyliu

How many CPUs do you have for your server?

You seem to have turned on intra-partition parallelism, and you have four nodes in the single server?

You may check out in total how many db2 agents serving this particular query? Is your server capable of providing the adequate processing power?

Also, you may check out the access plan to find out the difference with single column as partitioning key or two columns as partitioning key, respectively.

Data volume is another key factor in determining a proper access plan, and the estimated joined cardinality(the uniqueness of your data).

You may share the reason of having two columns as partitioning key in one of your table.

Also, CPU hitting 100% when a query is processed is not necessary a bad thing as long as the query can finish in shorter time:
  - A good access plan should make FULL use of the available system resource, AND
  - A good access plan should know the system resource limit and make use of the system resource WITHIN acceptable limit.

askgyliu

You may also check out your storage performance, and the active prefetchers serving the query.

If the physical system resource is not adequate, you may experience problem no matter what has been done.

Waiting time will increase exponentially if physical hardware is not able to support the system.

askgyliu

1) one more point to take note is that ensuring the tables having the same partitioning key does not guarantee the tables can be locally joined if these tables happen to be in different tablespaces, and these tablespaces are in different nodegroups.

2) For this case, since the (09) query is running fine, you may try to truncate (09) tables, and load the (10) data into (09) tables, and observe the difference, i.e. loading (10) data into both (09) and (10) tables, and check the performance difference.

myfriend2010

今天和狼QQ了一下，最终分析很有可能是存储过程中临时表造成的！

myfriend2010

唉！可恶的DB2

wangzhonnew

依然认为是统计数据有问题，你的select count(distinct xxxx)做了没有？如果按照你原先的数据，那个临时表里应该有30多万（还是300多万）记录，但是在使用临时表的计划里只有151。。。这样造成的MSJOIN可能会占用远比预期高的内存空间，在经过几次join以后结果集可能会非常大。你试一下首先在3个表里对cust_id做索引，然后对临时表runstats一下每次，保证db2优化器拿到的统计信息都是真实的

[ 本帖最后由 wangzhonnew 于 2007-12-9 04:47 编辑 ]