我有个查询对用户访问记录进行统计，用单表自相关，表的大小约有1GB多，记录数大概有17,000,000条，本来以前几个月的查询只要2个半小时就可完成，但现在运行43小时了就卡在75%的地方了。这样的事情9月份也发生过，查的时候是开始运行都好的，等到了大约70%左右就非常慢了，然后占用时间和预期时间慢慢不断增长，虽然显示还有十多个小时，估计一个星期都没希望完，当时通过优化Oracle 9i的内存占用再次查询勉强1天半过去了，这次看来是过不去了，CPU占用不到20％，读盘情况我看不到因为是远程遥控的。服务器有2GB内存，上次优化Oracle可占1.3GB，目前看Oracle只用了1GB，物理内存还空600MB。

查询代码如下
create table a11_dupdial5min_sp as
select a.user_name,count(*) dup_num,
sum(least(a.stop_time,b.stop_time)-b.start_time) duptime_sum
from a11_detail a,a11_detail b
where a.user_name=b.user_name
and a.frame_ip!=b.frame_ip
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
group by a.user_name
having count(*)!=0;

查用户用同一个帐号是否有同时登录行为，符合条件且时间段重叠的就累计时间和次数。user_name大约有2万2，平均每个user_name约75条记录，最多一个user_name大概有2万5记录。有基于user_name,start_time的索引。上次用按索引方式重排列表速度也几乎没增加。

类似大查询还有一个我才能完成一个月的统计，这次实在是过不去了，请各位大大不吝指点啊！感觉好像是什么缓冲内存超出一定范围Oracle就改用一种很慢的方式处理了。

2004.11.30 修改：把[救命]标题去掉。

能不能贴一下执行计划？

另外，如果是我，我就会建立中间的临时表，先
select a.user_name,a.stop_time,b.stop_time,b.start_time
from a11_detail a,a11_detail b
where a.user_name=b.user_name
and a.frame_ip!=b.frame_ip
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

然后再统计，
另外，大记录量时最好不要使用类似a.frame_ip!=b.frame_ip的条件

谢谢你的回复，什么是执行计划我看的不是太懂。我要先说明下我是有多年数据库编程经验的程序员，但我不是什么DBA，我只是用数据库而已，当然我现在用的数据库也没专门的DBA，我只能勉强去调整下。

从昨天中午12点半到现在9点，20小时过去了，处理只从75%跑到79%，真不知道Oracle在干什么事情，看来我只好中止了。我还是不太明白要个中间的临时表会有什么好处，不了解Oracle内部的处理过程，不过我打算按你说的去做做看，多谢了！另外不知道在9i中这个where条件的先后次序可有关系？

表上建了哪些索引？
每次都是这样统计全部表中的记录？
没有时间限制的？

lucky_lau用中间表可以大大缩短处理时间，假如系统还有资源可以在用中间表的基础上再使用并行查询速度可能会很多。

尽量不要在计划中出现nest loop，这样很慢的。

个人经验，经常一个sql根本跑不出来，换成procedure一条条处理，很快。不要一个sql实现，拆成一个procedure来做，

都是高手啊，谢谢！可惜写得术语我看得都不是很懂。昨天回帖的时候按lucky_lau版本的说法建中间表，开始进度飞快然后也是越来越慢，到晚上6点执行了一半，我不知道会不会象原先那样到70%越来越差，但感觉至少还要15个小时，当然比原先的执行的快，正犹豫要不要停呢。lucky_lau的话提醒了我，要对SQL进行分析，用SQL Analyzer查了下，发现我建的基于user_name和start_time的复合索引似乎没真正用上，因为我加一个光user_name的索引它就不用那个复合索引了，这Oracle什么德行啊？难道这么长时间都花在两个全表扫描的叠加了？我觉得光是user_name对上也不该这么慢啊，我查了下CPU负载很低，操作都花在读盘上了，每秒150次样子。按照SQL Analyzer的提示，又建了专门为这个查询的复合索引，约需8分钟，大部分字段都在上面，再分析，发现还是用老的索引，这怎么回事啊？干脆把老索引删了，这才用新索引，但分析的代价还是一样，做查询3个小时20分钟完成。另个类似的查询也专门按分析建议做索引，做索引和查询的时间几乎和这个一样。

我觉得Oracle实在是很难伺候，虽然查询总算完成了似乎也不是索引真正的功劳，只是因为索引把大部分的条件信息都包含了减少了读取。我原先建的user_name和start_time的复合索引应该就足够好了啊，可根本就不起作用。我前两天对过去月份的数据重做本帖所说的查询（因为以前算法有错），居然发现6月份没索引和7月份有复合索引的查询时间差不多，都是1个半小时，这Oracle的行为真是不可理谕啊！还有not in不知道为什么贼慢（另发帖子问）。顺带问下：我建了user_name和start_time的复合索引，是不是就没必要建光user_name的索引了？

还是怀念过去FoxPro的时代，386的机器上几十万的记录一眨眼查询就出结果了，没有索引还能根据查询自动建索引，那时候不光CPU慢硬盘也慢啊，真不知道现在的这些SQL数据库有什么好的（气话，FoxPro没任务机制），我觉得没一个性能可以和FoxPro相提并论的。

QUOTE:

---

最初由 wzy25 发布
个人经验，经常一个sql根本跑不出来，换成procedure一条条处理，很快。不要一个sql实现，拆成一个procedure来做，

---

我也觉得假如我按user_name一个一个查询也早该出来了，可是我只会编程，不会写存储过程啊。当然说到底我还是喜欢什么操作都一个SQL完成。

看明白为啥要中间表了，刚才回顾SQL Analyzer发现第一级扫描是按rowid（实际表中是按start_time排序）来的，不是按我希望的user_name,start_time或user_name来的，那要统计合并值用中间表肯定要好的多，我以前查询中间结果大概内存刚好够放下所以没有速度也不受影响。明显变慢大概就是我内存放不下中间结果变成磁盘存取导致的，这也是查询为什么会变得越来越慢的原因，因为中间结果增长占用了可作其它缓存用的内存。不过我以前过不去，把表里的记录按user_name,start_time排序速度也没有明显的增加。

这Oracle咋这么蠢呢？不能按我指定的group by字段去扫描，边扫描边统计不就行了？内存占用差太远了！第一级扫描要乘以第二级扫描的次数才差不多是总的次数（中间结果就有这么多条），第一级按rowid顺序读盘能省的时间和中间结果占内存（或读写磁盘）造成的性能影响比简直就微不足道！这什么基于代价的优化啊？

我这个是数据仓库性质的，表基本上不动，一个月处理一次所以也没statics统计，但是就算Oracle没有数据字段分布的先验知识，前面说的代价上的差异也是十分明显的啊，真是蠢！中间结果最少的情况是user_name每行一个不同也要等于表的行数，这样先查询再合并和边查询边合并差异最小（其实也蛮大，因为要多过一遍），但是我有单user_name索引的时候Oracle应该已经能知道user_name有多少个不同了（远少于表的行数），但是解析的规则还是一样的，这是不是说Oracle不能边做第一级扫描的时候边合并啊？group by一样要先按没有group by子句的出中间结果然后再合并啊？