sql 使CPU使用100%，棘手！

myfriend2010

明天我发这3张表的reorgchk结果你你看看！

wangzhonnew

reorgchk在这个时候有啥用？？？

askgyliu

Can LZ do the following in SEQUENCE:

1) Truncate 09 tables, and load 09 data into these three tables, and runstats on these tables, then do the query explain and save the output to tab09_stats09_data09.plan

2) Run the query below:
select max(cnt), avg(cnt), min(cnt), sum(cnt)
from (select cust_id, count(cust_id) as cnt from <table_name> group by cust_id)

Replace <table_name> with the three table names and save the output as pattern09.out

3) Truncate 09 tables, and load 10 data into 09 tables, DON'T runstats any more. Do the query explain and save the output to tab09_stats09_data10.plan

4) Run the query as in step 2 and save the output as pattern10.out

5) RUNSTATS on 09 tables, and do the query explain and save the output as tab09_stats10_data10.plan

6) Upload the outputs from the above steps

myfriend2010

我的 IE坏掉了,不能回贴

askgyliu

Performance.zip (11.04 KB, 下载次数: 16)

2007-12-10 15:41 上传

点击文件名下载附件

I just created a test case to show how the data pattern change will affect the SQL statement.

In the all the test scenarios, there is no change to table definition, no change to SQL statement, and the only change is the data pattern, and the outdated/updated statistics.

askgyliu

My first glance at the original SQLs posted by LZ puzzled me. What is the exact relationship between the three tables?

I have to say that I will be very reluctant to join any two tables if the two tables are M-M relationship. I have no idea about the actual data pattern in LZ's 09/10 data, but they may be significant enough to trigger DB2 optimizer to choose different access plan even all the corresponding tables are having the same structure - once DB2 knows the exact statistics.

When tuning a query or explain a query behavior, data pattern is important but unfortunately ignored in many occasions.

I have not tested the performance of the two different queries in my test, but I always believe my t2.sql should perform equally if not better.

[ 本帖最后由 askgyliu 于 2007-12-10 15:59 编辑 ]

askgyliu

原帖由 askgyliu 于 2007-12-10 06:40 发表
Can LZ do the following in SEQUENCE:

3) Truncate 09 tables, and load 10 data into 09 tables, DON'T runstats any more. Do the query explain and save the output to tab09_stats09_data10.plan

5) RUNSTATS on 09 tables, and do the query explain and save the output as tab09_stats10_data10.plan

If the explain result from these two steps are different, it is almost for sure (10) data has very different pattern from (09) data, and you should not expect the two having the same access plan nor the same kind of performance. DB2 is smart enough to use different access path if it detects the difference.

Try to avoid any M-M relationship in the joining, as to avoid the performance uncertainty, and the potential double counting etc.

askgyliu

The test case I created shows:

1) When both tables are joining with columns with distinct values, DB2 will use MS Join. This should be the case for (09) data with (09) statistics;

2) When a new set of data is loaded but statistics is not updated, DB2 has no additional information and it will simply adapt the old(wrong) statistics. This is a simulation of loading (10) data into (09) table without statistics update.

3) After statistics update, DB2 realizes one participating table in fact has very few distinct values in the joining column, so DB2 decides to use NL Join with the table with few distinct values as the driving table.

The optimizer just works as what is expected.

askgyliu

I would really suspect PAR_CUST_CUST_GRP_ASSOC_200710 and PAR_CUST_CUST_GRP_ASSOC_200709 have very different distinct values on CUST_ID. LZ may confirm on this?

myfriend2010

不是的,count(distinct CUST_ID) from the two tables are almost number!

askgyliu,ENGLIST 太好了,我直接看不懂,还有就是我的IE有问题,你有QQ没有,加我为好友:51219748
或者msn:
myfriend_2010@hotmail.com

原帖由 askgyliu 于 2007-12-10 21:47 发表
I would really suspect PAR_CUST_CUST_GRP_ASSOC_200710 and PAR_CUST_CUST_GRP_ASSOC_200709 have very different distinct values on CUST_ID. LZ may confirm on this?