[救命]上月2小时的查询现在看来1个星期都结束不了了

wdq4587

最初由 SweetMemory 发布
[B]不应该啊！
莫非时间花在磁盘排序上？
莫非返回上万结果集Oracle走了NL？连接字段无索引？
莫非没做好TABLE、INDEX的分析？

看我上面的分析是否对，正好同时发了，呵呵。

做好SQL优化这语句会超过10分钟么？？

那还不至于，光做个字段全的复合索引都要7分50秒。

拿Oracle跟POXPRO比？莫非有得一拼？ [/B]

你肯定没用过FoxPro，我这么比喻吧：FoxPro的速度象飞，而SQL的这些数据库感觉像骑自行车，中间还差汽车火车啊我就不比喻了，这还是拿我486时代的感觉和现在比：（

SweetMemory

问你几个问题：

你骂Oracle愚蠢，想知道你运行SQL前有没有做下面这个东东：
analyze table a11_detail  compute statistics for table for all indexed columns for all indexes;

你的optimizer_mode设成什么了？CHOOSE吗？

你这个SQL大约返回多少结果集？

where a.user_name=b.user_name这个连接之后大约能过滤掉多少？或能返回多少结果集？

不了解你的数据分布，也没见到你的执行计划，大家没法准确的帮助你，只能猜测！

如果你返回的结果集非常大，索引帮不了你，或许你可以尝试一下：
select /*+use_hash(a b) full(a) full(b) */ a.user_name,count(*) dup_num
……
前提是hash_area_size 要足够大，否则效果不会理想。

如果想更多人能够给你提供帮助，你就要提供给大家更多的信息，而不是一个劲埋怨Oracle怎么怎么不好，我就纳闷了为啥花这么多银子买Oracle而不买便宜得要死的FOXPRO？难道这两个产品也在竞争么？

biti_rainy

先给出你的执行计划吧！

表的大小约1 G,在我的概念中，
如果使用 hash  join ,不知道你是什么版本，9i 设置好 pga ，或者 8i 设置比较大的 hash  area size ,整个查询应该能控制在10分钟以内才算正常,超过30分钟是执行计划非常地不好

SweetMemory

这样吧，上面HINT试过了如果还不行，你运行一下这个脚本，把脚本中第III部分的SQL替换成你自己的SQL。然后把这个脚本生成的coe_statement.lst和coe_xplain.lst两个文件传上来，这个信息应该足够。ITPUB高手如云我相信只要你把问题描述清楚，信息足够会有很多高手愿意帮助你的，问题也会解决！

BTW：在运行脚本时一路YES。

wdq4587

晕了，看来这里有Oracle的饭丝啊，说话不小心，请原谅。

我不是DBA，以前也不用Oracle，所以你说的analyze table 还有optimizer_mode我一概都不懂啊，user_name的数量我开始就说明了，如果不能边连接就边做group合并，结果集无疑是非常大的，这个是肯定的。至于说问题吗，我一个月只要算一次，所以执行过就算解决了，不要执行3天还统计不出来就行了，现在是找原因。说到FoxPro，你没用过，我跟你怎么说也不会明白的，如果你用过就知道，和SQL型数据库比难以置信的快（DOS下2.6版，同样执行SQL语句），当然没法比，那个没任务提交机制，被淘汰是必然的。

说到执行计划，我是到这里求助才第一次听说，我也不怕你们笑话我孤陋寡闻。但我写SQL代码尽量是用通用的，避免利用某个数据库的专门特性。我现在用的是Windows 2000下的Oracle 9i，那个SQL Analyzer好像没法把执行计划粘贴出来，应该有SQL语句显示吧，可惜我不会。

那个脚本我正看如何执行。

SweetMemory

呵呵~

你还是把那个脚本执行一下，然后把产生的文件传上来吧！到时这里的Oracle Fans都研究一下吧！Oracle连FoxPro都跑不过 大家服气嘛~~~哈哈哈~~

我相信你这个东东10min之内Oracle能搞定！

对了，你这么肯定我没用过FoxPro啊？俺是计算机毕业的，那个破东东当年老师逼着学过。算了这个就不说了，觉得没意思~~~~

另外，你运行脚本时把SQL改一下吧，改成我上面那个用HASH的。你说结果集巨大，我也不知道大到什么程度，百万？千万？我怀疑真的走了NL，那肯定不合适，恐怕又要等N个小时才能出结果了。

你先alter system set pga_aggregate_target=200M;吧，当然最好再大点看你自身的情况吧！

wdq4587

你说的那个用HASH的我也不会用，分析结果出来了，你给看看吧。下一步真要怎么改你说细点，我Oracle真不太会用。不过10分钟我还是有怀疑的。

至于说FoxPro，xBase那种语言真是恶心，我也是学了好几次工作要用才勉强学进去，我编的FoxPro程序基本SQL的，但是速度吗，算了，没法跟你说了，我总不能找个386来给你演示下速度吧？

SweetMemory

最初由 wdq4587 发布
[B]ROW     7 SELECT STATEMENT Opt_Mode:CHOOSE (RBO has been used)                                                                                       
ROW     6 FILTER                                                                                                                                      
SET     5 . SORT (GROUP BY)                                                                                                                           
ROW     4 .. TABLE ACCESS (BY INDEX ROWID) OF 'ADSL.ADSL11_DETAIL'                                                                                    
ROW     3 ... NESTED LOOPS                                                                                                                           
ROW     1 .... TABLE ACCESS ***(FULL)*** OF 'ADSL.ADSL11_DETAIL'                                                                                      
ROW     2 .... INDEX (RANGE SCAN) OF 'ADSL.A11DETAIL_USER_NAME' (NON-UNIQUE)      [/B]

唉~~~你也太难为Oracle了

你这个查询，b无论如何都是一个table scan，在b做table scan而返回结果集巨大情况下，你还走了nested loops！你还抱怨Oracle如何如何

你的表和索引全无统计信息，CBO当然不能正确的选择执行计划。

你给的文件中信息不全，不过我想走HASH JOIN应该是可以的，你可以按以下步骤尝试：

1、show parameter workarea_size_policy 如果是auto不用动(缺省应该是)，如果不是：
alter system set workarea_size_policy=auto;
2、alter system set pga_aggregate_target=200M;我觉得这个值最底也是200M吧，不过你这表很大，我建议你执行这个查询的时候把这个值设得再大一点(如果资源允许的话)，哪怕执行完再改回来
3、analyze table a11_detail compute statistics for table for all indexed columns for all indexes;
4、执行你的SQL，我猜这个时候Oracle应该足够聪明了，它会走HASH JION的。如果还是很慢就放弃，执行下面的SQL：
select /*+use_hash(a b) full(a) full(b) */
  a.user_name,count(*) dup_num,
  sum(least(a.stop_time,b.stop_time)-b.start_time) duptime_sum
from
  adsl11_detail a,adsl11_detail b
where
  a.user_name=b.user_name
  and a.nas_ip=b.nas_ip
  and a.nas_port=b.nas_port
  and a.start_time<b.start_time and a.stop_time-1/288>b.start_time
  and a.frame_ip!=b.frame_ip
group by
  a.user_name
having
  count(*)!=0;



不知道大家有没有别的意见？

biti_rainy

一点点意见

alter system set pga_aggregate_target=1024m  ;

或者 2048M

通常没什么问题的，不用给太小，反正会自动返回给os的,1024M ,一个进程最多也只能用 50M


另：  临时表空间要保持足够的大，建议给大些，比如2G /  4G

wdq4587

show parameter workarea_size_policy 结果自动
show parameter pga_aggregate_target 结果512M，上次调内存调的
analyze 6分45秒完成
然后执行SQL，前面两遍TABLE SCAN很快，每遍大概4～5分钟，到HASH JION执行从10%到26%就一直预告还有54～58分钟，我要回家了，只好星期一来看结果了。
也不知道这个HASH JION遍是不是你说的HASH JION，因为我再用SQL Analyzer分析发现得出的步骤和以前一样。真是摸不着头脑啊。