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

wdq4587

星期一早上看，当时用了1小时24分钟执行完查询，这已经是到目前为止最快的速度了，但是离你所说的10分钟还是想去甚远。

我对Oracle的术语实在是缺乏了解，但是我想数据库基本的操作原理都是一致的。这个数据库查询最快的可能应该我先把数据库记录按user_name和start_time排序，这样扫描时磁盘读能减少到最小，第一级扫描无疑要全表扫，关键是按什么次序扫，按原始的rowid扫第二级扫描就会随机读无疑缺乏读盘效率（其实也不随机，因为原表是按start_time排的，查询效率不会太差），如果重排按user_name和start_time扫应该是最优的。但这只是第一级扫描，关键是第二级扫描后到group by之前的中间结果如何处理，如果非要全扫完再合并，那中间结果非常大，内存放不下就只有放磁盘，那样的速度是可想而知的，绝对没有可能提的很高的，这大概也就是为什么我以前重排数据库没有明显提高速度的原因；反之如果内存能放下全部中间结果并不明显影响其它的缓存，那先出中间结果再合并对速度就没什么明显的影响。

对于我这个特大中间结果的查询，要想快只有边扫描边group by合并处理，否则我认为谁也没可能把速度提供到10分钟完成（假定不再增大内存占用）。当然我假如做个索引把查询要用到的字段全包括进去（发现上次3小时20分钟完成的那个漏了个字段），查询时完全不读原表只读索引也能提高速度，但这只是细节性的，而非实质性的。可惜我不会编存储过程也不会用，不然编个按user_name扫描查询的就可以验证下到底是什么问题了（普通程序编程不行，因为每查个user_name命令的TCP/IP发送延迟会抵消执行效率的增加），如果还是一样的速度，那就说明我推断的不对，不然就是Oracle结构性的限制。

SweetMemory

还是给出你的执行计划吧，总根据你的描述猜测！
按以下步骤配置
DBA用户执行：
1、@$ORACLE_HOME/sqlplus/admin/plustrce.sql
2、@grant plustrace to user_name
执行SQL的用户：
@$ORACLE_HOME/rdbms/admin/utlxplan.sql

set timing on
set autotrace traceonly
运行你的SQL，运行完把下面显示的帖出来看看

biti_rainy

如果 PGA 足够大，temp表空间合适

使用 hash  join ,那么查询的瓶颈就在于 IO

这其中的代价应该大致为
1: 全表扫描
2:计算为hash table ,这里该进程PGA内存的大小是一个很关键的问题，决定被切分为几个hash  segment(假定为N)
3:第二次全表扫描并join
截止到这里，成本近似为   FTS + (N *  FTS)  + 写临时表空间
写临时表空间部分可能和前面有重叠，所以时间不是简单的叠加

然后group by 计算这一部，应该为读  join 结果后的临时表空间数据


所以假定你的表为 1.5G ,查询所需要的字段部分总长度假定为 500M,而进程内存我们可以设置到100M(设置pga_aggregate_target =  2048M) ,则总代价可近似看做

5*1.5 + 1.5 +  0.5*2    = 10G  read

500 + 500*2 = 1.5G write

如果你在查询过程中，可以观察
select  sum( BLOCKS) * 8192 from  v$sort_usage;
这里假定 block  size =  8k
这就是临时段消耗的最大空间，我这里给你假设的是  1.5G

也就是说，你的查询的代价时间长度，应该倾向于接近 读 10G 文件和写 1.5G 文件之和的时间消耗

当然，我这里的有一些假定和进程pga设置。 参考这个，应该能评估出大致消耗时间的，如果IO能力不是太差系统当时不繁忙，则可以通过一个全表扫描消耗的时间长度来大致评估。 当然，这里的500M的假设也许有回旋的余地，具体需要你观察才知道。

另：  我这里的计算方法并不代表 标准或者准确算法，是我个人习惯上评估执行时间长度的一个参考。

wdq4587

做了个全部用到字段的索引，查询花了1小时30分钟，（当然今天早上数据库有点负载，不像晚上完全没负载），就算查询变快了，那也差不了多少。

什么执行计划的，我下午再看吧。

不过假如你们确定Oracle非要把第二遍扫描做完才肯group by，那这个问题再追究下去也没什么意义了：（光看如何内存调优让东东都在内存里就执行快并不是我查询通过后要问的目的。

biti_rainy

最初由 wdq4587 发布
[B]做了个全部用到字段的索引，查询花了1小时30分钟，（当然今天早上数据库有点负载，不像晚上完全没负载），就算查询变快了，那也差不了多少。

什么执行计划的，我下午再看吧。

不过假如你们确定Oracle非要把第二遍扫描做完才肯group by，那这个问题再追究下去也没什么意义了：（光看如何内存调优让东东都在内存里就执行快并不是我查询通过后要问的目的。 [/B]

没有人说要全部在内存,没有谁让你去调整SGA，在你这个情况下，除非你有很大的SGA比如4G以上可能有用，我在这里的计算方法就是假定没有任何内存中缓存数据的，全部是物理读，问题就在如何尽量减少对 临时表空间的写和访问
我都说了大体时间计算方法供你做参考


当然，如果2小时你已经足够可以接受，也没什么必要说非要探讨了

SweetMemory

最初由 wdq4587 发布
[B]做了个全部用到字段的索引，查询花了1小时30分钟，（当然今天早上数据库有点负载，不像晚上完全没负载），就算查询变快了，那也差不了多少。

什么执行计划的，我下午再看吧。

不过假如你们确定Oracle非要把第二遍扫描做完才肯group by，那这个问题再追究下去也没什么意义了：（光看如何内存调优让东东都在内存里就执行快并不是我查询通过后要问的目的。 [/B]

你这人真有意思！别人都是来帮你的！明知道自己Oracle了解得少还在那一个劲的乱猜~自己都说没意义还有什么好问的。

看看BITI透彻的分析，按照他的方法去评估一下，好好学学吧！

只有你自己才最了解你的数据分布、系统的情况！

wdq4587

最初由 SweetMemory 发布
[B]
你这人真有意思！别人都是来帮你的！明知道自己Oracle了解得少还在那一个劲的乱猜~自己都说没意义还有什么好问的。
[/B]

不好意思，今天整个一个下午忙别的事，没能来看这个事。谢谢你们的帮助！就我自己的问题来讲，只要1、2天内算出结果就行了，并不需要非要调优到什么地步。

我又看了下 biti_rainy 的算法，跟我实际的操作比对，在analyze之后，第一、第二次全表扫描都用4分钟多些完成，这是很快的，慢都慢在hash join上，也就是中间结果存盘，都要1个小时以上，最后的table scan加sort output也很快（就是把中间结果按user_name分组合并）也很快，2＋6分钟多，因为group by合并后的结果是在内存中保存的，一遍对中间结果的线性扫描就够了。时间主要花在中间结果连接上了（写盘和读取按道理应该很快，因为是线性读写的），我觉得花的时间和lucky_lau版主的中间表算法应该是相当的。

我现在疑惑的是hash join为什么这么慢（我也不懂为什么analyze之后就变快了很多），难道还是内存占用不够？要分很多次造成的？我能否将表记录重新排序后强制不用散列连接？如果重排后按user_name的索引相等判断就足够优化了，散列连接的优化在读盘上不会节省时间的（因为判定不等的连接条件还是接近随机读盘）。

（做了个全部用到字段的索引后，过程从5步变成了3步，两次Index Fast Full Scan，一次Hash Join就完成了，但显然速度没明显增加，不过我疑惑的是这次可有中间结果写盘？）

biti_rainy

你的 pga 设置512m
workarea_size_policy =  auto

能否设置的再大些，比如2G. 这个可以动态的修改
alter  system  set  ...  就可以了，没有关系的，不用的时候会自动释放给os的

biti_rainy

索引包含了查询所需要的所有字段的话，如果把 Index Fast Full Scan 换为 index   range  scan ，我估计甚至可以使用 sort  merge join  ，这样可能不需要排序，并且不象hash  join那样反复的 读相同的数据

你的 hash  join 慢 我估计就是 hash_area_size 过小造成的。 不知道你的索引segment size 是多少，远超过 hash_area_size 的话(接近pga_aggregate_target * 5%) 则 hash  join可能代价就比较高

所以我一再强调请增大 pga_aggregate_target  达到2g 以上,这样比 512M 速度应该可以提高4倍以上

THE_SQL 就是你自己的sql，我估计除了 hash  join外你可以这样试一下

select /*+ index(a, index_name)  index(b, index_name) use_merge(a 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;


sqlplus 中
explain  plan for   THE_SQL;
@?/rdbms/admin/utlxplp;
把输出结果作为附件传上来

wdq4587

上午来看，把PGA增大到1.5G，重新执行有全字段索引情况下的查询，开始两次scan变快了点，从4分钟多变到2分钟多，但hash join的预告时间还变长了点，干脆中止了。

尝试了用select /*+ index(a, A11DETAIL_USER_NAME) index(b, A11DETAIL_USER_NAME) use_merge(a b)*/
没有用，执行计划都是一样的，Oracle执意要做hash join，我现在的问题是如何能强制它做nest loop？

执行计划如下：
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation               |  Name               | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |     1 |   108 |       | 45959 |
|*  1 |  FILTER                 |                     |       |       |       |       |
|   2 |   SORT GROUP BY         |                     |     1 |   108 |       | 45959 |
|*  3 |    HASH JOIN            |                     |     1 |   108 |  1040M| 45954 |
|   4 |     INDEX FAST FULL SCAN| A11DETAIL_SP_QUERY  |    16M|   851M|       |     4 |
|   5 |     INDEX FAST FULL SCAN| A11DETAIL_SP_QUERY  |    16M|   851M|       |     4 |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(COUNT(*)<>0)
   3 - access("A"."USER_NAME"="B"."USER_NAME" AND "A"."NAS_IP"="B"."NAS_IP" AND "A".
              "NAS_PORT"="B"."NAS_PORT"
       filter("A"."START_TIME"<"B"."START_TIME" AND "B"."START_TIME"<"A"."STOP_TIME"
              -.003472222222222222222222222222222222222222 AND "A"."FRAME_IP"<>"B"."
              FRAME_IP"


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Note: cpu costing is off